Course outline / Statistical functions

Lesson 45 of 75 · Advanced · 4 exercises

Spread and variation

Measure how spread out numbers are with standard deviation and variance.

Start this lesson free

What you'll practise

  1. Standard deviation of a sample
  2. Standard deviation of everything
  3. Variance
  4. Spread compared with the average

Standard deviation of a sample

The average says where numbers sit; the standard deviation says how spread out they are. A small value means the numbers are close to the average.

Use STDEV.S when your data is a sample, such as eight weeks out of a year. That's the usual choice.

In cell D2, find the standard deviation of B2:B9 with STDEV.S.

Standard deviation of everything

When the data is every value there is, such as all eight weeks of a short campaign, use STDEV.P. It gives a slightly smaller answer.

In cell D2, find the standard deviation of B2:B9 with STDEV.P.

Variance

Variance is the standard deviation squared. VAR.S is the sample version.

In cell D2, find the variance of B2:B9 with VAR.S.

Spread compared with the average

To compare spread between data of different sizes, divide the standard deviation by the average. This is the coefficient of variation.

In cell D2, divide STDEV.S(B2:B9) by AVERAGE(B2:B9).

Try it in a real spreadsheet

Every step checks your answer instantly and tells you exactly what to fix.

Start the lesson

Function guides

← Conditional averages and extremesPercentiles and quartiles →