Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

PERCENTRANK Function

Returns a value's rank as a percentage of a data set

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.

 

Formula

=PERCENTRANK(array,x,[significance])

 

The PERCENTILERANK function uses the following arguments:

  1. Array (required argument) – It is the array or range of data that defines relative standing.
  2. (required argument) – It is the value for which we want to know the rank.
  3. 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:

 

Example

Suppose we are given the following data:

 

PERCENTRANK Function

 

The formula we use is:

 

PERCENTRANK Function - Example 1

 

We get the results below:

 

PERCENTRANK Function - Example 1a

 

If we provide the formula:

 

PERCENTRANK Function - Example 1b

 

We get the results below:

 

PERCENTRANK Function - Example 1d

 

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:

  1. #NUM! error – Occurs if either:
    1. If array is empty
    2. If significance < 1
  2. If x does not match one of the values in an array, PERCENTRANK interpolates to return the correct percentage rank.
  3. #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.

 

Click here to download the sample Excel file

 

Additional resources

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:

  • Excel Functions for Finance
  • Advanced Excel Formulas Course
  • Advanced Excel Formulas You Must Know
  • Excel Shortcuts for PC and Mac

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!