VARA function

The VARA function in Excel calculates the variance of a sample while considering both numeric values and text (logical values are treated as 0). Unlike VAR.S, which only considers numeric values, VARA can handle more types of data, including logical values (TRUE/FALSE) and text (which is treated as 0).

Syntax

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

Parameters

• number1 (required): The first value, cell reference, or range that contains the sample data.
• number2, … (optional): Additional numbers, cell references, or ranges containing sample data.

You can provide multiple numbers, cell references, or ranges in the function.

Key Points

• Sample Variance: Like VAR.S, the VARA function calculates the variance for a sample, not for an entire population.
• Handling Non-Numeric Data:
  • Logical Values: Excel treats TRUE as 1 and FALSE as 0 when calculating variance.
  • Text: Excel treats text values as 0 for variance calculations.

  This makes VARA useful when your data set contains logical values or text that you want to account for as zeros in the variance calculation.

• Formula: The formula for calculating sample variance with VARA is:

  $$\text{Variance} = \frac{\sum (x_i – \bar{x})^2}{n – 1}$$Where:

  • $x_i$ is each individual data point.
  • $\bar{x}$ is the sample mean.
  • $n$ is the number of data points in the sample.
  • The main difference is that logical values are counted as 1 (TRUE) or 0 (FALSE) and text values are treated as 0.
• Units: Variance is expressed in squared units (e.g., if the data is in meters, the variance will be in square meters).

Example Usage

1. Calculating Sample Variance with Mixed Data Types

Suppose you have the following data representing test scores and some logical values indicating whether the student passed (TRUE) or failed (FALSE):

To calculate the sample variance, treating logical values (TRUE/FALSE) and numbers appropriately, use:

=VARA(A2:A6, B2:B6)

• The TRUE values in column B will be treated as 1, and the FALSE values will be treated as 0. The function will include these values when calculating the variance.

2. Variance with Text Data

If your data includes text (which is treated as 0), you can use VARA to calculate the variance without needing to exclude those text values. For example, if the data set includes text like “N/A” that should be considered as 0:

=VARA(A2:A6)

Here, any text in the range A2:A6 will be treated as 0 when calculating the variance.

Notes

• Text Handling: Unlike VAR.S, which ignores text, VARA treats text as 0. This is important when the data set includes non-numeric entries that you want to consider as zeros.
• Logical Values: TRUE is treated as 1, and FALSE is treated as 0, which can impact the variance calculation.
• Sample Variance: The VARA function calculates sample variance (not population variance), so it divides by $n – 1$ where $n$ is the number of data points in the sample.

Related Functions

• VAR.S: Calculates sample variance but only considers numeric values (ignores text and logical values).
• VAR.P: Calculates the variance for an entire population (use when the data represents the entire population, not just a sample).
• STDEV.S: Calculates the sample standard deviation, which is the square root of the sample variance.
• STDEV.P: Calculates the population standard deviation, which is the square root of the population variance.
• AVERAGE: Calculates the mean of a data set, which is required for variance calculation.
• COVARIANCE.S: Calculates the sample covariance between two data sets, which is related to variance.

The VARA function is useful for calculating sample variance when you have a mix of numeric, logical, and text data. It can be used in situations where logical values (TRUE/FALSE) or text entries are part of your data set and need to be considered as part of the variance calculation.