Events per Hour
From MariachiWiki
Determining Number of Events per Hour and per Day
Using Excel’s Data Analysis Tools
**NOTE: This lesson plan/tutorial is written assuming that you have gone through the Number of Events per delta time lesson plan/tutorial. If you are familiar with Excel and histograms, it is not necessary to do the other tutorial first. If you are not familiar with them, it is highly recommended that you do so because there are explanations for procedures in there.
Objective:
To manipulate data to the format you need so as to use it to put into a histogram.
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 create a total of 7 worksheets (one for each day) like you did for the Number of Events per delta time.
2. Copy & Paste Open the file "number per time interval.xls". From this file, copy the data cells in columns A and B and paste them into the new file. Repeat for each day, pasting them on the corresponding sheet in the new file.
3. Insert Right click on the number 1 for row 1 and choose insert to add a header row. Name the columns "date" and "hour".
4. Heading Create the heading "bin" for a new column of numbers in column C.
5. Bins In C2 type “0”. In C3 type “=C2+1” and drag down the formula to C25. (C25 should then display the number 23.) This creates your bins, each with a width of 1.
SAVE AS! - Be sure to rename the file as a Microsoft Excel Workbook (.xls) file!
6. Histogram From the Tools menu, choose “Data Analysis” and choose “histogram” from the list. The “input range” is your "hour" column. The “bin range” is column C, where you set up the bins. The “ouput range” is the place where you want the histogram to appear.
7. 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 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.
8. Bin range Click on the small box with the red arrow for the bin range. Click and hold down the mouse button on C2 to select all the filled cells in column C. Click on the small box with the red arrow again to make the whole box appear again.
9. Output Select “output range” and click on the small box with the red arrow for the output range. Click E1 and then click on the small box with the red arrow again to make the whole box appear again.
10. Chart You want the histogram displayed as a bar chart, so click the box to select “chart output” and click “ok”.
- 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.
11. Delete Delete the "more" category. It is empty and useless because there is no more data past hour 23 because hour 24 is hour 0 on the next day.
SAVE!
12. Enlarge Click on the white area of the chart and grab a black corner and enlarge the graph by dragging the mouse.
13. Delete Click on the legend and delete it.
14. 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.)
15. 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.
16. 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.
17. Gap width Double click again on one of the bars in the chart. Select the "options" tab and set the "gap width" to 100. This will reduce the gap between bars.
18. Total In E27, type “TOTAL”. In F27, type “=sum(F2:F25)” This adds up all the cells in column F to give you the total number of events per day.
19. Titles Click on and select the label for the x-axis and type “hour”. Click on and select the title and rename it “Events per hour -- (total from F27) per 24 hours”.
SAVE!
- For the remaining days of the week, repeat steps 3 through 19
EXAMPLE
Go to next: Events/Hour per week
Back to Candice
