Using the Excel PERCENTRANK.EXC Function
SummaryThe Excel PERCENTRANK.EXC function returns the rank of a value contained in a data set as a percentage exclusive of the data set. This function is useful when evaluating the relative standing of a value within a data set. For example, PERCENTRANK.EXC could be used to evaluate the standing of students who have taken a standardized test.
The PERCENTRANK.EXC function, along with the PERCENTRANK.INC function, are provided by Microsoft as a replacement for the PERCENTRANK function and may provide improved accuracy.
Syntax=PERCENTRANK.EXC (array, x, [significance])
Required. An array or range of data with numeric values that defines the relative standing.
Required. The specific value for which you want to find the rank.
Optional. The number of significant digits for the returned percentage value. If omitted, the function uses three digits (0.xxx).
Usage NotesPERCENTRANK.EXC returns the relative standing of a value within a data set, shown as a percentage. For example, if you had a score better than 90% of all scores it would be in the 90th percentile (see the PERCENTILE function). PERCENTRANK.EXC would provide the score with a rank of 0.90.
If the value specified in the X argument is not found in array, the function interpolates to return the correct percentage rank.
Inclusive vs. Exclusive
In Excel 2010, Microsoft replaced the PERCENRANK function with PERCENTRANK.EXC and PERCENTRANK.INC. The "EXC" indicates exclusive and "INC" indicates inclusive. Both formulas use the same arguments.
PERCENTRANK.EXC should be used when you must determine the rank exclusive of the first and last values in the array.
PERCENTRANK.INC. should be used when you must determine the rank inclusive of the first and last values in the array.
The following screen shot shows the difference between the two when using a small data set. Users should note, however, that as the size of the input array increases, the difference between the EXC and INC functions will decrease.
The PERCENTRANK.EXC function returns the #NUM! error in the following instances:
- If array is empty;
- If significance < 1