Calculate the standard deviation helps you to analyze the result of your AVERAGE calculation.

When you need to use the standard deviation?

Let's say you have a classroom with 10 students

• For the first exam, all the students have the same result 5
• For the second exam, 5 students have the mark 0 and 5 students have the mark 10

The average of the 2 exams is the same but the analyze is totally different.

In this situation, the standard deviation calculation will help you to have a better analyze of your averages.

Calculation of the standard deviation

To calculate the standard deviation, you don't need to know the formula. With Excel, you just have to call the STDEV function and you will return the result in a cell.

In this example, you simply write =STDEV(range) to return the standard deviation.

=STDEV(B2:B11) => 0

=STDEV(C2:C11) => 5.27

What does these results mean?

• 0 means that all values ​​in of the series is equal to the average. There is no gap (or deviation) between the average and the values of the series.
• On the other hand, for the second series, the result is very far from 0 and even exceeds the value of the average.

In other words, the standard deviation represents the dispersion of the data around the average.

The more the standard deviation is close to 0, the more the data is centered on the average.

Several formulas in Excel, why?

As you have certainly noticed, there are several functions in Excel to calculate the standard deviation.

In Excel 2010, Microsoft engineers have asked to signifiant statisticians to improve the speed of the functions and also their accuracy for large number of data.

• The STDEV.P calculates standard deviation based on the entire population (N). This function replace the former STDEVP function.
• The STDEV.S calculates standard deviation based on a sample (N-1). This function replace the former STDEV function.

The difference between the 2 calculation modes concerns the sample and therefore the divisor. If you calculate the standard deviation with the entire population, the divisor will be equal to N (with N, number of elements). When you calculate for a sample, the divisor is N-1.

Video to understand standard deviation

Maybe, you will understand why standard deviation is so important with this video