What is the PERCENTRANK Function?
The PERCENTRANK function is categorized under Statistical functions. The function will return the rank of a value in a data set as a percentage of the data set. 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 test score among all employee scores in an aptitude test.
The PERCENTILERANK function’s been replaced with PERCENTRANK.EXC and PERCENTRANK.INC functions. Although the function is still available for backward compatibility, it may no longer be available in future versions of MS Excel.
The PERCENTILERANK function uses the following arguments:
- Array (required argument) – It is the array or range of data that defines relative standing.
- X (required argument) – It is the value for which we want to know the rank.
- Significance (optional argument) – It 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, PERCENTILERANK function can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let us 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 our PERCENTRANK 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.
Few notes about the PERCENTILERANK Function:
- #NUM! error – Occurs if either:
- If 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 important Excel functions! 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 resources: