Basic Statistics in a Spreadsheet
The following instructions should work in most modern spreadsheet applications (for example, OpenOffice or Excel) since functions tend to be named the same (Open Office Function List, Excel Statistical Functions). If something doesn't seem to work as advertised, check out the help documentation of your particular application.
Mean, Median, and Standard Deviation
All of the single variable statistics are computed in the same way. As
an example we will compute the average of the list of 13 numbers stored in
column A. To do this we would type =AVERAGE(A1:A13) into a
blank cell in the spreadsheet and then press return (the colon ":" could be
read "to" as in "A1 to A13"). Upon pressing return the text shown in the
cell will be the average of the selected cells. If any of the data points
are altered, the average will automatically be updated to show the new
average.
Note: Your spreadsheet application may not highlight the A1:A13 cells.
| Function | Description | Example | Result |
|---|---|---|---|
| AVERAGE | Arithmetic Mean | =AVERAGE(A1:A13) | 2.85 |
| MIN | Minimum value | =MIN(A1:A13) | 1 |
| MAX | Maximum value | =MAX(A1:A13) | 5 |
| MEDIAN | Median | =MEDIAN(A1:A13) | 3 |
| STDEV | Sample Standard Deviation | =STDEV(A1:A13) | 1.21 |
| STDEVP | Population Standard Deviation | =STDEVP(A1:A13) | 1.17 |
"Results" given above are from the data shown in the above pictures.
Correlation
Correlation can also be computed, however now we need to input another
list of values. The function name is typically "CORREL" and each list of
cells needs to be separated by either a semi-colon ";" or a comma ",",
depending on your spreadsheet application (OpenOffice requires a semi-colon
=CORREL(A1:A13;B1:B13) while Excel requires a comma
=CORREL(A1:A13,B1:B13)).
