Quick Menu

Standard Deviation Calculations

This spreadsheet below shows two methods to determine the population standard deviation for a set of measurements.

Suppose you have 5 mass measurements for 10 mL samples of water.  The mass measurements are 9.97 g, 9.99 g, 10.01 g, 10.02 g and 9.98 g.

Manual Standard Deviation Calculations

  1. The first step is to input your data in a spreadsheet.  I placed this data in the cell range, B06:B10 (the green cells below).
  2. Calculate the average:  Using the AVERAGE() function, we calculate the average of our measurements in cell B11.
    Action

    Click on cell B11 and examine the formula in the Google sheet below

  3. Calculate the deviation from the average: In cells C6:C10, we calculate the deviation from the average.
    Action

    Examine the formula in cells C6 to C10, by clicking on them (e.g. =B6-$B$11).  This formula subtracts the average (cell $B$11) from each measurement.  Note the "$" in front of the column and row indexes ($B$11).  This is referred to as an absolute reference.  An absolute reference does not change when you copy it.  If there is no $, they are referred to as relative references and will translate as you copy them.

  4. Calculate the Variance: The variance is the square of the deviation.  In cells D6:D10 are the variance for each measurement.
    Action

    Examine the formula in cells C6 to C10.

  5. Calculate the Standard Deviation:  The standard deviation is the square root of the average variance.  In cell D11 is calculated the average variance and in cell D12 is the square root (standard deviation) of the measurements.
    Action

    Examine the formula in cells D11 and D12.

     

Spreadsheet function (STDEVP) to determine the Standard deviation

There is a built-in function in Google sheets (also in Excel - STDEV.P) to determine the standard deviation.  This function was used in cell D18 below.