In this article, you will learn about the PERCENTRANK.EXC function, the formula syntax and usage of the function in Microsoft Excel.
PERCENTRANK.EXC Function in Excel
Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set. The PERCENTRANK.EXC function in Excel returns the relative rank of a value in a data set as a percentage between 0 and 1 exclusively which represents the number of values less than the given value
= PERCENTRANK.EXC (array, x,[significance])
- Array – The array or range of data with numeric values
- X – The value for which you want to know the rank.
- Significance – The number of significant digits for the returned percentage value. When omitted, PERCENTRANK uses three digits (0.xxx) in default. (Optional.).
Usage Notes and Possible Errors
- When the array is empty, the PERCENTRANK.EXC function returns the #NUM! error value.
- In case of significance being lesser than 1 then, the PERCENTRANK.EXC function returns the #NUM! error value.
- And when x does not match one of the values in array, PERCENTRANK.EXC interpolates to return the correct percentage rank.
How to use the PERCENTRANK.EXC function in Excel?
Using this function in a WS is simple; all you need to do is enter the function as a formula of the cell in the formula bar.
Take a look at the given example
Enter the data in the respective columns and enter the PERCENTRANK.EXC formula.
Formula: = =PERCENTRANK.EXC(A2:A11,3)
Here, A2 refers to the cell name or the cell address of the array and 3 is the X value (argument). You will get the result in the Result column.