STDEVA in Excel: Sample Standard Deviation 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 STDEVA reference. Documentation and independently modeled examples; no native Excel execution or broader platform certification.

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

The first argument is required. Microsoft documents 1–255 value arguments and permits arrays or references. A range argument can hold several observations; the number of arguments is not the observation count.

The documented equation divides the sum of squared deviations by n−1, then takes the square root. The result has the same units as the encoded data. If the observations constitute the whole population being described, STDEVPA uses n instead.

Enter this deliberately mixed, unitless scoring fixture:

=STDEVA(A2:A5)

Expected result: approximately 1.707825128. The encoded sample is 2,4,1,0, whose mean is 1.75. Squared deviations are 0.0625, 5.0625, 0.5625 and 3.0625, totaling 8.75. Divide by 3 and take the square root.

For comparison, =STDEV.S(A2:A5) uses the numeric reference values 2 and 4 and gives approximately 1.414213562. The difference is not a rounding issue: the two functions are analyzing different observations.

Put TRUE, TRUE, FALSE and FALSE in C2:C5 as actual logical values, not quoted text.

=STDEVA(C2:C5)

The numeric model is 1,1,0,0, with mean 0.5 and squared-deviation total 1. Sample standard deviation is approximately 0.577350269, from the square root of 1/3.

That number describes variation in the encoded binary sample. It is not itself the fraction of TRUE observations, which is 0.5 here, and is not a percentage unless a separate reporting convention explains it.

For the first four-row fixture, put this in B2 and fill through B5:

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

The helper contains 2,4,1,0. These checks now agree:

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

Both give approximately 1.707825128. The helper makes the coding decision visible and allows numeric-only functions to check the result independently.

This helper is scoped to the supplied numbers and Booleans. It is not a general data-cleaning recipe: text, errors and physically empty cells need separate validation and a stated policy before extending the formula to them.

=COUNT(A2:A5) counts the two stored numbers, while =COUNT(B2:B5) counts all four encoded numbers. Therefore, using COUNT on the original mixed range as STDEVA’s n would supply the wrong denominator.

The same issue affects a minimum-sample guard. Inspect or explicitly encode allowed types first, then count the accepted numeric helper values. Do not let a two-number count silently stand in for four observations.

For an unchanged numeric sample, multiplying every value by 10 multiplies standard deviation by 10. The encoded values 20,40,10,0 therefore have standard deviation about 17.078251277. Merely displaying a different number format does not perform that transformation.

Microsoft explicitly says empty reference cells are ignored. Numeric zero and logical FALSE have a different role: both are observations with numeric value zero in this calculation.

For a separate numeric sample 3,3, standard deviation is 0. Adding an actual FALSE produces encoded 3,3,0, whose sample standard deviation is approximately 1.732050808. Adding a physically empty referenced cell instead does not add that zero observation.

The official page’s text rules are internally inconsistent: one statement maps text to zero, while another says reference text is ignored. This guide therefore does not certify ordinary text, numeric text or formula-empty text in mixed references. Check those cases in the target client or normalize them under a documented policy before relying on the result.

The sample equation needs at least two accepted observations. The exact native error for fewer observations and the complete error-containing-range behavior were not tested here; do not invent a valid zero standard deviation for a missing sample.

Microsoft says error arguments or text that cannot be translated numerically cause errors. Review those problems before wrapping the formula in a blanket error replacement.

Use STDEV.S for a numeric-only referenced sample and VARA when the squared-unit sample variance is the desired measure. State whether Boolean values were deliberately encoded when sharing the result.

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