Related Functions:

The PERCENTRANK.EXC function returns the relative position, between 0 and 1, exclusive, of a specified value within a supplied array.

  • This function was introduced in Excel 2010 and so is not available in earlier versions.

Syntax

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

Arguments

Argument Description
array The array or range of data with numeric values that defines relative standing
x The value for which you want to know the rank

  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] Optional. A value that identifies the number of significant digits for the returned percentage value. If omitted, PERCENTRANK.EXC uses three digits (0.xxx)

Examples

  A B C E
1 Data      
2 1      
3 3      
4 4      
5 7.5      
6 8      
7 8      
8 9      
9 10      
10 10      
11        
12 Formula Result Notes
13 =PERCENTRANK.EXC(A2:A10,2) 0.15 Returns the rank of the value 2 from the array contained in A2:A10
14 =PERCENTRANK.EXC(A2:A10,5) 0.328 Returns the rank of the value 5 from the array contained in A2:A10
15 =PERCENTRANK.EXC(A2:A10,5) 0.32 Returns the rank of the value 5 from the array contained in A2:A10 to 2 digits

Common Function Error(s)

Problem What went wrong
#NUM! Occurs if either:

  the supplied [significance] value is < 1
  the supplied array is empty
#N/A Occurs if the supplied x is smaller than the minimum, or greater than the maximum value in the supplied array