PERCENTRANK.INC function

PERCENTRANK.INC(array, value, significance?) PERCENTRANK.INC(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.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%):

PERCENTRANK.INC({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 3)PERCENTRANK.INC({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 3)

This formula returns 0 (0%):

PERCENTRANK.INC({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 0)PERCENTRANK.INC({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 0)

This formula returns 1 (100%):

PERCENTRANK.INC({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 10)PERCENTRANK.INC({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 10)

This formula specifies a number, 4.5, which is not part of the array:

PERCENTRANK.INC({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 4.5)PERCENTRANK.INC({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 4,5)

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

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

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

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

PERCENTRANK.INC({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 3)PERCENTRANK.INC({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 3)

Returns .3 (30%).

PERCENTRANK.INC({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 0)PERCENTRANK.INC({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 0)

Returns 0 (0%).

PERCENTRANK.INC({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 10)PERCENTRANK.INC({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 10)

Returns 1 (100%).

PERCENTRANK.INC({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 5.5)PERCENTRANK.INC({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 5,5)

Returns .55 (55%), which is a linearly interpolated value, halfway between the values 5 and 6 which are found in the array.