Table of Contents
To calculate 2 standard deviations in Google Sheets, first enter the data you want to analyze into a column. Then, use the function “=STDEV.S(range)” to find the standard deviation of the data set. Next, multiply the standard deviation by 2 to get the value of 2 standard deviations. Finally, use the function “=AVERAGE(range)-2*STDEV.S(range)” to find the lower limit of the range and “=AVERAGE(range)+2*STDEV.S(range)” to find the upper limit of the range. This will give you the range of values within 2 standard deviations from the mean.
You can use the following formula to calculate the value of two standard deviations in Google Sheets:
=2*STDEV(A2:A14)
This particular example calculates the value of two standard deviations for the values in the cell range A2:A14.
This value might be of interest to you because in statistics the Empirical Rule states that for a given dataset with a , approximately 95% of all data values fall within two standard deviations of the mean.
The following example shows how to calculate two standard deviations in Google Sheets in practice.
Example: How to Calculate 2 Standard Deviations in Google Sheets
Suppose we have the following dataset in Google Sheets:
We can use the following formulas in various cells to calculate the mean, the value of two standard deviations, and the values that fall two standard deviations below and above the mean:
- D1: =AVERAGE(A2:A14)
- D2: =2*STDEV(A2:A14)
- D3: =D1-D2
- D4:=D1+D2
The following screenshot shows how to use these formulas in practice:
From the output we can see:
- The mean value of the dataset is 79.615.
- The value of two standard deviations is 15.221.
- The value that falls two standard deviations below the mean is 64.394.
- The value that falls two standard deviations above the mean is 94.837.
Assuming that this sample of data is of the larger population it came from and that the values in this population are normally distributed, we would assume that 95% of all data values in this population fall between 64.394 and 94.837.
Note: If you would instead like to calculate three standard deviations, simply replace the 2 in the formula in cell D2 with a 3.
Additional Resources