Spread With STDEV and VAR

Two datasets can share the same average yet behave completely differently. One might cluster tightly around the mean; the other might swing wildly.

To capture that, you measure spread, also called dispersion. The two core tools are variance (VAR) and standard deviation (STDEV). Both answer the question: how far do the values typically stray from the average?

Variance is the average of the squared distances from the mean. Squaring does two things: it removes negative signs and it punishes big deviations more than small ones.

The downside is that variance is in squared units. If your data is in dollars, the variance is in dollars-squared, which is hard to interpret directly.

Standard deviation is simply the square root of the variance. Taking the square root brings the measure back into the original units.

So if salaries are in dollars, the standard deviation is also in dollars: "on average, values sit about X dollars away from the mean." That is why standard deviation is the more commonly reported number.

A crucial choice: are your numbers the whole population or a sample drawn from it?

• Sample: use STDEV.S and VAR.S (divide by n minus 1).
• Population: use STDEV.P and VAR.P (divide by n).

Almost always you have a sample, so the .S versions are the safe default.

Point STDEV.S at your data range and it returns the sample standard deviation. No sorting or pre-processing needed.

Here we measure how much daily sales in column A typically deviate from the average daily sale.

If you need variance itself, use VAR.S on the same range. Notice that squaring the standard deviation gives the variance back.

The formula below computes variance two ways; both return the same number, confirming the relationship between the two measures.

When your range truly contains every member of the group, not a sample, switch to the population versions.

For example, the exam scores of all 30 students in a class is a complete population, so STDEV.P is appropriate. The result is slightly smaller than STDEV.S would give.

A small standard deviation means values hug the average; a large one means they scatter widely.

For roughly bell-shaped data, about two-thirds of values fall within one standard deviation of the mean, and about 95% within two. So mean 100 with STDEV 10 implies most values sit between 90 and 110.

Is a standard deviation of 50 large? It depends on the scale. The coefficient of variation divides the standard deviation by the mean to give a relative measure you can compare across datasets.

A CV of 0.05 (5%) is tight; a CV of 0.80 (80%) is very volatile. Format the result as a percentage for easy reading.

Like other statistical functions, STDEV and VAR ignore text and blank cells inside the range, counting only numbers.

But a number you accidentally stored as text gets skipped, which can shrink your count and skew the result. Also remember: a single value (or none) gives a #DIV/0! error from STDEV.S because n minus 1 would be zero.

Team A and Team B both average 80 points. Team A has STDEV 4; Team B has STDEV 18.

Same average, very different consistency. Team A is reliable; Team B is streaky. The spread, not the center, tells you which team you can count on. This is exactly why analysts always report spread alongside the mean.

Test your grasp of spread functions.

You can now measure how scattered your data is:

• Variance = average squared distance from the mean (squared units).
• Standard deviation = square root of variance (original units).
• Use STDEV.S / VAR.S for samples, STDEV.P / VAR.P for full populations.
• The coefficient of variation (STDEV.S/AVERAGE) compares volatility across scales.

Always report spread next to the average.