Returns a value's rank as a percentage of a data set
Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
The PERCENTRANK Function is categorized under Excel Statistical functions. This function will return the rank of a value in a data set as a percentage. It can be used to evaluate the relative standing of a value within a data set.
In corporate finance, we can use PERCENTRANK to evaluate the standing of a specific test score among all the employee scores on an aptitude test.
The PERCENTILERANK function has been replaced with PERCENTRANK.EXC and PERCENTRANK.INC functions. Although it is still available for backward compatibility, it may not be available in future versions of MS Excel.
The PERCENTRANK function uses the following arguments:
Array (required argument) – This is the array or range of data that defines relative standing.
X (required argument) – The value for which we want to know its rank.
Significance (optional argument) – This is a value that identifies the number of significant digits for the returned percentage value. If omitted, PERCENTRANK uses three digits (0.xxx).
How to use the PERCENTRANK Function in Excel?
As a worksheet function, the PERCENTRANK function can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let’s consider an example:
Suppose we are given the following data:
The formula we use is:
We get the results below:
If we provide the formula:
We get the results below:
In the formula above, when calculating the PERCENTRANK for the value 5, the function interpolates and the resulting value is rounded down to two significant figures, as specified by the supplied [significance] argument.
Here, we formatted the results to percentage format. If the result of the function is presented as a decimal or shows 0%, it is likely to be due to the formatting of the cell containing the function. It can, therefore, be fixed by formatting the cell as a percentage, with decimal places, if required.
A few notes about the PERCENTRANK Function:
#NUM! error – Occurs if either:
If the array is empty
If significance < 1
If x does not match one of the values in an array, PERCENTRANK interpolates to return the correct percentage rank.
#N/A! error – Occurs if the given value of x is smaller than the minimum, or greater than the maximum, value in the supplied array.
Thanks for reading CFI’s guide to the Excel PERCENTRANK function. By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis and Excel modeling. To learn more, check out these additional CFI resources:
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Already have a Self-Study or Full-Immersion membership? Log in
Access Exclusive Templates
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.