Number of Events per delta time
From MariachiWiki
Determining Number of Events per Time Interval
Using Excel’s Data Analysis Tools
Objective:
To use Excel to graphically represent the randomness of the detection events from the scintillators using Excel’s data analysis capabilities. Students and/or teachers will be able to import data into Excel, format cells, calculate time intervals using Excel’s pre-programmed functions, copy functions down a column, sort data, and create a histogram (bar graph) representing the data.
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 Excel and from the File menu choose Open. As a default, only Excel files will be shown, but your file is not an Excel file. To change the types of files listed, in the "files of type" box, choose "all files". Select the .txt file with which you'd like to work.”
2. Import - The "text import wizard" box will appear. Choose "delimited" (which means there are boundary conditions) and click next. In step 2, uncheck the "tab" delimiter and choose "space" and "other". Next to the other box, type a colon and press next. (This will put a column divider in the place of spaces and colons.) This separates the data into columns of date, hour, minute, and seconds. In step 3, with the first column highlighted, choose the "date" format in MDY. Highlight the last column (UTC) and choose "do not import column".
3. Insert - Right click on the 1 on row 1 and choose insert to add a header row.
4. Titles - Create titles "date", "hour", "min", "sec", and "delta t (min)" in the header row (the new row 1).
5. SAVE! - Make sure to choose save as and rename the file as a Microsoft Excel workbook (.xls) file!
6. Calculate – In cell E2, type “=(B3*60+C3+D3/60)-(B2*60+C2+D2/60)”. This converts the time of the first and second events into minutes and calculates the time interval (in minutes) between the first and second events.
7. Copy – Click on cell E2. Click and hold the small black box in the lower right-hand corner and drag it down the column to the 2nd to last row of data. This will copy the formula down, referencing the correct cells so you won’t have to retype it for each cell. (Note: If you drag it to the last row of data, you will get a meaningless number because the 1st number in the formula will reference a cell with no data.)
8. Fix – Find the cell in column E that corresponds to the last event at hour 23. With this cell selected, in the formula bar (just beneath the toolbars at the top), highlight the first B cell reference in the formula and replace it with a "24". This fixes the time interval from a negative number (it was doing 0hr-23hr) to a correct calculation (24hr-23hr).
9. Copy – Select column E (click on the "E") and copy. Select column F and choose "paste special" from the Edit menu. Choose values. This will copy the value in the E cells, rather than the formula that was entered into them. If you just choose "paste", the formula will shift over one column (referencing column C instead of B, etc.) and you will not get the same values as in column E.
10. Sort – With the "F" for column F still selected, from the Data menu, choose “sort”. When the warning dialogue box appears, choose “continue.” This will sort only column F, while leaving the other columns where they are. Don’t change anything in the next box that appears, and click “ok”. This will sort column F from least to greatest so you will know your range of delta t. (This is important for the graph.)
11. Rename – Rename cell F1 to “delta t (sort)”
12. Bins – Column H will be your “bin” column. Basically, a bin divides your data (into bins). You can choose how large your bin will be, but for this case, we will choose 1. This means that each bin will only contain one number. The “1” bin will hold all the numbers such that 0 < x < (or =) 1. The “2” bin will hold all numbers such that 1 < x < (or =) 2 and so forth.
13. Create bins – In H1 type "bins". In H2 type “1”. In H3 type “=H2+1". Refer to your “delta t (sort)” column and see what the highest number is. Click on H3 and drag the little black box down until you get to the whole number just greater than the highest number in your data. (If the highest number is 54.542, then your bins should go to 55.)
14. SAVE!
WHAT EXACTLY IS A HISTOGRAM?
A histogram is a special type of bar graph. On the x-axis is your different categories (called bins) into which your data will be divided. On the y-axis is the frequency, or number of occurences. For example, if you were pulling socks out of a drawer and you had white, gray, and black socks, you could make a histogram of your results. White, gray, and black would be your categories (or bins), and the number of times you pulled out each color of sock would be the frequency. Histograms can also be used to explore probability.
15. Analysis – From the Tools menu, choose “Data Analysis” and choose “histogram” from the list. This box should appear:
The “input range” is your data column (column E). The “bin range” is column H, where you set up the bins. The “ouput range” is the place where you want the histogram to appear.
16. Input – Click on the small box with the red arrow for the input range (highlighted in blue above). This will take you back to your worksheet. Click E2 and hold down the mouse button to select all the filled cells in column E. Click on the small box with the red arrow again to make the whole box appear again.
17. Bins – Click on the small box with the red arrow for the bin range. Click H2 and hold down the mouse button to select all the filled cells in column H. Click on the small box with the red arrow again to make the whole box appear again.
18. Output – Select “output range” and click on the small box with the red arrow for the output range. Click I1 and then click on the small box with the red arrow again to make the whole box appear again.
19. 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.
20. SAVE!
21. In the data that appears with the graph, delete the cell that says "more" and the cell to the right. This category is meaningless because we made the bin range large enough to not have any in the "more" bin.
22. Customize – Click on the white area of the chart and grab a black corner and enlarge the graph by dragging the mouse.
23. Customize – Click on the legend and delete it.
24. Customize – 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.)
25. Customize – 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.
26. Customize – Do a slow double click on the label for the x-axis and type “delta t (min)”. Do a slow double click on the title and rename it “Number of Events per delta t”.
27. Optional – Double click on one of the bars on the chart. Select the "options" tab and set the "gap width" to 100. This reduces the gap between bars of data. 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. Click 'ok'. Double click on one of the values that appears and on the “font” tab, resize it to 10 font.
28. SAVE!
Other days of the week
Right click on the sheet name (bottom left corner) and choose insert -> worksheet. Click on and hold the tab for the new sheet and to drag it to after the existing sheet. Make a total of 7 sheets and rename them for each day (right click -> rename). To analyze the data for the other sheets, follow the same steps as above. However, the data will be opened in a new file, so you will have to select this data and copy it to your file with 7 sheets.
EXAMPLE
Go to next: Events per delta time per week
Back to Candice
