The A-functions coerce text and logicals — and LibreOffice's VAR.P coerces them too

Excel ships two versions of several statistical functions: the plain one and the “A” one. MAX and MAXA, MIN and MINA, AVERAGE and AVERAGEA, VAR.P and VARPA. The difference is not what they compute — it is what they do with the cells that are not numbers. The plain variants ignore text and logical values inside a range. The A-variants coerce them: TRUE becomes 1, and FALSE and text both become 0, and every coerced cell is counted in the divisor.

We executed all of it. The A-variants behave identically in LibreOffice Calc 25.8.7.3 and in Google Sheets, and match Microsoft’s documented rule on all twenty-three cases we ran. The one thing that breaks is the function that was supposed to be the boring half of the pair: VAR.P, whose whole job is to ignore logicals, coerces TRUE to 1 in LibreOffice and returns a different number.

The documented rule, and where Microsoft contradicts itself

The A-variants’ coercion rule is stated the same way on every one of their help pages: “Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero).” That is the rule our test corpus asserts, and it is the rule both executed engines implement.

The awkward part is that the same pages also carry a second remark, one or two lines later: “Empty cells and text values in the array or reference are ignored.” For a range holding -3, -5, "abc" the two remarks give different answers — 0 under the first, -3 under the second — and Microsoft prints both on the same page for MAXA, MINA and VARPA.

What settles it is AVERAGEA, whose page carries the identical contradictory pair and a worked example that resolves it. Averaging 10, 7, 9, 2 and a cell reading “Not available”, the page publishes 5.6 and explains that “the cell with the text ‘Not available’ is used in the calculation”. That is (10+7+9+2+0)/5: the text contributed a zero and a slot in the divisor. Under the “ignored” reading the answer would have been 28/4 = 7. So the text-evaluates-as-0 reading is the one Microsoft’s own arithmetic uses, and that is the reading our expected values assert throughout.

Empty cells are the one thing genuinely ignored, and both readings agree there: a blank is not a zero. We ran that separately for MAXA, MINA and AVERAGEA, and all three engines’ worth of documented and executed values line up.

Executed: the four A-variants, twenty-three cases

Every case below was run as written. The Excel column is Microsoft’s documented behaviour as recorded in our test corpus — it is the yardstick, and we do not run Excel. The Google Sheets column is executed output from the dated Drive-import run of 2026-08-31. The LibreOffice column is executed output from Calc 25.8.7.3; every row also returned the same value in 24.2.0.3, 24.8.7.2 and 25.2.0.3.

Twenty-one of the twenty-three rows agree everywhere. Two things are worth pulling out.

Google Sheets refuses a literal string as a direct argument. =MAXA("abc",-5) and =MINA("abc",5) are the only A-variant cases where Sheets diverged: it returned #VALUE! where Microsoft documents 0 and LibreOffice returned 0. Note what separates these two rows from the ones above them: the text is typed directly into the argument list rather than sitting in a referenced cell. Sheets coerces text in a range exactly as documented (=MINA(A1:A3) over 3, 5, "abc" returned 0) and rejects it as a bare argument. That is a narrow difference and it fails loudly, which makes it the good kind.

LibreOffice hands back a boolean, not a number. For =MINA(A1:A5) over FALSE, 0.2, 0.5, 0.4, 0.8, our readback recorded LibreOffice’s result as a logical FALSE, and for =MINA(A1:A2) over two TRUEs as a logical TRUE. Google Sheets returned the numbers 0 and 1. Numerically these are the same values — the cases count as matching, and any arithmetic downstream is unaffected — but the cell’s type differs, so a display will read “FALSE” instead of “0”, and an ISNUMBER or TYPE test on that cell answers differently. That is the same type-versus-value seam we document in ISNUMBER on a boolean and the TYPE function.

Executed: the plain variants, and the one that misbehaves

The paired cases matter more than the A-variant cases, because the plain functions are the ones in everybody’s workbooks. Same corpus, same runs.

