Home • People • Courses • Program • Research • Clinic • Goals • Kiosk • NewsUnderstanding Basic Statistics • Fitting • Exercise • Excel • Igor • Kaleidagraph • Origin • Power Laws • Dimensional Analysis
Using Microsoft Excel you can quickly compute the mean, standard deviation, and standard deviation of the mean (SDOM) of a set of data, and you can rapidly perform all sorts of error calculations.
A common task is to average several data points, and compute the standard deviation of the mean (SDOM) to estimate the uncertainty of the measurement. The figure shows an example of performing this calculation. The values to average are placed in column B, rows 2 through 5. In cell B8 a formula is typed to compute the mean (average). The formula is shown immediately to the right. It is =AVERAGE(B4..B8). All formulas in Excel begin with an equal sign, which must be the first character in the formula. The argument to the AVERAGE function is a range of cells. Of course, for this example you can calculate the mean by inspection, but it's reassuring that Excel gets the right answer.
The next cell down has the formula for computing the standard deviation. Does Excel use n or n-1 to compute the standard deviation? Is the computation of the SDOM done correctly?
Even better than computing something once using Excel is reusing the calculation many times. If you have a great many sets of 5 numbers for which you need to know the statistics, you can just type the new values over the 5 old values and write down the resulting average and SDOM. Alternatively, you can type each set of values in its own column and copy the formulas for average, standard deviation, and SDOM from column B to all the other columns.
Select the cells whose formulas you wish to copy (B8..B10). Then place the cursor at the lower right corner of cell B10. It will turn from an arrow to the solid black cross shown. Drag to the right through two columns and updated formulas will be written into these two columns. The average of each successive column has indeed risen by 1, as it should. Note also that the formula in D8, as shown in E8, now involves the values in column D, not column B as in the source. Excel automatically updates the references. Cell references that are modified when copied in this way are called relative references. Why are the standard deviation and SDOM values the same?
Plotting Data as You Go
Excel is also very useful for entering and plotting data as you take it.
Making a crude plot as you go is a very useful way to see whether the data
make sense, where you might need to take more points, and when a data point
doesn't seem to be following the trend. Can you spot the errant point in
the data sheet and plot below?
Here's how to put together such a sheet.
Copyright © 2014
Harvey Mudd College Physics Department
241 Platt Blvd., Claremont, CA 91711
WebMaster (at) physics.hmc.edu
Last modified: 26 August 2012