Home • People • Courses • Program • Research • Clinic • Goals • Kiosk • News
Understanding 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?
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.