VARA in Excel: Sample Variance and Explicit Boolean Coding

• Introduced: first introduction version is not established by the official history reviewed; the current product list is not a launch record.
• Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Checked: October 14, 2026; official VARA reference. Documentation and independently modeled examples; no native Excel execution or broader platform certification.

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

Microsoft lists 1–255 value arguments, with the first required. A reference can supply multiple observations. The official equation divides the sum of squared deviations from the included-data mean by n−1.

Use VARPA when the included data represents the complete population being described. Neither function can decide from the workbook alone whether the records are a sample or a population.

Set A2:A5 to 2,4,TRUE,FALSE, using =TRUE() and =FALSE() for the logical cells.

=VARA(A2:A5)

Expected result: 35/12, approximately 2.916666667. The encoded observations are 2,4,1,0, with mean 1.75.

The squared deviations sum to 8.75. With four observations, the sample denominator is 3, not 4.

For comparison, =VAR.S(A2:A5) sees only the two numeric reference values 2 and 4 and gives 2. It is a different accepted sample.

Put actual logical values TRUE, TRUE, FALSE and FALSE in C2:C5.

=VARA(C2:C5)

Expected sample variance: 1/3, approximately 0.333333333. The encoded mean is 0.5 and the squared-deviation sum is 1.

The population variance of those same encoded numbers is 0.25. Multiplying 0.25 by n/(n−1), here 4/3, gives 1/3. This is a denominator conversion for identical observations, not permission to change which records count.

For the first fixture, use B2 and fill through B5:

=IF(ISLOGICAL(A2),IF(A2,1,0),A2)

The helper contains 2,4,1,0. These numeric checks reproduce VARA:

=VAR.S(B2:B5)
=STDEV.S(B2:B5)^2

Both give approximately 2.916666667. COUNT of B2:B5 is four, while COUNT of the original mixed range is only two. A manual denominator based on the original COUNT would therefore be wrong.

This helper is scoped to the supplied number/Boolean fixture. It is not a validated conversion rule for text, errors or empty cells; audit those before applying it elsewhere.

On the numeric helper data, multiplying every observation by 10 produces 20,40,10,0. Its sample variance is approximately 291.666666667, one hundred times the original variance. Standard deviation instead grows by a factor of ten, from approximately 1.707825128 to 17.078251277.

Adding 100 to each encoded number gives 102,104,101,100. Its mean changes from 1.75 to 101.75, but the deviations and variance remain unchanged.

These transformations illustrate why variance units must be labeled. A variance calculated after converting a measurement unit cannot be compared directly with the original numerical variance.

Consider two numeric samples, 0,2 and 10,12. Each has sample variance 2. Their unweighted average variance is also 2.

Combining all four observations gives 0,2,10,12, mean 6, squared-deviation total 104, and sample variance 104/3, approximately 34.666666667. The shift between group means is part of the pooled spread.

Recompute from the intended sample, or use a justified pooled calculation retaining group sizes and means. Do not average subgroup variances and label the result as the variance of all observations.

The official page says actual TRUE maps to 1 and FALSE to 0, and says physically empty reference cells are ignored. Thus numeric 3,3 has variance 0, while adding FALSE gives encoded 3,3,0 with sample variance 3.

The text rules conflict: one passage maps text to zero, while another excludes reference text. Ordinary text, numeric text and formula-empty reference values remain unverified in this guide. Direct logical values and direct numeric text are documented as counted, but that does not resolve every reference coercion.

The sample equation requires at least two accepted observations. Exact native errors for insufficient observations or mixed error-containing ranges are held; the source generally says error arguments and untranslatable text cause errors.

STDEVA returns the square root of this sample variance on the same accepted data. Choose VAR.S when logical reference values should not be included.

• Microsoft VARA reference.
• Editorial testing standards.

Expected results are independent statistical/arithmetic models or explicitly documented rules, not captured native output. Examples use English function names and comma separators. Datasets and reporting policies are fictional.