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, that is available in earlier versions of Excel.
Syntax: PERCENTRANK.INC( array, x, [significance] )
|array||–||The array of values, within which you want to find the relative position of a specific value|
|x||–||The specific 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
Cells B1-B4 of the spreadsheet below show examples of the Excel Percentrank.Inc 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 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.
Percentrank.Inc Function Errors
If you get an error from the Excel Percentrank.Inc function this is likely to be one of the following:
#N/A – Occurs if the supplied x is smaller than the minimum, or greater than the maximum value in the supplied array.
#NUM! – the supplied [significance] value is < 1 or the supplied array is empty