PERCENTRANK: Excel truncates to significance, LibreOffice and Google Sheets round

The significance argument of PERCENTRANK is the least interesting-looking argument in the statistics library. It does not change the ranking; it only says how many digits of the answer you want, and it defaults to three. It is also the argument on which Microsoft’s documentation and both engines we execute part company, silently, in the third decimal place of an ordinary percentile rank.

The rule Microsoft publishes is truncation. The rule both executed engines implement is rounding. On any input whose exact rank is a repeating decimal — 5/9, 6/9, and every other rank you get from a list of ten values — those two rules give different answers, and nothing errors, warns, or flags the difference.

Executed: the three rows that separate truncation from rounding

The Excel column is Microsoft’s documented behaviour as recorded in our test corpus — in these three rows it is a figure Microsoft prints in its own worked-example table. 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 Calc 25.8.7.3, with the same result on the other three pinned builds.

The .INC rows run over Microsoft’s published data 13, 12, 11, 8, 4, 3, 2, 1, 1, 1 in A2:A11; the .EXC row runs over its published data 1, 2, 3, 6, 6, 6, 7, 8, 9 in A2:A10. Both data sets are Microsoft’s own, and so are the three figures in the Excel column.

Why the exact rank is the whole story

PERCENTRANK.INC gives the r-th smallest of n values the rank r/(n-1), counting r from zero, so the smallest scores 0 and the largest scores 1. With ten values the denominator is 9, and a numerator of 5 or 6 produces a rank that never terminates in decimal. Five of the ten values are below 4, so =PERCENTRANK.INC(A2:A11,4) is exactly 5/9 = 0.55555…, and Microsoft even spells the arithmetic out on the page for a neighbouring row (“3 values in the set are smaller than 2, and 6 are larger… 3/(3+6)=0.333”).

Now cut that to three significant digits two different ways:

• Truncate 0.55555… → 0.555. This is what Microsoft publishes.
• Round 0.55555… → 0.556. This is what both engines returned.

The companion row runs the test in the other direction, which is what makes the reading unambiguous rather than a possible typo on one page: 6/9 = 0.6666… truncates to 0.666 and rounds to 0.667, and Microsoft publishes 0.666 while both engines returned 0.667. Two rows, two directions, one rule each.

PERCENTRANK.EXC shows it a third time, and much more loudly. Its interpolated example is exactly 0.381 (3.81/10, computed in rational arithmetic), and Microsoft’s page passes significance = 1 to it and prints 0.3, adding the gloss “displaying only 1 significant digit in the result (the default is 3)”. Cutting 0.381 to one significant digit gives 0.3; rounding it gives 0.4. Both engines returned 0.4.

The ranking itself is right — only the last step differs

This is worth stating plainly, because it bounds the damage. Every case in our corpus whose exact rank terminates at the requested precision passed in both engines, including the ones that pin which definition of “percent rank” each engine implements:

So the ordering, the interpolation, the inclusive-versus-exclusive endpoints and the arithmetic are all correct in both engines. The disagreement lives entirely in the step that turns a full-precision rank into a significance-limited one, which is why it is so easy to miss: an engine that had the ranking wrong would show it everywhere, and an engine that only rounds instead of truncating shows it nowhere except on repeating decimals.

The legacy PERCENTRANK is not the same test

PERCENTRANK without a suffix is still present in all three products and is the spelling most old workbooks use. Our corpus executes four cases for it, and all four match in both engines:

That is a clean pass, and it is also a warning about what a clean pass is worth. Three of those four values are exact at three digits no matter which rule you apply, and the fourth — 2/3 over four data points — is asserted in our corpus as 0.667, the rounded form, so it could never have distinguished the two rules either. The legacy file was written before the truncation question was asked, and it passes for that reason and no other. Those four cases were executed in LibreOffice on 2026-08-04 and in Google Sheets on 2026-08-29; the .INC and .EXC cases above ran in both engines on 2026-08-31. We have no executed case that puts the legacy spelling on a repeating-decimal rank, so this guide makes no claim about whether it truncates or rounds. If your workbook uses the unsuffixed name, treat it as untested on this point rather than as cleared.