Notice how narrow the failure is. LibreOffice’s VAR.P gets the ten-number documented example right (678.84), gets a single data point right (0), gets an all-text range right (#DIV/0!), and — this is the important one — correctly ignores text in a range, returning 0.6667 for 1, 2, 3, "x" exactly as documented. Only the logical value is mishandled. Text is ignored as VAR.P requires; TRUE is coerced as VARPA requires. The function is applying half of one rule and half of the other.

Where 8.2222 comes from

The arithmetic makes the mechanism unambiguous, and it is worth doing because a variance is not a number anybody eyeballs for plausibility.

The documented result. VAR.P ignores the logical, so the data are 4 and 8. Mean 6; squared deviations 4 and 4; sum 8; population denominator n = 2. 8 / 2 = 4.

LibreOffice’s result. Coerce TRUE to 1 and the data are 4, 8, 1. Mean 13/3 = 4.3333; squared deviations 1/9, 121/9 and 100/9; sum 222/9 = 24.6667; denominator n = 3. 24.6667 / 3 = 8.2222 — which is the executed value to the fourth decimal place the case rounds to.

So the coerced TRUE does two things at once: it adds a data point far below the others, and it enlarges the divisor. Both push the answer away from the documented one, and the result is a variance a little over double the correct value on this input. On a real dataset the distortion depends entirely on how many logicals are in the range and where they sit relative to the mean, so there is no rule of thumb to apply and no ratio to correct by. The derivation above is our explanation of the measurement; what we executed is the value 8.2222.

Why this one is dangerous

A wrong error is annoying. A wrong number that looks right is expensive, and this is one of those. Three properties make it hard to catch:

It only fires when a logical is inside the range. A range of pure numbers — which is what almost every VAR.P is pointed at — returns the identical value in all three engines. So a workbook can pass a spot-check on ninety-nine columns and be wrong on the hundredth, where somebody dropped a =B2>100 flag column into the same block as the measurements.

Text does not trigger it. The obvious defensive test — put a stray label in the range and see whether the answer moves — passes. Text is ignored correctly. Only logicals leak through, and logicals are the one non-numeric value that does not look non-numeric when a column is formatted.

The magnitude is plausible. 8.2222 against 4 is not an obviously broken number; it is a variance that has roughly doubled. Anything built on it — a standard deviation, a control limit, a z-score, a confidence interval — will be wrong by a factor near 1.4 and will still print, sort and chart normally.

The nearest relative on this site is how each engine counts booleans, which is the same underlying question — is a TRUE in a range a value or not? — asked of the counting functions instead of the variance ones.

What to do about it

Find the exposure first. The formulas at risk are VAR.P (and its older spelling VARP) pointed at a range that can contain a logical value. Search your workbook for VAR.P( and VARP(, then look at what each range actually holds — not at what it is supposed to hold. Columns of comparison results, checkbox columns, and anything filled by a formula returning TRUE/FALSE are the sources.

Exclude logicals explicitly rather than relying on the function to do it. If the range genuinely mixes flags and measurements, the portable fix is to point the variance at numbers only — a separate column, or a range that stops before the flags start. A function that is documented to ignore something and observably does not is not a foundation to build a workaround on.

Do not reach for VARPA as the “safe” one. It is not a safer VAR.P; it is a different function that deliberately counts what the other one drops. Our five VARPA cases matched everywhere, so if VARPA is what you want, it travels. Just be sure it is what you want: on 1, 2, 3, "x" the two functions return 1.25 and 0.6667 respectively, and that difference is by design.

Reading the A-variants in the other direction is fine. Everything in the first table matched across both executed engines, so a workbook that leans on MAXA, MINA, AVERAGEA or VARPA to fold text and logicals into a calculation moves between LibreOffice and Google Sheets without a change — with the single exception of a bare literal string typed straight into MAXA or MINA, which Sheets rejects with #VALUE!. Put the text in a cell and the divergence disappears.

Honest limits

The Excel column throughout is Microsoft’s documented behaviour as recorded in our test corpus. We do not run Excel, and no value in that column is a measurement. Where Microsoft’s page contradicts itself — the “text evaluates as 0” versus “text values are ignored” pair on the MAXA, MINA and VARPA pages — we assert the reading that AVERAGEA’s worked example uses, and we say so on each case rather than pretending the documentation is clean.

The Google Sheets column is executed output from a dated run, because Sheets has no version number to pin: a formula-only .xlsx carrying no cached results goes to Google Drive, is recalculated by Google’s engine, and is exported back for readback, with a deterministic =1111+2222 canary in every sheet proving the recalculation happened. These functions were run on 2026-08-31. Google could change any of this tomorrow without changing a version number.

The LibreOffice column is executed output from Calc 25.8.7.3, and every case on this page returned the identical value in the other three builds we keep pinned — 24.2.0.3, 24.8.7.2 and 25.2.0.3. Those four are the only builds we tested, so we can say the VAR.P behaviour is stable across that range and nothing about when it started.

The scope limit that matters most. VAR.P is the only non-A statistical function in our corpus with an executed probe for a logical value inside a referenced range. Our MAX, MIN, AVERAGE, VAR, VAR.S and VARP cases probe stray text only, and they all pass. So we cannot tell you whether the same over-coercion affects any of them: we did not measure it, and we are not going to guess from one function to its neighbours. If your workbook has logicals sitting inside ranges fed to those functions, treat that as untested here and check it yourself — the formula checker and Migration Audit below are the fastest way to see what your own file does.

See our methodology for how recalculation is proven rather than assumed, the LibreOffice version support page for the cross-release stability picture, and the per-engine matrices for VAR.P, VARPA, MAXA, MINA and AVERAGEA.

Check before you migrate