Lesson 188: How to use the STDEV function in Excel

This article will explain how to calculate the standard deviation based on the entire population using the STDEV.P function in Excel and how to estimate the standard deviation based on a sample using the STDEV.S function in Excel.

How to use the STDEV function in Excel

Standard deviation is a number that tells you how far the numbers are from their mean.

1. For example, the numbers below have a mean of 10.

Explanation: the numbers are the same, which means there is no change. As a result, the numbers have zero standard deviations. The STDEV function is an old function. Microsoft Excel recommends that you use the new STEDV.S function.

2. The numbers below also have a mean of 10.

Explanation: the numbers are close to the mean. As a result, those numbers have low standard deviations.

3. The numbers below also have a mean (mean) of 10.

Explanation: the numbers are spread evenly. As a result, the numbers have high standard deviations.

STDEV.P

The STDEV.P (P stands for Population) function in Excel calculates the standard deviation based on the entire population. For example, you are teaching a group of 5 students. You have test scores of all students. The entire set consists of 5 data points. The STDEV.P function uses the following formula:

In this example, x1 = 5, x2 = 1, x3 = 4, x4 = 6, x5 = 9, μ = 5 (mean), N = 5 (number of data points).

1. Calculate the mean (μ).

2. For each number, calculate the distance to the mean.

3. For each number, square this distance.

4. Sum (∑) these values.

5. Divide by the number of data points (N = 5).

6. Take the square root.

7. Fortunately, the STDEV.P function in Excel can do all of these steps for you.

STDEV. WILL

The STDEV.S function (S stands for Sample) in Excel estimates the standard deviation based on a sample. For example, you are teaching a large group of students. You only have test scores of 5 students. The sample size is equal to 5. The STDEV.S function uses the following formula:

In this example, x1 = 5, x2 = 1, x3 = 4, x4 = 6, x5 = 9 (same numbers as above), x̄ = 5 (sample mean), n = 5 (sample size) .

1. Repeat steps 1-5 above but in step 5 divide by n-1 instead of N.

2. Take the square root.

3. Fortunately, the STDEV.S function in Excel can do all these steps for you.

Note: why do we divide by n – 1 instead of by n when we estimate the standard deviation based on a sample? Bessel’s correction states that dividing by n-1 instead of by n will give a better estimate of the standard deviation.

Variance

The variance is the square of the standard deviation. Sometimes it’s easier to use variance when solving statistical problems.

1. The VAR.P function below calculates the variance based on the entire set.

2. The VAR.S function below estimates the variance based on a sample.

3. VAR and VAR.S also give similar results.

Note: Microsoft Excel recommends using the new VAR.S function.

In addition, you can also view many other excel articles here.