How to use the STDEVPA Function in Excel

In this article, we will learn about how to use STDEVPA function in Excel to calculate the standard deviation  for the range of values.

STDEVPA function is a built-in function to calculate the standard deviation of the range of population data provided. The standard deviation for the set of values is calculated using the formula shown below.

Here

  1. x is the mean or average of the data.
  2. n is the number of values in the data.

There are newer and updated versions of this function used for a different set of values.

  1. STDEVP: This function used for dataset having population
  2. STDEV.S: This function used for data set have sample values
  3. STDEV.P: This function is newer version for STDEVP. Excel recommends this over STDEVP
  4. STDEVA: This function accepts the text or cell reference values which other STDEVA functions ignore.
  5. STDEVPA: This function accepts population as data set and text and logic_values are considered in data set.

Logica values : TRUE is considered as 1 whereas FALSE as 0.

Text : text values are considered as 0.

STDEVPA function in excel returns a number which is the standard deviation, considering population data having text or logical values.

Syntax of STDEVPA function:

=STDEVPA ( Value 1, [value 2], ...)

value 1 : First value ( necessary )

value 2 : Second value ( optional ). Upto 255 values can be added.

Example:

Let’s understand this function using an example.

Here we have a range of values from B1 : B20 cell and we need to get the standard deviation for the same using the below formula.

Use the formula

=STDEVPA ( B1 : B20 )

Here array argument to the function is given using the cell reference method.

Using the above formula in the cell to get the standard deviation of the values.

The Standard deviation for the range considering text values and Logic values is 25.94 whereas STDEV function returns 21.19 . This difference occurs as the value of n in the formula changes, as in STDEVPA case value of n is 20 ( includes text & logic values )  but for STDEV case value of n is 17 ( ignores text & Logic values ).

Notes:

  1. The function is used for the sample population data.
  2. If data represents a sample data, use the STDEVA function. STDEVA function is used to calculate the standard deviation for the sample data considering text and logical values.
  3. Arguments which are error values, returns error.
  4. The function returns the #DIV/0! (division) error, if there’s only text or logic values.

As you can see the STDEVPA function in excel returns values from the table field.

Hope you understood How to use the STDEV function in Excel. Explore more articles on Mathematical functions like STDEV and STDEV.P functions here. Please state your query or feedback in the comment box below.

Related Articles

How to use the STDEV.P function in Excel

How to use the STDEV function in Excel

How to use STDEV.S function in Excel

Popular Articles

Edit a dropdown list

If with conditional formatting

Vlookup by date

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.