fx
FunctionPedia
Quick Answer

VARPA Function

Population variance that also counts text and logicals as numbers.

✓ Excel✓ Google SheetsExcel All versions

Syntax

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

Parameters

ParameterDescriptionRequired
value1The first value, range, or reference in the population.Required
value2Additional values, ranges, or references (optional).Optional

Basic Example

Population variance of numeric values only

=VARPA(10,20,30,40,50)
Result200

With only numbers present, VARPA returns the same result as VARP.

Advanced Examples

Example 1: Text and TRUE are counted

A range that mixes numbers, text, and a logical

Text becomes 0 and TRUE becomes 1 before the calculation

=VARPA(10,20,30,TRUE,"apple")
Result: 131.36
The values used are 10,20,30,1,0; the mean is 12.2 and the population variance (sum of squared deviations 656.8 divided by 5) is 131.36.

Example 2: Contrast with VARP on a reference

Ignoring non-numeric cells

VARP ignores text and logicals inside a range

=VARP(A1:A5) where A1:A5 = 10,20,30,TRUE,"apple"
Result: 100
VARP sees only 10,20,30 (n=3), variance 100, whereas VARPA counts all five values for 131.36.

How VARPA Works

VARPA coerces every argument to a number (text to 0, TRUE to 1, FALSE to 0), then computes the population variance using the n denominator. Because it divides by n rather than n-1, it assumes the supplied values constitute the whole population.

1
Identify values
Collect the values, including any text or logicals you want counted.
2
Enter formula
Type =VARPA(range or values).
3
Press Enter
The population variance including non-numerics is shown.

Important Notes & Limitations

  • Counting text as 0 can distort the result if text cells are labels rather than true zeros.

  • Dividing by n assumes a full population; for a sample use VARA.

  • Requires at least one effective value, otherwise returns #DIV/0!.

Common Errors & Fixes

#DIV/0!No effective numeric values after coercion.

Fix: Provide at least one value that evaluates to a number.

Larger variance than expectedText labels were coerced to 0, pulling the mean down.

Fix: Use VARP when text cells should be ignored.

Download Practice File

Practice VARPA with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the VARPA function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Frequently Asked Questions

Does VARPA treat empty cells as 0?
No. Empty cells are ignored; only text and logicals are coerced (text to 0, TRUE to 1, FALSE to 0).
When should I use VARPA instead of VARP?
When your data includes logicals or text that genuinely represent 1/0 or zero, and you want them in the population.
What is the sample equivalent?
VARA, which uses the n-1 denominator instead of n.