PERCENTRANK function - The function returns the rank of the value in Excel

In sorting and statistical data, you often have to rank the objects according to a certain criteria. The following article details how to use the PERCENTRANK function to find the rank of the value.

Description: The function returns the rank of a value in a dataset according to a certain criteria. For example, use the PERCENTRANK function to evaluate the exam results of candidates in the exams.

Syntax: PERCENTRANK (array, x, [significance]) .

Inside:

- array: An array of data or data range containing the data to be evaluated, a required parameter.

- x: The value you want to determine rank, is a required parameter.

- significance: Optional parameter used to specify the number of digits returned by the percentage value, if omitting the default value of 3 digits after the comma (0, xxx).

Attention:

- If the array value is empty, the function returns the #NUM! Error value.

- If significance

- Where the value of x does not match the comparison values ​​in the array function PERCENTRANK perform interpolation to return the appropriate value.

Example 1:

Find the rank of the value 25 by using PERCENTRANK with the following table:

PERCENTRANK function - The function returns the rank of the value in Excel Picture 1

In the cell to calculate enter the formula: = PERCENTRANK (D13: L13, D14) .

PERCENTRANK function - The function returns the rank of the value in Excel Picture 2

Pressing Enter results as:

PERCENTRANK function - The function returns the rank of the value in Excel Picture 3

Here the value 25 in the data array has 1 value less than 25 and 7 values ​​greater than 25 => percent rank of 25 equals 1 / (1 + 7) = 0.125.

Example 2:

The value to calculate the percentage rank is not in the data array.

For example, find the percentage rank of the value 15 in the data array:

PERCENTRANK function - The function returns the rank of the value in Excel Picture 4

In the cell to calculate enter the formula = PERCENTRANK (D13: L13, D14) press Enter the result is:

PERCENTRANK function - The function returns the rank of the value in Excel Picture 5

In this example, calculate the PERCENTRANK of two adjacent values ​​of 15, 14 and 18.

The value 15 not in the data array should be calculated based on its two nearest neighbors based on PERCENTRANK values ​​of 14 and 18:

- PERCENTRANK (14) = 0.5

- PERCENTRANK (18) = 0.625

=> PERCENTRANK (15) = 0.5 + (0.25 * (0.625-0.0.5)) = 0.531

So if the value to find the percentage rank is not in the array of functions, take the nearest neighbor value and the number of parts equal to the distance between the two neighbors.

The above article details the usage as well as special cases of PERCENTRANK function .

Good luck!