Blog

Excel provides two functions, STDEV.P and STDEV.S, to calculate the standard deviation of a set of data. Standard deviation is a measure of how spread out the numbers in a data set are. The STDEV.P function calculates the standard deviation based on the entire population, while STDEV.S is used when you have a sample of the population.

Example

Let’s consider a scenario where you have a dataset representing students' test scores in two different classes. You want to compare the spread of scores between the two classes using both STDEV.P and STDEV.S.

In this example, you can use STDEV.P and STDEV.S to calculate the standard deviation for each class’s scores. This will help determine which class has more variability in test scores.

Practice Exercise 1

Task: You have five products' sales data over three months. Use Excel to calculate the standard deviation of sales for each product using both STDEV.P and STDEV.S.

Practice Exercise 2

Task: The following table represents the weekly attendance numbers at a gym over six weeks. Calculate the standard deviation of attendance using both STDEV.P and STDEV.S to analyze the variability.

Solutions

For the solutions to both exercises, we will calculate the standard deviation using STDEV.P and STDEV.S. The formulas you will use are:

• STDEV.P: =STDEV.P(range)
• STDEV.S: =STDEV.S(range)

Solution for Exercise 1:

For Product A in Exercise 1:

• STDEV.P formula: =STDEV.P(B2:D2)
• STDEV.S formula: =STDEV.S(B2:D2)

Repeat these formulas for each product (B2 for Product A, B3 for Product B, etc.).

Solution for Exercise 2:

For Weekly Attendance in Exercise 2:

• STDEV.P formula: =STDEV.P(B2:B7)
• STDEV.S formula: =STDEV.S(B2:B7)

This will provide you with the standard deviation for the gym's attendance over six weeks.

Make sure to apply these formulas directly in Excel to get the results.