The Excel PERCENTRANK Function

Related Functions:

In Excel 2010, the PERCENTRANK function has been renamed the PERCENTRANK.INC function.

Although it has been replaced, the Percentrank function is still available in Excel 2010 (stored in the list of compatibility functions), to allow compatibility with earlier versions of Excel.

Basic Description

The Excel PERCENTRANK function calculates the relative position of a specified value, within a set of values, as a percentage.

The syntax of the function is:

PERCENTRANK( array, x, [significance] )

Where the function arguments are listed in the table below:

array-The array of values, within which you want to find the relative position of a specific value.

The value that you want to calculate the relative position of.

(x must be within the range of the values in the supplied array, but it does not need to be exactly equal to one of the values. If x is not found in the array, the array values are interpolated to calculate the percentage rank).

An optional argument that specifies the number of significant digits that the returned percentage value is rounded to.

(By default the returned percentage value is accurate to 3 significant digits).

Percentrank Function Examples

Cells B1-B4 of the spreadsheet below show examples of the Excel Percentrank Function, used to calculate the relative position of various values within the array of values in cells A1-A9.

11=PERCENTRANK( A1:A9, 6.5 )
22=PERCENTRANK( A1:A9, 7, 5 )
34=PERCENTRANK( A1:A9, 8 )
46.5=PERCENTRANK( A1:A9, 14 )

Note that, in cell B2 of the spreadsheet above, when calculating the percentrank for the value 7, the function interpolates one third of the way between the percentrank for 6.5 (=37.5%) and the percentrank for 8 (=50.0%).

The resulting value, 41.6666666666667% is rounded down to 5 significant figures, as specified by the supplied [significance] argument.

Further examples of the Excel Percentrank function can be found on the Microsoft Office website

Percentrank Function Errors

If you get an error from the Excel Percentrank function this is likely to be one of the following:

Common Errors
#N/A-Occurs if the supplied x is smaller than the minimum, or greater than the maximum value in the supplied array.

Occurs if either:

  • The supplied [significance] value is < 1
  • The supplied array is empty.

Also, the following formatting problem is encountered by some users:

Common Formatting Problem

If the result of your Percentrank function is presented as a decimal, or shows 0%, this is likely to be due to the formatting of the cell containing the function.

This can therefore be fixed by formatting the cell as a percentage, with decimal places. To do this:

  • Use the mouse to select and then right click on the cell(s) to be formatted;
  • From the right-click menu, select the Format Cells ... option and ensure the Number tab is selected;
  • Under the Category heading, select the option Percentage, and in the box to the right (entitled Decimal Places), select the number of decimal places to be displayed;
  • Click OK.