PERCENTRANK.INC function
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.INC({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 5)PERCENTRANK.INC({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 5) returns .5 (50%).
This formula returns .3 (30%):
This formula returns 0 (0%):
This formula returns 1 (100%):
This formula specifies a number, 4.5, which is not part of the array:
4 and 5 are part of the array, though, enabling PERCENTRANK.INC to use interpolation to find an approximate match, .45 (45%).
The first number of the array has the percentile rank 0 (0%) and the last
number of the array has the percentile rank 1 (100%). To exclude percentiles
below 1 / (n + 1) and above n / (n + 1) (where
n is the number of elements of the array), use PERCENTRANK.EXC instead. Both
functions map to the full range of the given array elements.
Related function
Use PERCENTILE.INC to return the array element at the kth percentile, which can be seen as the reverse operation to PERCENTRANK.INC. PERCENTRANK.INC({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 5)PERCENTRANK.INC({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 5) returns .5 and PERCENTILE.INC({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 0.5)PERCENTILE.INC({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 0,5) returns 5.
Examples
Returns .5 (50%).
Returns 5. PERCENTILE.INC can be seen as the reverse operation to PERCENTRANK.INC.
Returns .3 (30%).
Returns 0 (0%).
Returns 1 (100%).
Returns .55 (55%), which is a linearly interpolated value, halfway between the values 5 and 6 which are found in the array.