Download Simple Data Analysis Using Excel - Classical Physics Lab | PHYS 401 and more Lab Reports Physics in PDF only on Docsity!
Brief Instructions to Simple Data Analysis Using Excel Part I Entering data into Excel, doing simple calculations, putting a line through data, and finding the slope, intercept and uncertainties of the line
Excel is a spreadsheet with which text based and numerical data can be analyzed and displayed. A spreadsheet consists of a two-dimensional array of cells into which text, numbers, symbols and formulae can be entered. A portion of a spreadsheet is shown below.
Columns are labeled by capital letters, and rows are labeled by numbers. Note cells A1 and B contain text. Cells A2-A12 contain numerical data in scientific notation format. Cells B2-B contain numbers in decimal format. Other formats are available. Note that cell A2 is bordered in black indicating that it is the active cell, i.e. its contents may be modified. The layout of the Excel user interface should be familiar. There is a menu bar, a tool bar, and a formula bar. The menu and tool bars may be customized by including or omitting various options.
tool bars formula bar menu bar
An Excel file is organized into Worksheets. The collection of Worksheets is a Work-book.
Data are enter into a cell by selecting the cell with the cursor, typing the text, number, or formula into the cell, and pressing the ‘Enter’ key. The entry appears both in the cell and the formula bar as it is typed. After the ‘Enter’ key is pressed, the active cell becomes the next cell in the column. This feature is helpful in data entry.
The width of a column of cells may be changed through ‘Format→Column→Width’. The format of numerical data may be selected through ‘Format→Cell→Number/Scientific’. The number of decimals displayed may be selected in this manner. Internally, Excel holds 15 digits.
After data are entered, the next step in analysis is to perform some mathematical operation on the data. For the RLC_1_data file, the first step is to find the period of the oscillation from the measured time difference, t1-t11, between ten zero crossings. The period is simply the time difference divided by five. The data for the RLC laboratory can be downloaded from the Classical Physics Laboratory section of http://www.npl.uiuc.edu/~debevec/.
Make cell C2 active. Type ‘=B2/5’. Press ‘Enter’. The value 0.001492 appears in the cell. To perform the same calculation on cells B3-B12, select cell C2 and with the left mouse button pressed down, move the cursor to cell C12. Cells C2 through C12 are now selected. Then select ‘Edit→Fill→Down’. Values then appear in the cells. ‘Edit→Fill→Down’ in effect has copied the formula from cell C2 into the other cells and automatically increments the cell reference. Column C now contains the period. Cell C1 may be used as a column label. Frequency may be calculated in column D, and angular frequency in column E.
The laboratory hand-out shows that there is a linear relation between the inverse of the period squared (1/T^2 ) and the inverse of the capacitance (1/C). Column F may be used for the inverse of the capacitance, and column G for the inverse of the square of the period. A graph is the next step for these data.
Cells may be cut and pasted from one part of the spreadsheet to another. When the formula ‘=1/A2’ is entered in cell F2, Excel interprets ‘A2’ as the cell which is five cells to the left of the current cell. Excel is using relative referencing. With relative referencing, column F could be cut and pasted elsewhere in the spread sheet and the formula would still be calculated correctly. Excel also has absolute referencing. The syntax for an absolute reference to cell A1 is $A$1. One application of absolute referencing is to store a constant in a given cell and multiply other cells by this constant. In entering formulae, the precedence of operations in calculation must be respected. Parenthesis should be used to eliminate ambiguities. Complex formulae can be evaluated in steps.
Excel has a large number of built in mathematical functions. The available functions may be found by selecting a cell and ‘Insert→Function’. Note that pi is entered as ‘PI()’, and trigonometric functions arguments are in radians. A number of built in statistical functions are also available. A few of these functions for basic statistical evaluations are listed below.
SUM(), e.g. =SUM(B2:B7) MAX() MIN() AVERAGE() MEDIAN() MODE() AVDEV() STDEV() STDEVP()
Evaluation of formulae can sometimes produce errors. One common error is division by zero, which is signaled by the messages #DIV/0! in the offending cell. Other error messages are
Then in the formula bar type ‘=LINEST(G2:G12,F2:F12,TRUE,TRUE)’, and hold down simultaneously the ‘Ctrl’, ‘Shift’ and ‘Enter’ keys. The arguments of the function are the y values, the x values, TRUE/FALSE, depending on whether the intercept is not forced to zero or is forced to zero, and TRUE/FALSE, depending on whether standard errors of the slope and intercept should or should not be calculated. The slope, intercept, and their errors appear in the four cells as shown in the picture below. The labels have been added for identification.
0.448888 6676. 0.000955 6780.
b (^) a
σb σa
Now that the slope and intercept of the best line have been calculated, the best fit line can be calculated and added to the graph. It is convenient to create the names ‘b’ and ‘a’ first as shown in the picture below.
In cell H2 type ‘=a+b*F2’ and enter. Then, as before, drag the result to cell F12, and select ‘Edit→Fill→Down’ to put the best fit values of the line in these cells. The data for this line can be added to the existing chart by right clicking on the chart and selecting ‘Source Data’. Then in the dialog box that appears, select ‘Series’ to enter a new series of data. Select ‘Add’ and then for ‘X Values’ enter ‘=Sheet1!$F$2:$F$12’ and for ‘Y Values’ enter ‘=Sheet1!$H$2:$H$12’. Note the syntax. It must be entered completely correct. The points can be connected by right clicking on any fit value and selecting ‘Format Data Series→Patterns.’ Then in the dialog box select ‘Line→Automatic’ and ‘Marker→None.’ The best fit line now appears through the data. The above steps can be repeated to analyze all of the data of the RLC laboratory. The Excel Worksheets can be annotated with text boxes to make them largely self contained.
With these procedures the data from all parts of the RLC laboratory can be analyzed.
Part II Making a Histogram, Adding Statistical Errors and Calculating the Normal and Poisson Distributions
This note describes how to make a histogram from the Excel file mantle_012403_v1.xls, which may be found in phyaplu\301 Common. To make a histogram we first enter data in an array. Ten rows are convenient. If there are 180 entries, there will be eighteen columns. The last column will be column R. A histogram is a plot of the frequency of an entry versus the value of the entry. In this example each entry is an integer. Find the smallest entry with the function MIN(A1:R10) and the largest entry with the function MAX(A1:R10). Find the number of entries with the function COUNT(A1:R10). These three functions and labels for their values are in cell B13 for ‘=MIN(A1:R10)’ and cell for A13 the label ‘min’, cell B14 for ‘=MAX(A1:R10)’ and cell for A14 the label ‘max’, and cell B15 for ‘=COUNT(A1:R10)’ and cell for A15 the label ‘entries’. In this example 64 is the smallest value, and 115 is the largest value. There are 180 entries. Next we choose the number of bins into which the entries are sorted. If we choose too few bins, the details of the distribution will be lost. If we choose too many bins, the fluctuations in the entries due to the finite sample will obscure the shape of the distribution. In Excel the number of bins is chosen by specifying the bin boundaries. In principle, the bins could have different widths. In this example we will choose 5 for the bin width, and the bin boundaries will be 60, 65,…,120. We make a column of bin limits in a convenient place in the worksheet. In the example file the bin limit values are in column A18:A30 with the label in cell A17.
The histogram function is chosen from ‘Tools→Data Analysis→Histogram’. The dialog box asks for the Input Range. In this example it is ‘$A$1:$R$10’. The dialog box asks for the Bin Range. In this example ‘$A$18:$A$30. Also the dialog box asks for the Output Range. This is the top left cell of the output. Any convenient place in the worksheet can be specified. In this example we choose C18. Excel then gives two columns, Bin and Frequency. In the example the output appears below cells C18 and D18. The first bin, Bin 60, has frequency 0. There are no entries less than 60. The second bin, Bin 65, has frequency 1. There is one entry between 61 and 65. The third bin, Bin 70, has frequency 2. There are two entries between 66 and 70. The last bin, More, has frequency 0. There are no entries greater than 120. The table below gives the limits of this histogram for these Bin Limits. Note the specific value of the limits, which for integer entries are quite important.
Bin Range of x 60 x < 60 65 60 < x ≤ 65 70 65 < x ≤ 70 75 70 < x ≤ 75 80 75 < x ≤ 80 85 80 < x ≤ 85 90 85 < x ≤ 90 95 90 < x ≤ 95 100 95 < x ≤ 100 105 100 < x ≤ 105 110 105 < x ≤ 110 115 110 < x ≤ 115 120 115 < x ≤ 129 More < 120
has a normal distribution function, NORMDIST. The arguments for NORMDIST are described in the Excel help file. The function is evaluated in cells I18:I30. Cells H18:H30 and I18:I30 are identical as expected. The normal distribution is plotted on the data. The format of the plot is a continuous smooth curve with no points.
The last part of the data analysis is the calculation of the error in the mean. The error in the mean is the standard deviation, ‘sigma’, divided by the square root of the number of measurements, “N”. It is calculated in cell D38 and labeled in cell C38. The result of this experiment is 87.6 ± 0.6 with the result given to three significant figures. If we had not subdivided the counting time into 180 intervals, we would have measured 15799 total number counts. This number is calculated in cell F13 and labeled in cell D13. The average rate is the total number of counts divided by the total time, 180 intervals. The average rate is calculated in cell F14 and labeled in cell D14. This average agrees with “AVERAGE” and “mean” calculated elsewhere on the worksheet. The value is identical to “AVERAGE.” In one counting period in which 15799 counts are recorded, the error in the number of counts is √15799, and the error in the average rate is √15799 / 180 = 0.7. Note that the error is the same as the error in the mean calculated in cell D38. This point is mentioned in the counting statistics laboratory hand-out.
