The following article introduces you to PERCENTRANK.EXC - one of the functions in the group of statistical functions that is very popular in Excel.
Description: The function returns the rank of a value in a dataset as a percentage excluding values 0 and 1. The function supports Excel 2010 and later versions.
Syntax: PERCENTRANK.EXC (array, x, [significance])
Inside:
- array: An array or range of data to determine percentile, which is a required parameter .
- x: The value you want to determine its rank in the dataset, is a required parameter.
- significance: The value determines the number of digits of the percentage value returned, which is an optional parameter, if omitted the default is 3 digits.
Attention:
- If array array value is empty -> the function returns the #NUM! Error value .
- If significance value <1 -> the function returns the #NUM! Error value .
- If x does not match any value of the array -> interpolation function to determine the rank of the value.
For example:
Find the rank of the values described in the data table below:
- Calculate the rank of the value 12 in the array. In the cell to calculate enter the formula : = PERCENTRANK.EXC (E6: K6, E7)
- Press Enter -> rank of value 12 in the dataset is:
- Calculate the rank of the value 4.25. In the cell to calculate, enter the formula: = PERCENTRANK.EXC (E6: K6, E8)
- Press Enter -> return value is:
Because the value 4.25 is a non-array value -> the function returns the # N / A error value
Above are instructions and some specific examples when using PERCENTRANK.EXC function in Excel.
Good luck!