VARPA function

The VARPA function in Excel calculates the variance of an entire population while considering numeric, text, and logical values. This function is similar to VAR.P, but it differs by including logical values and treating them as 1 (TRUE) or 0 (FALSE), and text values as 0 in the variance calculation.

Syntax

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

Parameters

• number1 (required): The first number, cell reference, or range containing data for which you want to calculate the population variance.
• number2, … (optional): Additional numbers, cell references, or ranges containing data for which the variance is to be calculated.

Key Points

• Population Variance: Like VAR.P, the VARPA function calculates variance for the entire population, not just a sample. If you are working with a sample and need sample variance, you would use VARA.
• Text Handling: Unlike VAR.P, which ignores text, VARPA treats text values as 0 in the calculation.
• Logical Values: Logical values are included in the calculation as well, with TRUE treated as 1 and FALSE treated as 0.
  • TRUE = 1
  • FALSE = 0
  • Text = 0
• Formula: The formula for calculating population variance with VARPA is:

  $$\text{Variance} = \frac{\sum (x_i – \mu)^2}{N}$$Where:

  • $x_i$ is each data point.
  • $\mu$ is the population mean.
  • $N$ is the total number of data points in the population.
• Units: Variance is expressed in squared units. For example, if the data represents meters, the variance will be in square meters.

Example Usage

1. Calculating Population Variance with Mixed Data Types

Suppose you have the following data representing ages (numeric), logical values (TRUE/FALSE), and text values (treated as 0):

To calculate the population variance, including logical values (TRUE = 1, FALSE = 0) and text (N/A = 0):

=VARPA(A2:A7, B2:B7)

• The function will treat TRUE as 1, FALSE as 0, and text (like “N/A”) as 0 in its calculation.

2. Handling Data with Text

If the data set includes text values that should be treated as 0 for variance calculation, VARPA can be used directly without needing to exclude the text values:

=VARPA(A2:A6)

Here, any text in the range A2:A6 will be treated as 0, and logical values will be treated as 1 or 0.

Notes

• Difference from VAR.P: The key difference between VAR.P and VARPA is that VARPA includes logical values (TRUE/FALSE) and text as part of the calculation, while VAR.P ignores these types of data.
• Population Variance: The function assumes that the data represents an entire population, so it divides by $N$, where $N$ is the total number of data points in the population.
• Text in Data: Since text values are treated as 0, they don’t affect the variance calculation, but their presence may still reduce the overall spread of the data.

Related Functions

• VAR.P: Calculates the variance for an entire population, but ignores text and treats logical values as 0.
• VARA: Calculates the sample variance and treats logical values as 1 and text as 0.
• STDEV.P: Calculates the population standard deviation, which is the square root of the population variance.
• STDEV.S: Calculates the sample standard deviation from a sample.
• AVERAGE: Calculates the mean (average) of a data set, which is used in variance calculations.
• COVARIANCE.P: Calculates the population covariance between two data sets.

The VARPA function is useful when you need to calculate the population variance for data that includes not just numeric values but also logical values and text. It’s typically used when you want a more inclusive calculation, accounting for all data types within a population.