How to Use the VARA and VARPA Functions in Microsoft Excel to Calculate Variance

Variance is a foundational statistical measurement that quantifies the degree of dispersion or spread in a dataset relative to its arithmetic mean. In Microsoft Excel, standard variance functions such as VAR.S and VAR.P strictly ignore text entries and Boolean logical values (TRUE and FALSE) contained within referenced cell ranges. However, data analysts frequently encounter real-world surveys, operational audit logs, and binary test datasets where text labels and logical flags carry specific numeric weight.

To evaluate datasets that include logical flags and textual values without manually converting or cleaning every cell, Excel provides the specialized VARA (Sample Variance with Text and Logicals) and VARPA (Population Variance with Text and Logicals) functions. This guide explains their mathematical underpinning, syntax specifications, treatment of non-numeric data, and practical worked examples.

---

Comparing Excel Variance Functions

Selecting the appropriate variance function depends on two criteria: whether your dataset represents a sample or an entire population, and how non-numeric values must be treated:

Function	Dataset Scope	Treatment of Text and Logicals	Mathematical Denominator
VAR.S / VAR	Sample	Ignored completely (excluded from count)	\(n – 1\)
VAR.P / VARP	Entire Population	Ignored completely (excluded from count)	\(N\)
VARA	Sample	Evaluated: TRUE=1, FALSE=0, Text=0	\(n – 1\)
VARPA	Entire Population	Evaluated: TRUE=1, FALSE=0, Text=0	\(N\)

---

How VARA and VARPA Interpret Non-Numeric Values

Unlike their standard counterparts, the “A” series variance functions follow strict conversion rules when encountering non-numeric cells:

• Logical TRUE: Converted to a numerical value of 1.
• Logical FALSE: Converted to a numerical value of 0.
• Text Strings: Any text string (such as "Pending", "N/A", or an empty string "" produced by a formula) evaluates to 0.
• Empty / Blank Cells: Blank cells with no content are completely skipped and do not alter the divisor count \(n\) or \(N\).

---

Syntax and Argument Specifications

The VARA Function Syntax (Sample Variance)

Calculates variance based on a sample taken from a larger population:

=VARA(value1, [value2], ...)

The VARPA Function Syntax (Population Variance)

Calculates variance based on the entire population of data:

=VARPA(value1, [value2], ...)

Parameter Rules

• value1: Required. The first numerical, logical, or text argument, cell reference, or contiguous cell range.
• [value2], ...: Optional. Up to 254 additional arguments or ranges containing values to include in the variance calculation.

---

Step-by-Step Practical Demonstration

Consider an audit scenario assessing user task completion scores where binary statuses and descriptive text are mixed with numerical ratings:

Cell	Raw Input	Value Interpreted by VARA / VARPA	Value Interpreted by VAR.S / VAR.P
A1	10	10	10
A2	TRUE	1	Ignored
A3	FALSE	0	Ignored
A4	"Incomplete"	0	Ignored
A5	15	15	15

Calculating Sample Variance with VARA

If this dataset represents a sampled cross-section of users, enter the formula in cell B1:

=VARA(A1:A5)

Manual Step-by-Step Calculation:

1. The active evaluated array is \(\{10, 1, 0, 0, 15\}\).
2. Count of items \(n = 5\).
3. Sum of values: \(10 + 1 + 0 + 0 + 15 = 26\).
4. Sample Mean \(\bar{x} = \frac{26}{5} = 5.2\).
5. Sum of squared deviations \(\sum (x_i – \bar{x})^2\):
   • \((10 – 5.2)^2 = 4.8^2 = 23.04\)
   • \((1 – 5.2)^2 = (-4.2)^2 = 17.64\)
   • \((0 – 5.2)^2 = (-5.2)^2 = 27.04\)
   • \((0 – 5.2)^2 = (-5.2)^2 = 27.04\)
   • \((15 – 5.2)^2 = 9.8^2 = 96.04\)
   • Total Sum of Squares = \(23.04 + 17.64 + 27.04 + 27.04 + 96.04 = 190.8\)
6. Sample Variance = \(\frac{190.8}{n – 1} = \frac{190.8}{4} = \mathbf{47.7}\).

By contrast, executing =VAR.S(A1:A5) only considers cells A1 and A5, producing a completely different variance of 12.5.

Calculating Population Variance with VARPA

If cells A1:A5 encompass all existing users in the entire population, enter:

=VARPA(A1:A5)

The mathematical divisor becomes \(N = 5\) rather than \(n – 1 = 4\):

$$\text{Population Variance} = \frac{190.8}{5} = \mathbf{38.16}$$

---

Mathematical Formulations

Sample Variance Formula (VARA)

$$s^2 = \frac{\sum_{i=1}^{n} (x_i – \bar{x})^2}{n – 1}$$

Where \(\bar{x}\) is the sample mean (calculated using AVERAGEA) and \(n\) is the total count of non-blank arguments (calculated using COUNTA).

Population Variance Formula (VARPA)

$$\sigma^2 = \frac{\sum_{i=1}^{N} (x_i – \mu)^2}{N}$$

Where \(\mu\) is the true population mean and \(N\) is the total count of population observations.

---

Standard Deviation Equivalents

Variance is expressed in squared units of measurement. To revert variance back to the original units of measurement, compute the square root of the variance using the corresponding standard deviation functions:

Variance Function	Matching Standard Deviation Function	Square Root Equivalent Formula
=VARA(A1:A20)	=STDEVA(A1:A20)	=SQRT(VARA(A1:A20))
=VARPA(A1:A20)	=STDEVPA(A1:A20)	=SQRT(VARPA(A1:A20))

---

Common Errors and Troubleshooting

Error Code	Underlying Cause	Recommended Fix
#DIV/0!	Fewer than 2 values supplied to VARA, or 0 values supplied to VARPA.	Ensure the range contains at least 2 non-blank cells for sample calculations.
#VALUE!	Direct text argument in quotes that cannot be interpreted.	When typing values directly into the formula argument list, Excel expects numbers or logicals. Reference cells instead: VARA(A1:A10).
Unusually high variance result	Text strings being converted to zeros unintentionally.	If text labels such as "Missing" are skewing the mean, use VAR.S or filter out non-numeric values prior to calculation.