The Excel PERCENTRANK.INC Function

PERCENTRANK.INC & PERCENTRANK.EXC Functions

In Excel 2010, the new PERCENTRANK.INC and PERCENTRANK.EXC functions both calculate the relative position of a specified value, within a supplied array of values, and return this value, as a percentage.

The difference between these two functions is that the Percentrank.Inc function calculates a value in the range 0 to 1 inclusive, whereas the Percentrank.Exc function calculates a value in the range 0 to 1 exclusive.

Basic Description

The Excel Percentrank.Inc function calculates the relative position, between 0 and 1 (inclusive), of a specified value within a supplied array.

The function is new in Excel 2010 and so is not available in earlier versions of Excel. However, Percentrank.Inc is simply a renamed version of the old Percentrank function, which is available in earlier versions of Excel.

The syntax of the Percentrank.Inc function is:

PERCENTRANK.INC( array, x, [significance] )

Where the function arguments are:

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

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).
[significance]-

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.Inc Function Examples

In cells B1-B4 of the spreadsheet below, the Excel Percentrank.Inc Function is used to calculate the relative position of different values within the array of values in cells A1-A9.

 Formulas:
 AB
11=PERCENTRANK.INC( A1:A9, 6.5 )
22=PERCENTRANK.INC( A1:A9, 7, 5 )
34=PERCENTRANK.INC( A1:A9, 8 )
46.5=PERCENTRANK.INC( A1:A9, 14 )
58 
69 
710 
812 
914 
 Results:
 AB
1137.5%
2241.666%
3450.0%
46.5100.0%
58 
69 
710 
812 
914 

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.Inc function are provided on the Microsoft Office website


Percentrank.Inc Function Errors

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

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

Occurs if either:

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

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

Common Formatting Problem

If the result of your Percentrank.Inc 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. This can be done using one of the following methods:

Method 1 - Percentage Formatting Through the Ribbon

The easiest way to change cell formatting is to select the cell(s) to be formatted and then select the percentage button from the 'Number' group on the Home tab of the Excel ribbon (see below):

Excel Format Cells As Percentage Button on Ribbon

Method 2 - Percentage Formatting Through the Format Cells Dialog Box

If you have an older version of Excel, that does not have the ribbon, you can change the formatting of an Excel cell via the following steps:

Excel Format Cells Dialog Box Percentage Option
  • Select the cell(s) to be formatted.
  • Right click on the selected cell, and from the right-click menu, select the Format Cells ... option.
  • The 'Format Cells' dialog box (shown on the rightabove) will be displayed. Ensure the Number tab of the dialog box is selected and from within the options in this tab, select Percentage.
  • An option will appear in the Format Cells box, that allows you to select how many decimal places you want to display.
  • Click OK.