STDEVPA Function in Google Sheets (Formula Examples)

Published on

In this tutorial, we can learn how to use the STDEVPA Function in Google Sheets.

There are two types of standard deviations (SD). They are population standard deviation and sample standard deviation.

If you want to calculate as per type # 1, you can use the STDEVPA function in Google Sheets.

As far as I know, to calculate these two types, there are ten functions (five each) in Google Sheets, and they are as per the table below.

Table Contain the List of All the Standard Deviation Functions in Google Sheets:

Serial NumberFunction NameTypeRemarks
1DSTDEVPopulation SampleDatabase function
2DSTDEVPPopulationDatabase function
3STDEVPopulation Sample
4STDEVSPopulation Sample
5STDEVAPopulation SampleSetting text to zero
6STDEVPPopulation
7STDEV.PPopulation
8STDEVPAPopulation Setting text to zero
9SUBTOTAL(7 or SUBTOTAL(107Population SampleSTDEV – skips hidden/filtered out rows
10SUBTOTAL(8 or SUBTOTAL(108 PopulationSTDEVP – skips hidden/filtered out rows

You can find most of them listed in my Google Sheets Functions Guide.

Among the five functions for population standard deviation, the STDEVPA function stands out from the rest as it evaluates text strings as 0 (zero).

As a side note, to know the other four functions for population SD, please check the ‘Type’ and ‘Remarks’ columns of the above table.

Syntax and Arguments of the STDEVPA Function in Google Sheets

Syntax:-

STDEVPA(value1, [value2, …])

Arguments:-

value1 – The first value or array of the population.

value2 – Additional values or arrays to include in the population.

Features:-

The below points are applicable when an argument is an array or array/range reference.

The STDEVPA function in Google Sheets evaluates-

  • STDEVPA evaluates each text value as 0 (zero).
  • Boolean TRUE evaluates as 1 (one), whereas FALSE evaluates as 0 (zero).
  • Empty cells are ignored.
  • Error-values in the array or array reference cause errors in the STDEVPA formula output in Google Sheets.

You can get to practically know the features with the help of my examples below.

Formula Examples

Below I have included ten formula examples to the STDEVPA function in Google Sheets.

That may help you understand how the function evaluates texts, Boolean values, and blank cells in the array or array reference.

The following formula is in cell A12 for the range A2:A11.

=STDEVPA(A2:A11)

I have copied it for the other columns by dragging the cell A12 fill handle to its right-hand side.

Formula Examples to the STDEVPA Function in Google Sheets

What happens when you enter values directly within the STDEVPA formula (into the list of arguments) in Google Sheets?

  • STDEVPA will return #VALUE! error, if any of the entered values are text. You can test it as follows.
=stdevpa(5,10,"Test String")
  • The text representations of numbers will be counted.

E.g.:-

=stdevpa(5,10,"5")
  • Boolean TRUE evaluates as 1 where as FALSE evaluates as 0 (zero). The formula =stdevpa(5,10,TRUE) is equal to using =stdevpa(5,10,1).
  • Error values in the array cause errors.

Conclusion

The difference between STDEVP and STDEVPA functions in Google Sheets lies in evaluating the Boolean values (logical TRUE and FALSE) and text strings.

If you don’t want to consider them, use the former function, else the latter.

We can calculate the population standard deviation with the help of some other functions (which are not listed in the above table) and operators in Google Sheets.

If you go through them below, you can understand SD.

Let’s consider the values in A2:A11 (please refer to the image above).

Here are the steps to follow.

  1. In cell C2 insert =average(A2:A11) to return the average of the numbers in the array A2:A11.
  2. Using =ArrayFormula(sum((A2:A11-C2)^2)) in cell D2, we can calculate the difference of each value from the number in cell C2, i.e., average, and square it.
  3. Find the variance using =D2/count(A2:A11) in cell E2.
  4. Find the square root of the value in E2. You can use =sqrt(E2) for that and check the output.

That’s all about the STDEVPA function in Google Sheets. Thanks for the stay. Enjoy.

Related:- Mean and Standard Deviation Straight Lines on a Column Chart in Google Sheets.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.