PERCENTILE returns a cutoff at a chosen fraction of a numeric dataset. It interpolates between observations, so the answer need not be an actual value in the data.
PERCENTILE function syntax
=PERCENTILE(data, percentile)- data: numeric range or array.
- percentile: fraction from zero to one, such as 0.9 for P90.
Set up the example data
Enter this small dataset starting in A1. The first row contains headers. Keep the formula output separate from the input cells.
| Value |
|---|
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
Find the median with P50
Enter this formula in A10. The middle observation is thirty. P50 agrees with MEDIAN for the same dataset.
=PERCENTILE(A2:A6,0.5)Result: 30.
Calculate an interpolated P90
For five sorted observations, the inclusive position is 1+(5-1)*0.9, or 4.6. Interpolating sixty percent between forty and fifty gives forty-six.
=PERCENTILE(A2:A6,0.9)Result: 46.
Find the first quartile
The 25th percentile is Q1. QUARTILE with an argument of one is the corresponding quartile calculation.
=PERCENTILE(A2:A6,0.25)Result: 20.
Pool a multi-column array
All four numbers contribute to one distribution. This does not calculate a separate percentile for each column.
=PERCENTILE({10,20;30,40},0.5)Result: 25.
Calculate the interquartile range
Subtract Q1 from Q3 to measure the spread of the middle portion of the distribution. Here that is forty minus twenty.
=PERCENTILE(A2:A6,0.75)-PERCENTILE(A2:A6,0.25)Result: 20.
Use a fraction rather than a whole percent
Use 0.9 or 90%, not the number ninety. PERCENTILE.EXC also interpolates; it is not a way to force an observed data value.
=PERCENTILE(A2:A6,90)Result: #NUM!.
Other Google Sheets articles you may also like