Skip to main content
Logo image

Contemporary Mathematics: Contemporary Mathematics at Nebraska

Michelle Homp, Alyssa Seideman, Sean Gravelle, Andrew Hayes, The Mabel Elizabeth Kelly Fund

Section A.3 How to compute the mean and standard deviation in both Microsoft Excel and Google Sheets

This tutorial uses the following data set. It contains the scores on a 5 point quiz for a class of 18 students.
Table A.3.1. Number of students who earned each score
Score 5 4 3 2 1 0
Count 6 2 3 0 2 5
You can follow the steps below by looking at the mean and standard deviation tab of the sample spreadsheet. If you are using Google Sheets, the example is found by clicking here
 1 
docs.google.com/spreadsheets/d/1_wnEQl5btBz6646XbhOrjyAYLRntf7dCunb-qRUJBDM/edit?usp=sharing
. Note that you will need to switch to the other tab after you follow the link. Also note that this is a read-only file; to modify it, you have to make a copy for yourself.
  1. Enter the value and count data (including the sum of the counts) into a fresh sheet, either by following directions of Section A.1 (for Excel) or Section A.2 (for Google Sheets) or simply by copying columns A and B from the bar graph sheet into a second sheet.
  2. Enter the headings “V*C”, “SqDev”, “SqDev*C”, “MEAN”, “VARIANCE”, and “STDEV” in cells C1, D1, E1, F1, F4, G1.
  3. Compute the mean as a weighted average of the values.
    1. Calculate the values times counts by entering “=A2*B2” in C2 and dragging the formula down to the end of the data.
    2. Compute the sum of values*counts by copying the formula from B9 to C9.
    3. Compute the mean by entering “=C9/B9” in F2.
  4. 4. Compute the square deviations from the mean by entering “=(A2-F$2)^2” in D2 and dragging the formula to the end of the data.
  5. Compute the variance as a weighted average of the square deviations.
    1. Calculate the SqDev times counts by entering “=D2*B2” in E2 and dragging the formula down to the end of the data.
    2. Compute the sum of SqDev *counts by copying the formula from C9 to E9.
    3. Compute the variance by entering “=E9/B9” in F5.
  6. Compute the standard deviation by entering “=SQRT(F5)” in G2.
  7. Improve readability by changing the number format. This is found in slightly different menu options in each program, as follows.
    1. In Excel: Highlight columns D through G and choose Format, Format Cells, Number from the Cells menu.
    2. In Google Sheets: Highlight columns D through G, then under the Format menu, go to the Number sub-menu (near the top) and select the option “Number”.
  8. Depending on the data, you might want to change the number of digits to be shown. For integer values, 2 digits is best.
PrevTopNext