Google Sheets Cheat Sheet

How To Calculate Standard Deviation In Google Sheets

There are a number of functions that can calculate standard deviation in Google Sheets.

Which function to use depends on your data:

ABC
1SamplePopulation
2Non-numeric = ignored=STDEV(A:A)=STDEVP(A:A)
3Non-numeric = 0=STDEVA(A:A)=STDEVPA(A:A)

Read on for a more detailed explanation.

Google Sheets allows you to quickly find the standard deviation of a data set.

It's actually quite a powerful tool to complete statistical analysis.

Before you can get the standard deviation, you need to ask yourself two questions about your data:

  1. Is your data a sample or a population?
  2. How do you want to handle non-numeric data (e.g. text and Boolean)?

If you've already answered these questions, you can skip to the functions or example calculation.

A data set's standard deviation simply indicates how much each data point deviates from the mean (the average).

A larger standard deviation means the data points are more dispersed.

A smaller standard deviation means the data points group closely around the mean.

shows are chart of two normally distributed data sets with the same mean but difference standard deviations. the smaller standard deviation set is closely grouped near the mean and the larger standard deviation set is more dispersed

Question 1: Standard Deviation Of A Sample Or Population?

Standard deviation is calculated differently based on whether the data is a sample from a larger population or the entire population itself.

Here are a couple of examples to clarify the difference between a sample and a population:

A school year group of 150 students sits a 100-question multiple choice test.

A teacher at this school has access to only the results of their 30 students.

The teacher wants to calculate:

  1. The standard deviation of their class
  2. The standard deviation of the year group

For the standard deviation of the class, the teacher should use the population standard deviation equation.

Why?

Because the teacher has access to all of the relevant data points. None are being left out.

For the standard deviation of the year group, the teacher should use the sample standard deviation equation.

Why?

Because the teacher has access to only 30 of the 150 results. They have a sample of a larger population and some data points from the population will be excluded from the calculation.

Here's the difference between the equations:

Sample Standard Deviation Equation

shows the equation for a sample's standard deviation

Where:

Population Standard Deviation Equation

shows the equation for a population's standard deviation

Where:


Essentially: When you're calculating the standard deviation of a population you divide by n and when it's a sample you divide by n-1.

Google Sheets has functions for both of these equations.

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

Question 2: Handling Non-Numeric Data

Standard deviation functions only work with numbers.

If there is non-numeric data in your set, you need to tell Google Sheets what to do with it.

These non-numeric values (e.g. text or Boolean data) can be:

  1. Ignored (treated as a blank cell)
  2. Included in the calculation as zero (0)

From the example above with the teacher, students who weren't at school when the test took place are included in the data set with "Absent" listed as their result.

The teacher would want this text to be ignored, otherwise the standard deviation would increase significantly because a number of students would register a score of zero when in reality they haven't taken the test yet.

How To Calculate Standard Deviation In Google Sheets

Now that you've answered the questions above you can use this table to determine which function you should use:

ABC
1SamplePopulation
2Non-numeric = ignored=STDEV(A:A)=STDEVP(A:A)
3Non-numeric = 0=STDEVA(A:A)=STDEVPA(A:A)

For the STDEV and STDEVP functions, you can also use STDEV.S and STDEV.P (respectively).

This notation actually makes things clearer as the letter after the period immediately indicates whether the function calculates the standard deviation or a Sample or Population.

All of the formulas work the same:

=STDEV(value1, [value2, ...])

Simply provide your data as arguments and get the relevant standard deviation returned.

It's best to stick to either arguments as references or values instead of a mix of both.

Why?

Two reasons:

1. Text from cell and range references is ignored (or treated as 0) but including text arguments directly like:

=STDEV("text",5,10)

Throws the following error:

Function STDEV parameter 1 expects number values. But 'text' is a text and cannot be coerced to a number.

2. Another odd behaviour is that the STDEV and STDEVP functions ignore Boolean values in cell and range references but not when entered as arguments directly.

You need to provide at least two numbers as arguments.

If you don't you will get a #DIV/0! error:

Evaluation of function STDEV caused a divide by zero error.

Example Standard Deviation Calculation In Google Sheets

Here's a the results from a class of 10 from the school example described above:

AB
1StudentResult
2Darrel86
3Sylvia43
4Doris44
5Samantha70
6AdrianAbsent
7Oliver76
8Brianna99
9Clare97
10Quentin71
11Ramona68

Here are the standard deviation outputs for the various formulas available:

ABCD
1StudentResultFormulaOutput
2Darrel86=STDEV(B2:B11)20.02498439
3Sylvia43=STDEVP(B2:B11)18.87973634
4Doris44=STDEVA(B2:B11)29.74035791
5Samantha70=STDEVPA(B2:B11)28.21418083
6AdrianAbsent
7Oliver76
8Brianna99
9Clare97
10Quentin71
11Ramona68

Remember from earlier that for the standard deviation of the class itself the best option is STDEVP (18.87973634) while for the year group it's STDEV (20.0249843).


FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.