PERCENTRANK.EXC function

PERCENTRANK.EXC(array, value, significance?) PERCENTRANK.EXC(array; value; significance?)

array — { Number }

The array from which to derive the result.

value — Number or { Number }

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 — Number or { Number } (optional)

The number of significant digits in the result. If omitted, it is assumed to be 3.

Returns — Number or { Number }

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

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

PERCENTILE.EXC({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 50%)PERCENTILE.EXC({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 50%)

Returns 5. PERCENTILE.EXC can be seen as the reverse operation to PERCENTRANK.EXC.