PERCENTRANK(array,x,[significance]) returns x’s fractional standing among numeric observations. The inclusive convention puts observed endpoints at zero and one. The omitted precision argument is three.
Try it
=PERCENTRANK({10,20,30,40},15) returns 0.166. Fifteen is halfway between the first two observations, so its rank is interpolated before the returned decimal places are truncated.
Quirks
A query outside the numeric data range gives #N/A in the current code. Significance below one gives #NUM!. Microsoft describes this argument as significant digits; the recorded helper instead truncates decimal places. Microsoft documents #NUM! for an empty array, while the current helper returns #N/A; that difference remains visible rather than silently promoted to an Excel rule. Duplicate-rank policy needs separate controls.
Two percent-rank controls retain decimal places rather than significant digits
In the retained catalogue capture, PERCENTRANK({37.15,46,54},38.59,6) returns 0.081355, and PERCENTRANK.INC({0,1,2,3,4,5,6,7,8,9,10,11,12},1) returns 0.083 with omitted significance. These results retain six and three decimal places respectively, and differ from keeping that many significant digits in the untruncated ranks. The scope is these two build 20527 calls.
Formula
Excel
Mathematical value
=PERCENTRANK({37.15,46,54},38.59,6) Excel 16.0.20527.20088 Current Channel, Windows 64-bit (NT 10.0.26200), AMD Ryzen 7 4800H, en-ZA, Workbook Compatibility Version 1Inputs and reference value
Typed formula.
0.081355 0x3fb4d3ae685db76b
—
=PERCENTRANK.INC({0,1,2,3,4,5,6,7,8,9,10,11,12},1) Excel 16.0.20527.20088 Current Channel, Windows 64-bit (NT 10.0.26200), AMD Ryzen 7 4800H, en-ZA, Workbook Compatibility Version 1Inputs and reference value
Sort the admitted numbers. For distinct observations give zero-based index i the fraction i/(n-1). Locate x exactly or bracket it by neighbouring values, then interpolate between their fractions. Finally apply trunc(rank*10^d)/10^d, using the admitted precision d. For the example the first two fractions are 0 and 1/3; their midpoint is 1/6, truncated to 0.166. The current exact-match branch uses a binary search; its duplicate selection is an implementation detail and its tie policy outside these controls remains open.
Rust code
Show the CalcFunc source of this function
Quoted from the public repository DnaCalc/DnaCalc, crates calc-func and calc-fml, at commit 9e1884369a67. Comments are the source's own. A kernel shared with other functions appears once, on its module page.
No implementation has been published with a Handbook test suite for this function.
Version differences
No same-input comparison between named Excel builds is recorded for this function here. The documented behaviour below is not a measurement of every Excel release.
The retained catalogue capture on Excel 16.0.20527.20088 Current Channel, Windows 64-bit (NT 10.0.26200), AMD Ryzen 7 4800H, en-ZA, Workbook Compatibility Version 1 records =PERCENTRANK({37.15,46,54},38.59,6) = 0.081355 (0x3fb4d3ae685db76b). Inputs were entered as the shown formula literal. Raw, saved and reopened views belong to one capture; this is not a comparison between builds.
Excel 16.0.20527.20088
Windows 64-bit, AMD Ryzen 7 4800H, en-ZA, Workbook Compatibility Version 1
The retained catalogue capture on Excel 16.0.20527.20088 Current Channel, Windows 64-bit (NT 10.0.26200), AMD Ryzen 7 4800H, en-ZA, Workbook Compatibility Version 1 records =PERCENTRANK({37.15,46,54},38.59,6) = 0.081355 (0x3fb4d3ae685db76b). Inputs were entered as the shown formula literal. Raw, saved and reopened views belong to one capture; this is not a comparison between builds.