Calculating the standard deviation helps you explain your AVERAGE calculation
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
- Write the function =STDEV
- Select your range of cells
- For the first Exam the Standard deviation is =STDEV(B2:B11) => 0
- For the second exam, the result is =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 average value.
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, several functions in Excel calculate the standard deviation.
In Excel 2010, Microsoft engineers asked significant statisticians to improve the speed of the calculations and their accuracy for large numbers of data.
- The STDEV.P is based on the entire population. This function replaces the former STDEVP function.
- The STDEV.S is based on a sample. 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 equal N (with N, the number of elements). When you calculate for a sample, the divisor is N-1.
19/02/2021 @ 12:05
This is very knowledgeable information for students, I gave you a favor in the form of an online calculator.