Table of Contents
STDEV.P and STDEV.S are two functions in Microsoft Excel used to calculate the standard deviation of a set of data. The main difference between these two functions is the method they use to calculate the standard deviation. STDEV.P uses the population formula, which divides the sum of squared deviations by the total number of data points. On the other hand, STDEV.S uses the sample formula, which divides the sum of squared deviations by the sample size minus one. This means that STDEV.S gives a slightly higher value than STDEV.P, making it more suitable for smaller sample sizes. In summary, the difference between STDEV.P and STDEV.S lies in the formula they use to calculate the standard deviation, with STDEV.S being more appropriate for smaller sample sizes.
STDEV.P vs. STDEV.S in Excel: What’s the Difference?
There are three different functions you can use to calculate standard deviation in Excel:
1. STDEV.P: This function calculates the population standard deviation. Use this function when the range of values represents the entire population.
This function uses the following formula:
Population standard deviation = √Σ (xi – μ)2 / N
where:
- Σ: A greek symbol that means “sum”
- xi: The ith value in the dataset
- μ: The population mean
- N: The total number of observations
2. STDEV.S: This function calculates the sample standard deviation. Use this function when the range of values represents a sample of values, rather than an entire population.
This function uses the following formula:
Sample standard deviation = √Σ (xi – x)2 / (n-1)
where:
- Σ: A greek symbol that means “sum”
- xi: The ith value in the dataset
- x: The sample mean
- N: The total number of observations
3. STDEV: This function calculates the sample standard deviation as well. It will return the exact same value as the STDEV.S function.
Technical Note:
Since the formula for the population standard deviation divides by N instead of n-1, the population standard deviation will always be smaller than the sample standard deviation.
The reason the population standard deviation will be smaller is because if we know every value in the population, then we know the exact standard deviation.
However, when we only have a sample of the population then we have more uncertainty around the exact standard deviation of the overall population, so our estimate for the standard deviation needs to be larger.
The following example shows how to use these functions in practice.
Example: STDEV.P vs. STDEV.S in Excel
Suppose we have the following dataset in Excel:
The sample standard deviation turns out to be 9.127 and the population standard deviation turns out to be 8.896.
As mentioned earlier, the population standard deviation will always be smaller than the sample standard deviation.
When to Use STDEV.P vs. STDEV.S
In most cases, we’re unable to collect data for an entire population so we instead collect data for just a of the population.
Thus, we almost always use STDEV.S to calculate the standard deviation of a dataset because our dataset typically represents a sample.
Note that STDEV and STDEV.S return the exact same values, so we can use either function to calculate the sample standard deviation of a given dataset.