For example, a test score greater than 80% of all test scores is said to be at the 80th percentile. In this case, PERCENTRANK will assign a rank of .80 to the score. In the example shown, the formula in C5 is: where “data” is the named range C5:C12. Note: Microsoft classifies PERCENTRANK as a “compatibility function”, now replaced by the PERCENTRANK.INC function.
Inclusive vs. Exclusive
Starting with Excel 2010, the PERCENTRANK function has been replaced by two functions: PERCENTRANK.INC and PERCENTRANK.EXC. The INC version represents “inclusive” behavior, and the EXC version represents “exclusive” behavior. Both formulas use the same arguments.
Use the PERCENTRANK.EXC function to determine the percentage rank exclusive of the first and last values in the array. Use the PERCENTRANK.INC or PERCENTRANK to find the percentage rank inclusive of the first and last values in the array.
Notes
If x does not exist in the array, PERCENTRANK interpolates to find the percentage rank. When significance is omitted PERCENTRANK returns three significant digits (0.xxx)
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
