fx
FunctionPedia
Quick Answer

VARA Function

Sample variance that also counts text and logicals as numbers.

✓ Excel✓ Google SheetsExcel All versions

Syntax

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

Parameters

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

Basic Example

Sample variance of numeric values only

=VARA(10,20,30,40,50)
Result250

With only numbers present, VARA returns the same result as VAR.

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

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

Example 2: Contrast with VAR on a reference

Ignoring non-numeric cells

VAR ignores text and logicals inside a range

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

How VARA Works

VARA first coerces every argument to a number (text to 0, TRUE to 1, FALSE to 0), then computes the sample variance with the n-1 denominator. Because non-numeric entries count toward both the sum and the mean, they change the variance materially.

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

Important Notes & Limitations

  • Counting text as 0 can distort the result when text cells are labels, not true zeros.

  • Still requires at least two effective values, otherwise returns #DIV/0!.

  • The n-1 denominator still assumes a sample, not a full population (use VARPA for population).

Common Errors & Fixes

#DIV/0!Fewer than two effective numeric values after coercion.

Fix: Provide at least two values that evaluate to numbers.

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

Fix: Use VAR when text cells should be ignored.

Download Practice File

Practice VARA with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the VARA 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 VARA 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 VARA instead of VAR?
When your data includes logicals or text that genuinely represent 1/0 or zero, and you want them in the sample.
What is the population equivalent?
VARPA, which uses the n denominator instead of n-1.