The Excel PERCENTRANK.EXC Function

Home » Excel-Built-In-Functions » Excel-Statistical-Functions » Excel-Percentrank-Exc-Function

Search this site:

Custom Search

Related Function:

PERCENTRANK.INC

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.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:

array

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

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.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.

Formulas:

Examples of use of the Excel Percentrank.Exc Function

Results:

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).

Further examples of the Excel Percentrank.Exc function can be found on the Microsoft Office website

Trouble Shooting

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 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.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 :

Use the mouse to right click on the cell containing the function
Select the Format Cells ... option and ensure the Number tab is selected
Under the Category heading, make sure the option Percentage is selected, and in the box to the right (entitled Decimal Places), select the number of decimal places to be displayed.
Click OK

Return to the Excel Statistical Functions Page

Return to the List of All Built-In Excel Functions

Disclaimer

Privacy Policy