How to Use the STDEV Function in Google Sheets
=STDEV(value1, [value2, ...]). For an entire population use STDEVP.
What STDEV does
The average tells you where the middle is. Standard deviation tells you how far values typically stray from it. Two classes can both average 70, but if one has scores clustered tightly and the other has scores all over the place, STDEV shows the difference.
STDEV syntax
=STDEV(value1, [value2, ...])
Per Google's help page, value1 is the first value or range of the sample, and further values or ranges are optional. Several rules apply:
- STDEV calculates the sample standard deviation. For an entire population, use STDEVP.
- At least two values are required. Otherwise the function returns
#DIV/0!. - Text typed directly as an argument returns an error. Cell references that contain text are ignored. To treat text as 0, use STDEVA.
- STDEV equals the square root of the variance for the same data.
- The page lists a maximum of 30 arguments but notes that Google Sheets accepts any number.
A step-by-step example
Here are five scores in cells A1:A6.
| A | |
|---|---|
| 1 | Score |
| 2 | 10 |
| 3 | 20 |
| 4 | 30 |
| 5 | 40 |
| 6 | 50 |
=STDEV(A2:A6)
This returns the sample standard deviation of the five scores. If the five scores are the whole group you care about, rather than a sample drawn from a bigger group, use the population version instead:
=STDEVP(A2:A6)
The population result is a little smaller, because it divides differently than the sample version.
Common mistakes
- Using STDEV on a complete population. Choose STDEVP in that case.
- Passing a single value. You need at least two, or you get #DIV/0!.
- Typing text as an argument. It returns an error.
Try it yourself
Use the scores above (A2:A6). Type your formula into the box, starting with =, then press Check.
Exercise 1: Sample deviation
Write a formula that returns the sample standard deviation of A2:A6.
Exercise 2: Whole population
Write a formula that returns the population standard deviation of A2:A6.
What to learn next
- PERCENTILE: the value below which a share of the data falls.
- AVERAGE: find the mean of a range.
- MEDIAN: find the middle value of a range.
Syntax and rules in this guide were checked against Google's STDEV help page. See all guides.