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 two of the three engines we execute part company, silently, in the third decimal place of an ordinary percentile rank.

The rule Microsoft publishes is truncation, and Excel for the web implements it. The rule Google Sheets and LibreOffice 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 desktop 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 Excel-for-the-web column is our dated recalculation run on OneDrive, a different application from the desktop product and the only Microsoft engine here that is measured rather than quoted.

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 Google Sheets and LibreOffice returned; Excel for the web returned the truncated 0.555.

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 Google Sheets and LibreOffice returned 0.667 and Excel for the web returned 0.666. 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. Google Sheets and LibreOffice returned 0.4; Excel for the web returned 0.3.

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 all three executed 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 every executed engine. 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 Google Sheets and LibreOffice:

In Google Sheets and LibreOffice 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 of those engines on 2026-08-31.

Excel for the web is where the fourth row stops being a clean pass. On 2026-09-01 it returned 0.666 for =PERCENTRANK(A1:A4,30) — the truncated form of 2/3, against the 0.667 our corpus asserts and the 0.667 Sheets and LibreOffice both return. So the legacy spelling is not exempt: it takes the same significance rule as .INC and .EXC in that engine, and the corpus’s own expected value is the rounded one, which is why the row is scored as a mismatch there rather than as a divergence in the other three columns. We still have no measurement of the legacy spelling on a repeating-decimal rank in desktop Excel, and no page here claims one.

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 Sheets nor LibreOffice reproduces that, in two different directions. Excel for the web splits the difference: it raises #NUM! for the significance = 0 rows exactly as documented, and returns #N/A for the empty-array rows, which is what Sheets does too:

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 desktop Excel, and no value in that column is a measurement. What we can say is narrow and factual: the published figures and the Sheets/LibreOffice figures differ, and the published figures are the ones consistent with the truncation rule the same pages describe.

The Excel-for-the-web column is a measurement, of a different application: an .xlsx carrying no cached results is uploaded to OneDrive, recalculated by Excel for the web on open, and downloaded again for readback, with the same per-sheet canary discipline. It is dated rather than versioned because the service has no pinnable build. It reproduced the published truncation on every significance row we ran, which is corroboration of the documentation and nothing more: it is not evidence about the desktop product, and where it disagrees with the documented column — the two empty-array rows — we cannot tell whether the web engine diverges or the documentation is wrong about both.

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 Sheets/LibreOffice 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

A note on which Excel this is. The Excel column in the tables above is Microsoft’s documented behaviour for desktop Excel, as recorded in our test corpus — we do not run desktop Excel, and no value in that column is a measurement. Excel for the web is a different application with its own calculation engine, and that one we do run (recalculated on OneDrive, 2026-09-01). Its measured results are published on each function’s own page rather than in these guide tables. Because we have no desktop run to compare against, a disagreement between an Excel-web measurement and the documented column is genuinely ambiguous: it may mean the web engine diverges from the desktop one, or that the documentation is wrong about both. We do not claim to know which.