STDEVPA function

The STDEVPA function in Excel calculates the standard deviation of a dataset, considering logical values (TRUE/FALSE) and text as part of the data, similar to the STDEVA function. However, STDEVPA is designed to handle entire populations rather than samples.

Syntax

=STDEVPA(number1, [number2], ...)

Parameters

1. number1 (required): The first number, cell reference, or range that contains the data for which you want to calculate the standard deviation.
2. number2, … (optional): Additional numbers, cell references, or ranges containing the data.

Key Points

• Population vs. Sample:
  • STDEVPA is used when the dataset represents an entire population. It differs from STDEVA in that it assumes the data represents the full set, rather than just a sample.
  • Logical values (TRUE/FALSE) are treated as 1 and 0, respectively.
  • Text values are evaluated as 0. If you want to include text-based data in your calculation, STDEVPA will treat them as 0.
• Formula: The formula for calculating the standard deviation of a population is: $$\sigma = \sqrt{\frac{1}{N} \sum_{i=1}^N (x_i – \mu)^2}$$ Where:
  • $\sigma$ is the population standard deviation.
  • $x_i$ is each value in the dataset.
  • $\mu$ is the population mean.
  • $N$ is the number of values in the population (including logical values and text as 1 and 0, respectively).

Examples

1. Standard Deviation with Numeric and Logical Data (Population)

For the dataset {10, 20, TRUE, 30, FALSE, 40} in A1:A6, where:

• Logical value TRUE is treated as 1.
• Logical value FALSE is treated as 0.

To calculate the population standard deviation:

=STDEVPA(A1:A6)

Result: 13.219 (approx.)

• The logical values are included as 1 and 0 in the calculation.

2. Standard Deviation with Text Data (Population)

Given the dataset {5, 10, "Text", 15, 20} in B1:B5, where:

• The text value “Text” is treated as 0.

To calculate the population standard deviation:

=STDEVPA(B1:B5)

Result: 6.702 (approx.)

• The text “Text” is treated as 0 in the standard deviation calculation.

3. Handling a Mix of Numbers, Logical Values, and Text (Population)

For the dataset {10, 20, TRUE, "Text", FALSE, 40} in C1:C6, where:

• Logical values: TRUE is treated as 1, FALSE as 0.
• Text value “Text” is treated as 0.

To calculate the population standard deviation:

=STDEVPA(C1:C6)

Result: 13.219 (approx.)

• The result includes the logical and text values treated as 1 and 0.

4. Error Handling with Insufficient Data

If there are fewer than two numeric values, or only non-numeric data is present, the function will return an error. For example, in a range with just one logical value:

=STDEVPA(A1)

Result: #DIV/0!

• A population standard deviation requires at least two values.

Notes

• Error Handling:
  • #DIV/0!: This error occurs when there are fewer than two values in the dataset (including logical and text values treated as 1 and 0).
  • #VALUE!: This error can occur if the arguments are invalid or contain incompatible data types.
• Use Cases:
  • STDEVPA is used when the dataset represents an entire population and includes logical values (TRUE/FALSE) and text values.
  • If you’re working with sample data, use STDEVA or STDEV.S instead.

Related Functions

• STDEV.S: Calculates the standard deviation for a sample, ignoring logical values and text.
• STDEVA: Calculates the standard deviation for a sample, including logical values and text (similar to STDEVPA but for sample data).
• STDEVP / STDEV.P: Calculates the standard deviation for an entire population, ignoring logical values and text.
• AVERAGE: Returns the mean of a dataset, ignoring text values.

The STDEVPA function is ideal for calculating the standard deviation of a dataset that represents an entire population, and it includes logical and text values in the calculation. It’s particularly useful when you’re dealing with complete data sets that contain a mix of numeric, logical, and text values.