Analysing this situation

Let's say you have a classroom with 10 students

• For the first exam, all the students had the same mark; 5
• For the second exam, the result is totally different
  • 5 students have the mark 0
  • 5 students have the mark 10

The average is the same for both exams (5), but of course, the analysis can't be the same. This is where the standard deviation will help you to have a better analysis of your average results.

Calculation of the standard deviation

1. Write the function =STDEV
2. Select your range of cells

=STDEV(B2:B11) => 0

=STDEV(C2:C11) => 5.27

What do these results mean?

• 0 means that all values ​​in the series are 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 result is close to 0, the more the data is centered on the average; 0 means no dispersion at all

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 significant statisticians to improve the speed of the calculations and also their accuracy for large numbers of data.

• The STDEV.P is based on the entire population (N). This function replaces the former STDEVP function.
• The STDEV.S is based on a sample (N-1). This function replaces 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.

Tutorial video