STDEVA function

The STDEVA function in Excel calculates the standard deviation of a dataset, considering logical values (TRUE/FALSE) and text values as part of the data. It is similar to the STDEV.S function but with additional flexibility in handling non-numeric values.

Syntax

=STDEVA(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

• Difference from STDEV.S:
  • Unlike STDEV.S, which only considers numeric values, STDEVA includes logical values and text.
  • TRUE is treated as 1, and FALSE is treated as 0.
  • Text values are evaluated as 0. If you want to include text-based data in your calculation, STDEVA will do so, treating them as 0.
• Formula: The formula for calculating the standard deviation is the same as for a sample, with logical values and text included: $$s = \sqrt{\frac{1}{n-1} \sum_{i=1}^n (x_i – \bar{x})^2}$$ Where:
  • $x_i$ is each value in the dataset.
  • $\bar{x}$ is the sample mean.
  • $n$ is the number of values in the sample (including logical values and text as 1 and 0, respectively).

Examples

1. Standard Deviation with Numeric and Logical Data

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 standard deviation:

=STDEVA(A1:A6)

Result: 13.219 (approx.)

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

2. Standard Deviation with Text Data

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

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

To calculate the standard deviation:

=STDEVA(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

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 standard deviation:

=STDEVA(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:

=STDEVA(A1)

Result: #DIV/0!

• A 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:
  • STDEVA is particularly useful when you need to include logical or text values in the dataset, such as when you are working with survey data, where TRUE or FALSE responses represent some condition.
  • For numerical-only data, you might prefer using STDEV.S or STDEV.P to avoid treating text as 0.

Related Functions

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

The STDEVA function is ideal when working with datasets that may contain logical values (TRUE/FALSE) or text, and you want them included in the calculation of the standard deviation.