The two documented errors are a separate divergence

Both Microsoft pages exclude two inputs in the same words — “If significance < 1, PERCENTRANK.INC returns the #NUM! error value” and “If array is empty, PERCENTRANK.INC returns the #NUM! error value” — and neither engine reproduces that, in two different directions:

D20:D25 is deliberately left blank in the test workbook; nothing is written into it. Two things follow. Google Sheets does not treat significance = 0 as an error at all — it returns the ordinary three-digit answer, so a guard that expects a zero or a stray blank to raise #NUM! simply never fires there. LibreOffice does raise an error, but the wrong one: #VALUE! rather than #NUM!, which is the same substitution we find across the corpus and document in every error code LibreOffice reports as #VALUE!. IFERROR and ISERROR are unaffected either way, because they catch every code; a branch on ERROR.TYPE(...)=6 is not.

What to audit

Find the repeating denominators, not the formulas. The rows that can move are the ones whose exact rank does not terminate at the requested number of digits, and that is a property of your data length, not of your formula text. For PERCENTRANK.INC the denominator is n-1; a list of 10, 4, 7, 8, 13 or 19 values (denominators 9, 3, 6, 7, 12, 18) produces repeating ranks for most numerators. For PERCENTRANK.EXC the denominator is n+1. Lists whose length makes the denominator a product of 2s and 5s — 11, 21, 26 values for .INC, giving 10, 20, 25 — can never show the difference.

Look at what consumes the rank. A rank that is displayed, or fed to a chart, or compared with a wide band, will not care about the third decimal. A rank compared against a hard threshold will: =IF(PERCENTRANK.INC(range,x)>=0.556,"top half","bottom half") flips on exactly the input this guide is about, and so does anything that does an equality test against a stored rank from a previous run.

Ask for full precision when you can. Nothing in the documented or executed behaviour above changes if you ask for more digits than the answer needs. A rank taken at high significance and rounded yourself — =ROUND(PERCENTRANK.INC(range,x),3) instead of the default three-digit significance — produces the rounded value in every engine, deliberately and visibly, rather than the engine’s undocumented choice. That is a construction we are describing from the behaviour above, not a case we ran; treat it as a suggested shape rather than as a measured result.

Do not expect the migration direction to matter. Because LibreOffice and Google Sheets agree here, moving a workbook between those two changes nothing. The exposure is a workbook authored against Excel’s documented output, or a figure copied out of Excel and compared to one of the other two.

Honest limits

The Excel column throughout is Microsoft’s documented behaviour as recorded in our test corpus, and in the sharp rows it is a figure Microsoft prints on its own PERCENTRANK.INC and PERCENTRANK.EXC pages. We do not run Excel, and no value in that column is a measurement. What we can say is narrow and factual: the published figures and the executed figures differ, and the published figures are the ones consistent with the truncation rule the same pages describe.

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 comes back as .xlsx for readback, with a deterministic =1111+2222 canary in every sheet proving the recalculation happened. The .INC and .EXC cases ran on 2026-08-31, the legacy PERCENTRANK cases on 2026-08-29.

The LibreOffice column is executed output from Calc 25.8.7.3, and every case above returned the identical result on 24.2.0.3, 24.8.7.2 and 25.2.0.3. Those four builds are the only ones we tested. Note that both dotted names are stored in a .xlsx as _xlfn.PERCENTRANK.INC and _xlfn.PERCENTRANK.EXC; LibreOffice reads those tokens and evaluates them, so nothing here is a storage-form artefact — see when a function exists but the file cannot say so for the cases where it is.

Scope. Seventeen cases across the three spellings is the entire PERCENTRANK corpus. Untested here: the legacy PERCENTRANK on any repeating-decimal rank, significance values above 3, non-integer significance, arrays containing text or logicals, duplicated values at the query point under .EXC, and whether the two engines’ agreement extends to PERCENTILE and QUARTILE, which have their own significance-free rounding story. See our methodology for how recalculation is proven rather than assumed, and the function pages for PERCENTRANK.INC, PERCENTRANK.EXC and PERCENTRANK for the per-engine support matrices.

Check before you migrate