Table of Contents
To calculate 3 standard deviations in Excel, you can use the STDEV.S or STDEV.P function. These functions will calculate the standard deviation of a set of data. To calculate 3 standard deviations, you can multiply the standard deviation by 3. For example, if the standard deviation is in cell A1, you can enter the formula “=A1*3” into another cell to get the value of 3 standard deviations. This calculation can help determine the spread or variability of a data set and identify outliers that may exist beyond 3 standard deviations from the mean.
You can use the following formula to calculate the value of three standard deviations in Excel:
=3*STDEV(A2:A14)
This particular example calculates the value of three 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 99.7% of all data values fall within three standard deviations of the mean.
The following example shows how to calculate three standard deviations in Excel in practice.
Example: How to Calculate 3 Standard Deviations in Excel
Suppose we have the following dataset in Excel:
We can use the following formulas in various cells to calculate the mean, the value of three standard deviations, and the values that fall three standard deviations below and above the mean:
- D1: =AVERAGE(A2:A14)
- D2: =3*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 78.92308.
- The value of three standard deviations is 24.23205.
- The value that falls three standard deviations below the mean is 54.69103.
- The value that falls three standard deviations above the mean is 103.1551.
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 99.7% of all data values in this population fall between 54.69103 and 103.1551.
Note: If you would like to calculate a different number of standard deviations, simply replace the 3 in the formula in cell D2 with a different number.
Additional Resources