Frequency of Events per Hour

From MariachiWiki

Now you may want to know how common it is to have 5 events per hour, or 10 events per hour and so on. This tutorial will guide you through this process to graph frequency (number of occurences) vs. events per hour. The height of each bar will tell you how many times during the week there were x number of events per hour.

OBJECTIVE:

To combine all the data from each graph for a whole week into one graph to display the events per delta time 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 "frequency vs. events per hour_week[x].xls". [x] denotes a variable 1, 2, 3, etc.

2. Title - In A1 type "events/hour"

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

4. Copy - Select and copy the frequency cells in column F (do not include the "total" value).

5. Paste special - On the new file, select cell A2 and "paste special - values".

6. Repeat - Repeat this copy and paste special for each sheet/day on "events per hour_week[x].xls". (Paste them all onto the same sheet.)

7. Sort - Select the "A" for column A and go to "sort" on the Data menu. Click "ok" to sort column A from least to greatest. Note the last/highest number in column A after sorting.

8. Bins - In B1 type "bin". In B2 type "0" and in B3 type "=B2+1" to create a bin width of 1.

9. Bins - Select B3 and drag the black box in the lower right corner down far enough so the last number in column B corresponds to the highest/last number in column A.

10. Histogram - From the Tools menu, choose “Data Analysis” and choose “histogram” from the list. The “input range” is your data column. 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.

11. 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.

12. 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.

13. 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.

14. 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.

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 size 14 font.

19. Titles - Click on the label for the x-axis and select "bin" and type “Events per hour”. Click on the title and select it and rename it “Frequency vs. Events per Hour”.

20. 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.

21. Gap width - Double click on one of the bars again and select the "options" tab and set the "gap width" to 100. This reduces the gap between the bars on the graph.

22. Delete - Delete the "more" bin from column D and E. This is a useless bin because there is no data in there because we set the bin range high enough to include all data points.

EXAMPLE

---

Go to Cummulative Frequency of Events per Hour

Back to Events/Hour per week

Back to Candice