STDEV.S function

The STDEV.S function in Excel calculates the standard deviation of a sample dataset. Standard deviation is a measure of the amount of variation or dispersion of a set of values. The STDEV.S function is specifically used when your data represents a sample of a larger population, rather than the entire population.

Syntax

=STDEV.S(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

• Sample vs. Population:
  • Use STDEV.S when the data represents a sample from a larger population.
  • Use STDEV.P if your data represents the entire population.
• The formula for calculating the standard deviation of a sample is:

  $$s = \sqrt{\frac{1}{n-1} \sum_{i=1}^n (x_i – \bar{x})^2}$$Where:

  • $s$ is the sample standard deviation.
  • $x_i$ is each value in the dataset.
  • $\bar{x}$ is the sample mean.
  • $n$ is the number of values in the sample.

  The key difference between STDEV.S and STDEV.P is the denominator:

  • STDEV.S divides by $n – 1$ (Bessel’s correction), which corrects for the bias in the sample estimate.
  • STDEV.P divides by $n$ when calculating the standard deviation of the entire population.
• Non-numeric values (such as text or blank cells) are ignored in the dataset.

Examples

1. Basic Standard Deviation Calculation

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

=STDEV.S(A1:A5)

Result: 7.9057 (approx.)

• This is the standard deviation of the sample data.

2. Using a Range of Data

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

=STDEV.S(B1:B6)

Result: 18.257 (approx.)

• This gives the sample standard deviation.

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.S(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.S(E1:E5)

Result: 7.9057

• The text “N/A” is ignored, and the standard deviation is calculated based on the numeric values only.

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.S(A1)

Result: #DIV/0!

• A sample 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.S function is most appropriate when working with sample data, such as in statistical analysis, surveys, experiments, and market research.
  • For population data, use STDEV.P.

Related Functions

• STDEV.P: Calculates the standard deviation for a population (use this for population data).
• VAR.S: Calculates the variance for a sample (square of the sample standard deviation).
• VAR.P: Calculates the variance for a population.
• 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.S function is vital for calculating the variability of a sample dataset, providing insights into how spread out the values are around the mean. This is crucial in fields like finance, research, and quality control, where understanding the dispersion of a sample is key to making decisions or drawing conclusions.