Events/Hour per week

From MariachiWiki

Now that you've created a visual representation (histogram) of the events per hour for each day, you may want to combine the whole week's data into one graph. This page is created to teach you how to do just that.

OBJECTIVE:

To combine all the data from each graph for a whole week into one graph to display the events per hour per week.

MATERIALS:

• Scintillator data from website

• Excel

• Excel’s Data Analysis Toolpak*

To verify if you have the Toolpak, open Excel and click on the Tools menu. “Data Analysis” will be listed if you have it. If it is not listed, click on “Add-ins” from Tools menu and select “Analysis Toolpak” and “Analysis Toolpak – VBA” and click “ok”.

Step-By-Step How-to:

1. Open a new Excel file and save it as "events per hour per week_[x].xls". [x] denotes a variable 1, 2, 3, etc.

2. Titles - Label A1 as "hour" and B1 as "bin"

3. Open - Open the existing file "events per hour_week[x].xls".

4. Copy - Select and copy the data in column B (not the whole column, just the cells) from "events per hour_week[x].xls"

5. Paste special - Choose "paste special" from the Edit menu in the file "events per hour per week_[x].xls" and choose "values" and paste it into the first empty A cell.

6. Repeat - Repeat steps 4 and 5 for each sheet/day of the week. (Paste all the data on a single sheet in the new file.)

7. Sort - Select column A and go to the Data menu and choose "sort". Do not change anything and click ok. This will sort the data from 0 to 23 for the whole week rather than having it go from 0-23 for one day and then 0-23 for the next day.

8. Copy & paste - Copy C cells (bins) from "events per hour_week[x].xls" and paste special (values) into column B on "events per hour per week_[x].xls".

SAVE!

9. Histogram - From the Tools menu, choose “Data Analysis” and choose “histogram” from the list. The “input range” is your data column (column A). The “bin range” is column B, where you set up the bins. The “ouput range” is the place where you want the histogram to appear.

10. Input range - Click on the small box with the red arrow for the input range. This will take you back to your worksheet. Click and hold down the mouse button on A2 and drag down to select all the filled cells in column A. Click on the small box with the red arrow again to make the whole box appear again.

11. Bin range - Click on the small box with the red arrow for the bin range. Click and hold down the mouse button on B2 and drag down to select all the filled cells in column B. Click on the small box with the red arrow again to make the whole box appear again.

12. Output - Select “output range” and click on the small box with the red arrow for the output range. Click D1 and then click on the small box with the red arrow again to make the whole box appear again.

13. Chart - You want the histogram displayed as a bar chart, so click the box to select “chart output” and click “ok”.

14. Delete - Delete the information in D26 and E26. This is the "more" category, but there is no more data past hour 23 because hour 24 is hour 0 on the next day.

If a box pops up that says "reference not valid", just click "ok". Sometimes this box appears, but other times it does not. It is not understood what this box means because clicking "ok" makes the graph with no problem.

SAVE!

15. Enlarge - Click on the white area of the chart and grab a black corner and enlarge the graph by dragging the mouse.

16. Delete - Click on the legend and delete it.

17. x-axis - Double click on a number on the x-axis and choose the “font” tab and choose size 9 or 10 font. Choose the “scale” tab and put a “1” in each box. (This will put the scale at 1.)

18. y-axis - Double click on a number on the y-axis. On the “patterns” tab, select “inside” for the “minor tick mark type”. On the "font" tab, select font size 14.

19. Data labels - Double click on one of the bars on the chart. Select the “data labels” tab and choose “value”. This will display the value for each of the bars to make it easier to read quickly if you want to know an exact number. Double click on one of the values that appears and on the “font” tab, resize it to 10 font.

20. Gap width - Double click again on one of the bars on the chart. Select the "options" tab and set the "gap width" at 100. This will reduce the gap between bars.

20. Total - In D27, type “TOTAL”. In E27, type “=sum(E2:E25)” This adds up all the cells in column E to give you the total number of events per week.

21. Titles Click on the label for the x-axis and select it and type “hour”. Click on and select the title and rename it “Events per hour per week [x] -- (total from E27) total".

SAVE!

T-TEST

If the data is "good", there should be little variation from one hour to the next. In order to test this, we will perform a statistical test called a t-test. Excel has the capability to do this with a pre-programmed function. The syntax is "=ttest(array1, array2, tails, type)". Arrays 1 and 2 are your sets of data. Tails can either be 1 or 2. Tail 1 means that you predict that one set of data will have a higher average than the other. Tail 2 means that you have no prediction (i.e. you expect them to be the same). Type can either be 1, 2, or 3. Type 2 and 3 are similiar, so for this case we will not distinguish between them. Type 1 is like a "before and after" test. You test the same sample/subject multiple times. Type 2 is comparing 2 different things, like responses on a survey from biology students and chemistry students. The value returned is called a p-value. If the p-value is greater than .05, then the data are considered to be statistically the same. If the data are exactly the same, the t-test will return a p-value of 1.

22. T-test - In D29 type "ttest". In E29, type "=ttest(E2:13,E14:E25,2,1)" This is a tail 2 and type 1 test. If you get a p-value greater than .05, you know that your data is good. If your p-value is less than .05, this shows that there are statistically more events during a certain hour, possibly due to sources other than cosmic rays.

[edit]