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 Excel Percentrank.Exc function calculates the relative position, between 0 and 1 (exclusive), 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.
The syntax of the function is:
Where the function arguments are:
|array||-||The array of values within which you want to find the relative position of a specific value.|
|x||-||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]||-||(By default the returned percentage value is accurate to 3 significant digits).|
Cells B1-B4 of the spreadsheet below show examples of the Excel Percentrank.Exc Function, used to calculate the relative position of 4 different values within the array of values in cells A1-A9.
Note that, in the examples above:
Further examples of the Excel Percentrank.Exc function are provided on the Microsoft Office website
If you get an error from the Excel Percentrank.Exc function this is likely to be one of the following:
|#N/A||-||Occurs if the supplied value of x is smaller than the minimum, or greater than the maximum value in the supplied array.|
Also, the following formatting problem is encountered by some users:
If the result of your Percentrank.Exc 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:
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):
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: