Statistical
How MAXA, MINA, STDEVA, and VARA Coerce Booleans and Text vs. Numeric Aggregates
Audit and reproduce Excel's *A statistical functions (MAXA, MINA, STDEVA, STDEVPA, VARA, VARPA), where TRUE evaluates as 1, FALSE and text strings evaluate as 0, and blanks are ignored.
Why mixed boolean/text columns shift MAXA, MINA, and STDEVA
Standard numeric aggregates (MAX, MIN, STDEV.S, VAR.S) skip logical values and text labels completely. By contrast, Excel's *A variants coerce every non-blank cell in a range: numbers keep their value, TRUE becomes 1, and both FALSE and text strings (even numeric text like "0.95" or status notes like "Pending") become 0.
On fractional utilization or compliance data between 0 and 1, a single TRUE flag raises MAXA to 1.00 while a single "Pending" note pulls MINA down to 0.00 and inflates STDEVA/VARA dispersion. Projecting the coercion rule explicitly into a helper column makes the difference auditable.
| Unit | Raw Entry (B) | Numeric-Only View | *A Coerced Value (C) | Effect on *A Stats |
|---|---|---|---|---|
| Line-1 | 0.65 | 0.65 | 0.65 | Included as 0.65 |
| Line-2 | TRUE | Ignored | 1.00 | Pushes MAXA from 0.85 to 1 |
| Line-3 | "Pending" | Ignored | 0.00 | Pulls MINA from 0.40 to 0 |
| Line-4 | 0.40 | 0.40 | 0.40 | Included as 0.40 |
| Line-5 | 0.85 | 0.85 | 0.85 | Included as 0.85 |
Explicit *A Coercion Mapping & STDEVA / VARA Evaluation
Maps non-blank entries in B2:B6 to their exact *A numeric equivalents in C2:C6 so MAX(C2:C6), MIN(C2:C6), STDEV_S(C2:C6), and VAR_S(C2:C6) reproduce MAXA, MINA, STDEVA, and VARA.
=ROUND(STDEV_S(C2:C6), 4)Compare Against Numeric-Only STDEV.S
Evaluates STDEV_S directly on B2:B6 (ignoring TRUE and "Pending") to quantify how much text/boolean coercion inflates volatility.
=ROUND(STDEV_S(B2:B6), 4)Try the example
Use the original values or change them to test your understanding.