May 17, 2021

How to use STDEVPA Function in Excel

The Excel STDEVPA function calculates the standard deviation for an entire population. Unlike the STDEVP function, STDEVPA evaluates text and logicals that appear in references. To calculate standard deviation for a sample, use STDEVA or STDEV.S.

Syntax:= STDEVPA (number1, [number2], …)

The STDEVPA function syntax has the following arguments:

1. Value1 (required argument) – This is the first number argument corresponding to a population.
2. Value2 (optional argument) – This is a number argument that corresponds to the population.

Example: Let’s look at some Excel STDEVPA function examples and explore how to use the STDEVPA function as a worksheet function in Microsoft Excel:

Our first table consists of 2 columns: “Date” (column A) and “Amount” (column B). The second one has the value of standard deviation calculated with STDEVP function and with the result placed in the cell B17. The idea is to get the standard deviation of sales data based on population with STDEVPA function and to place the result in the cell B16.

Syntax:  =STDEVP(B3:B14)

Result: 2853068.524

We want to get the standard deviation of the data in the “Amount” column. In order to better explain the formula, we will create a named range Amount for cell range B3:B14.

Syntax: =STDEVPA(B3:B14)

Result: 6302556.8

STDEVPA function calculates the standard deviation of the population. This function evaluates logical values and text in the calculation of the standard deviation. In our example text is interpreted as zero and the formula result in the cell B16 is \$6302556.8. This result means that amount values differ \$6302556.8 from the mean of the amount data range.

READ:  How to use QUARTILE.INC Function in Excel

Note:

• STDEVPA assumes that its arguments are the entire population. If your data represents a sample of the population, you must compute the standard deviation by using STDEVA.
• For large sample sizes, STDEVA and STDEVPA return approximately equal values.
• The standard deviation is calculated using the “n” method.
• Arguments can be the following: numbers; names, arrays, or references that contain numbers; text representations of numbers; or logical values, such as TRUE and FALSE, in a reference.
• Text representations of numbers that you type directly into the list of arguments are counted.
• Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero).
• If an argument is an array or reference, only values in that array or reference are used. Empty cells and text values in the array or reference are ignored.
• Arguments that are error values or text that cannot be translated into numbers cause errors.
• If you do not want to include logical values and text representations of numbers in a reference as part of the calculation, use the STDEVP function.
• STDEVPA uses the following formula:

where x is the sample mean AVERAGE(value1,value2,…) and n is the sample size.