Syntax
PERCENTRANK.EXC(array, x, [significance])
| Parameter | Description |
|---|---|
| array | Parameter of the PERCENTRANK.EXC function. |
| x | Parameter of the PERCENTRANK.EXC function. |
| [significance] | (Optional.) Parameter of the PERCENTRANK.EXC function. |
Examples
Exclusive percentile rank
Formula
=PERCENTRANK.EXC(B2:B50, B10)
Returns the percentile rank of B10's value using exclusive interpolation. The minimum value returns 1/(n+1) instead of 0.
Statistical ranking
Formula
=PERCENTRANK.EXC(C2:C1000, 250)
Returns where 250 falls in the distribution. With 999 values, the range is 0.001 to 0.999 instead of 0 to 1.
Custom precision
Formula
=PERCENTRANK.EXC(A1:A100, 55, 4)
Returns the exclusive percentile rank with 4 significant digits.
Common Errors
#N/A
The value x falls outside the data range, or the array is empty.
#NUM!
The data set has fewer values than required for the exclusive method to produce a valid rank.
Tips
Never returns 0 or 1
Unlike PERCENTRANK.INC, the exclusive version never assigns a rank of exactly 0 or 1. The minimum rank is 1/(n+1) and the maximum is n/(n+1).
Preferred for probability plotting
Use PERCENTRANK.EXC when creating normal probability plots or QQ plots, as the exclusive method avoids infinite z-scores at the endpoints.
Requires enough data
With very small data sets (fewer than 3-4 values), PERCENTRANK.EXC may return errors or unreliable results. Use .INC for small samples.
Try PERCENTRANK.EXC in Viztab
Import your data and use PERCENTRANK.EXC with 370+ other formulas. No signup required.
Open Viztab