Array
The array from which to derive the result.
Value
The value to find. If it is not present, the next smallest value and the next largest value are used to derive an approximate result using linear interpolation.
Significance
The number of significant digits in the result. If omitted, it is assumed to be 3.
Returns
The percentile rank of a number in an array, or a linearly interpolated value if the given value is not part of the array.
Returns the percentile rank of a number in an array. PERCENTRANK.EXC ({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 5) PERCENTRANK.EXC ({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 5) returns .5 (50%).
PERCENTRANK.EXC excludes percentiles below 1 / (n + 1) and above n / (n + 1) (where n is the number of elements of the array). To have 0 (0%) returned if the first element of the array is specified, and to have 1 (100%) returned if the last element of the array is specified, use PERCENTRANK.INC instead. Both functions map to the full range of the given array elements.
Related function
Use PERCENTILE.EXC to return the array element at the k th percentile, which can be seen as the reverse operation to PERCENTRANK.EXC. PERCENTRANK.EXC ({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 5) PERCENTRANK.EXC ({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 5) returns .5 and PERCENTILE.EXC ({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 0.5) PERCENTILE.EXC ({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 0,5) returns 5.
Examples
Returns .5 (50%).
Returns 5. PERCENTILE.EXC can be seen as the reverse operation to PERCENTRANK.EXC.