Percentiles are statistics which can help define where particular values sit in relation to the rest of the data set.
The formulas below show some examples based on the 20 values in column A:
- Cell C2 returns the value for P10 (the tenth percentile).
- Cell C3 returns the 25th percentile, which is also the first quartile.
- Cell C4 returns P50, which is also the median value.
- Cell C5 returns the 75th percentile, which is the third quartile. Note that you can use the % format in the formula as well as using decimal values. You can deduct the first quartile from the third quartile to calculate the interquartile range.
- Cell C6 demonstrates the PERCENTRANK.INC function. In this case it is showing that 16% of values are below the value in cell A14.