The Excel PERCENTRANK.EXC Function
PERCENTRANK.INC & PERCENTRANK.EXC Functions
The 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.
Function Description
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:
PERCENTRANK.EXC( 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.Exc Function Examples
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.
|
Formulas:
|
Results:
|
Note that, in the examples above:
- In cell B2 of the spreadsheet, when calculating the percentrank for the value 7, the function interpolates one third of the way between the (exclusive) percentrank for 6.5 (=40.0%) and the (exclusive) percentrank for 8 (=50.0%). The resulting value, 43.3333333333333% is rounded down to 5 significant figures, as specified by the supplied [significance] argument.
- As the percentrank calculation is exclusive of the values 0 and 1, the array's maximum value of 14 has a percentrank of 90% (=0.9).
For further examples of the Excel Percentrank.Exc function, see the Microsoft Office website
Percentrank.Exc Function Errors
If you get an error from the Excel Percentrank.Exc 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:
|
Also, the following formatting problem is encountered by some users:
Common Formatting Problem
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, if required.
The easiest way to format a cell to display a percentage 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):
This uses the default percentage formatting style for your computer system which might be exactly what you require.
However, if you want to change the number of decimal places that are displayed, this can be done within the 'Format Cells' Dialog Box, which is brought up by clicking on the dialog box launcher (shown in the above image).
Within 'Format Cells' dialog box:
- From within the 'Category' list on the left side of the dialog box, select the Percentage type.
- The Decimal places: option will appear in the dialog box. Use this to specify the number of decimal places that you want to display.
- Click OK.