Excel - Microsoft Excel Percentrank.Exc 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 format of the function is : 

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

Where the function arguments are: 

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 a specific value, within the array of values in cells A1-A9. 

The formulas for the functions are shown in the spreadsheet on the left, and the results are shown in the spreadsheet on the right. 

Note that, in the example in cell B2 of the spreadsheet above, 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. 

Note also that, 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). 

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 

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 :