STDEVPA in Excel: Population Spread with Boolean Observations

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

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

Microsoft documents 1–255 value arguments, with only the first required; references and arrays can supply multiple observations. STDEVPA uses the population equation: take the square root of the average squared distance from the encoded population mean.

A complete list of observed records is not automatically the entire population of interest. If the task estimates a wider process from a sample, STDEVA uses the sample denominator n−1 instead.

Enter 2 and 4 in A2:A3, =TRUE() in A4 and =FALSE() in A5. This fixture represents four deliberately encoded, unitless scores.

=STDEVPA(A2:A5)

Expected result: approximately 1.479019946. TRUE and FALSE contribute 1 and 0, so the numeric population is 2,4,1,0. Its mean is 1.75, and squared deviations total 8.75. Dividing by n=4 gives variance 2.1875; taking its square root gives the standard deviation.

A numeric-only reference calculation =STDEV.P(A2:A5) instead sees 2 and 4, producing 1. The choice changes the population, not just the formula’s name.

Suppose all four records in the reporting group are TRUE, TRUE, FALSE and FALSE in C2:C5.

=STDEVPA(C2:C5)

Expected standard deviation: 0.5. The encoded values 1,1,0,0 have mean 0.5; all four squared deviations are 0.25, so population variance is 0.25.

For a complete binary population with TRUE fraction p, the mathematical variance is p×(1−p). With three TRUE values and one FALSE, p=0.75 and standard deviation becomes approximately 0.433012702. A smaller spread here does not mean a smaller TRUE fraction: the fraction increased while the values became more concentrated near 1.

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

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

The helper is 2,4,1,0. Numeric-only checks are:

=STDEV.P(B2:B5)
=SQRT(VAR.P(B2:B5))

Both give approximately 1.479019946. They expose the encoded observations and make the population convention explicit.

The helper is only validated for these numbers and actual logical values. Do not expand it over empty cells, text or errors without defining how those inputs should be treated.

A sample calculation on the same four encoded numbers gives approximately 1.707825128. Multiplying that sample result by SQRT(3/4) recovers 1.479019946. This conversion is valid only when both calculations use the same included observations and n really is four.

Duplicate the encoded numeric list so it becomes 2,4,1,0,2,4,1,0. The mean remains 1.75, squared deviations total 17.5, and dividing by n=8 still gives variance 2.1875. Population standard deviation is unchanged.

Selective duplication is different. For numeric values 0,0,1,1, population standard deviation is 0.5. Appending one more 1 gives 0,0,1,1,1, whose mean is 0.6 and standard deviation is approximately 0.489897949. The extra record changes the weights.

Check whether repeated rows are real observations or accidental duplication before interpreting a population statistic.

Microsoft says physically empty reference cells are ignored. A stored zero or actual FALSE is an included zero observation, so replacing missing data with FALSE changes both the distribution and its denominator.

For numeric 3,3, population standard deviation is 0. Adding FALSE produces encoded 3,3,0, with mean 2 and standard deviation approximately 1.414213562. A referenced physically empty cell does not supply that zero.

The source conflicts on text: it maps text to zero in one paragraph but says reference text is ignored in another. Ordinary text, numeric text and formula-empty text in references remain unverified here. Direct numeric text is explicitly documented as counted, but that does not resolve every reference case.

The source says error arguments and text that cannot be translated numerically cause errors. Exact native error classifications, an empty accepted population and the full error-containing-reference matrix are not certified by these examples.

Before reporting the result, record the population boundary, Boolean coding, missing-data policy and accepted observation count. COUNT on the original mixed fixture sees only two stored numbers; COUNT on the explicitly encoded helper sees four.

Use STDEV.P for a numeric-only referenced population. VARPA returns population variance in squared units; taking its square root agrees with STDEVPA for the same accepted data.

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