VARPA in Excel: Population Variance with Logical Values

• 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 VARPA reference. Documentation and independently modeled examples; no native Excel execution or broader platform certification.

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

Microsoft documents 1–255 value arguments; the first is required, and a reference can supply several observations. The official equation uses n rather than the sample denominator n−1.

Use VARA when the records are a sample intended to describe a wider population. The number of visible rows does not determine the correct convention; the reporting question does.

Enter 2,4,TRUE,FALSE in A2:A5, using actual logical formulas =TRUE() and =FALSE() for the last two cells.

=VARPA(A2:A5)

Expected result: 2.1875. The encoded observations are 2,4,1,0, their mean is 1.75, and their squared deviations sum to 8.75. Dividing by all four observations gives 2.1875.

The square root is approximately 1.479019946, the population standard deviation. Variance and standard deviation describe related spread but have different units and different numerical scales.

=VAR.P(A2:A5) instead uses the numeric reference values 2 and 4, giving variance 1. Ignoring the logical cells changes the population being analyzed.

In C2:C5 place actual logical values TRUE, TRUE, FALSE and FALSE.

=VARPA(C2:C5)

Expected variance: 0.25. The encoded mean is 0.5 and each squared deviation is 0.25.

If the complete population changes to TRUE, TRUE, TRUE, FALSE, the mean becomes 0.75 and variance becomes 0.1875. For numeric binary coding, population variance is p×(1−p), where p is the fraction of ones.

Both an all-FALSE population and an all-TRUE population have variance 0. Zero spread therefore does not tell you which outcome occurred. Report the mean or TRUE fraction alongside variance when that distinction matters.

For the first mixed fixture, put this in B2 and fill through B5:

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

The numeric helper is 2,4,1,0. Independent spreadsheet checks are:

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

Both give 2.1875. COUNT of the numeric helper is four; COUNT of the original mixed range is two. Using the original COUNT as the denominator would not reproduce VARPA.

The helper is validated only for this number/Boolean fixture. Expanding it to text, errors or empty cells requires a separate input policy, not a silent conversion assumption.

Repeating the entire encoded population gives 2,4,1,0,2,4,1,0. The squared-deviation total doubles to 17.5, n doubles to eight, and population variance remains 2.1875.

By contrast, start with numeric 0,0,1,1, whose variance is 0.25. Add just one more 1, giving 0,0,1,1,1. The new mean is 0.6 and variance is 0.24. Selective repetition changes the empirical population weights.

A duplicated export row can therefore change a report even though it introduces no new distinct value. Review observation identity as well as the list of distinct numbers.

Adding 100 to every numeric helper value produces 102,104,101,100. The mean becomes 101.75, while variance remains 2.1875 because all deviations are unchanged.

Multiplying every value by −2 produces −4,−8,−2,0. Variance becomes 8.75, four times the original value. A negative scaling factor reverses order but still contributes its square to variance.

If the original values carried measurement units, the variance would carry their square. Use STDEVPA when you need spread in the original units, and label all unit conversions explicitly.

Microsoft says physically empty reference cells are ignored, while actual FALSE contributes zero. For numeric 3,3, variance is zero. Adding FALSE creates encoded 3,3,0, mean 2 and population variance 2. A physically empty referenced cell does not add that zero observation.

The source maps text to zero in one passage but says reference text is ignored in another. This guide holds ordinary text, numeric text and formula-empty reference behavior for a native check rather than choosing whichever passage is convenient. Direct logical values and direct numeric text are documented as counted, which does not resolve all reference cases.

The reference says error arguments and text that cannot be translated numerically cause errors. Exact native error labels, an empty accepted population and the full error-containing-range matrix were not established here. A missing population is not evidence of a valid zero variance.

For the same four encoded observations, sample variance is 35/12, approximately 2.916666667; multiplying by (n−1)/n gives population variance 2.1875. This conversion requires an identical accepted dataset.

Use VAR.P for numeric-only reference data. Record Boolean coding and missing-data decisions alongside any VARPA result.

• Microsoft VARPA 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.