STDEV.P function

The STDEV.P function in Excel calculates the standard deviation of a dataset, assuming the data represents the entire population. Standard deviation measures the amount of variation or dispersion of a set of values. The higher the standard deviation, the more spread out the values are from the mean.

Syntax

=STDEV.P(number1, [number2], ...)

Parameters

1. number1 (required): The first number, cell reference, or range that contains the data for which you want to calculate the standard deviation.
2. number2, … (optional): Additional numbers, cell references, or ranges that contain the data.

Key Points

• Population vs. Sample:
  • Use STDEV.P when you are working with data that represents the entire population.
  • If you are working with a sample of a population, use STDEV.S instead, as it applies Bessel’s correction to account for the fact that a sample tends to underestimate the population standard deviation.
• The formula for calculating the standard deviation of a population is:

  $$\sigma = \sqrt{\frac{1}{N} \sum_{i=1}^N (x_i – \mu)^2}$$Where:

  • $\sigma$ is the population standard deviation.
  • $x_i$ is each value in the dataset.
  • $\mu$ is the population mean.
  • $N$ is the number of values in the population.
• Non-numeric values (such as text or blank cells) in the data range are ignored by the function.

Examples

1. Basic Standard Deviation Calculation

For the dataset {5, 10, 15, 20, 25} in A1:A5:

=STDEV.P(A1:A5)

Result: 7.9057 (approx.)

• This is the standard deviation for the entire population of values.

2. Using a Range of Data

For the dataset {50, 60, 70, 80, 90, 100} in B1:B6:

=STDEV.P(B1:B6)

Result: 18.257 (approx.)

• This gives the standard deviation for the entire population.

3. Including Multiple Ranges

If your data is split across multiple ranges, such as C1:C3 containing {1, 2, 3} and D1:D3 containing {4, 5, 6}, you can combine them:

=STDEV.P(C1:C3, D1:D3)

Result: 1.7078 (approx.)

4. Handling Non-Numeric Data

For the dataset {5, 10, "N/A", 20, 25} in E1:E5, the text “N/A” will be ignored:

=STDEV.P(E1:E5)

Result: 7.9057

• The text is ignored, and the standard deviation is calculated based only on the numeric values.

5. Error When Data is Insufficient

If there are fewer than two numeric values in the range, the function will return a #DIV/0! error. For example, in a range with only one value:

=STDEV.P(A1)

Result: #DIV/0!

• A population standard deviation requires at least two data points to calculate.

Notes

• Error Handling:
  • #DIV/0!: This error occurs when there is only one numeric value or when there is no numeric data in the specified range.
  • #VALUE!: This error can occur if non-numeric values are included, but they are typically ignored unless all data is non-numeric.
• Use Cases:
  • The STDEV.P function is most appropriate when the dataset represents the entire population, such as when analyzing all data points in a study or census.
  • For sample data, use STDEV.S, which adjusts for the fact that a sample tends to underestimate population variability.

Related Functions

• STDEV.S: Calculates the standard deviation for a sample (use this for sample data).
• VAR.P: Returns the variance for a population (square of the standard deviation).
• VAR.S: Returns the variance for a sample.
• AVERAGE: Returns the mean of a dataset.
• NORM.S.DIST: Returns the cumulative distribution function (CDF) of the standard normal distribution for a z-score.

The STDEV.P function is essential in many fields such as data analysis, quality control, and finance, where understanding the spread or dispersion of a dataset is important for decision-making and analysis.