Statistical
Calculate quintiles (20%, 40%, 60%, 80%) and assign 1–5 quintile buckets
Compute percentile cutoffs and assign values to five threshold-based bands.
How quintile cutoffs divide a distribution into five 20% bands
While quartiles split data at 25%, 50%, and 75%, quintiles divide a dataset into five 20% slices using four cutpoints: Q_1st (20th percentile, k = 0.2), Q_2nd (40th, k = 0.4), Q_3rd (60th, k = 0.6), and Q_4th (80th, k = 0.8).
Excel lacks a dedicated QUINTILE function—use PERCENTILE.INC(Range, QuintileNum / 5) to compute any quintile threshold (or DecileNum / 10 for deciles).
| Quintile Band | Percentile Cut | Cutoff on [10,20,30,50,70,90] |
|---|---|---|
| Quintile 1 (Bottom 20%) | <= PERCENTILE.INC(valuesRange, 0.2) | <= 20 |
| Quintile 2 (20%–40%) | <= PERCENTILE.INC(valuesRange, 0.4) | >20 and <=30 |
| Quintile 3 (40%–60%) | <= PERCENTILE.INC(valuesRange, 0.6) | >30 and <=50 |
| Quintile 4 (60%–80%) | <= PERCENTILE.INC(valuesRange, 0.8) | >50 and <=70 |
| Quintile 5 (Top 20%) | > PERCENTILE.INC(valuesRange, 0.8) | > 70 (e.g. 90) |
Quintile threshold and 1–5 bucket assignment formulas
Bind the four cutoffs once inside LET so each row is classified into Quintile 1..5 cleanly.
=LET(valuesRange, $A$2:$A$7, IFS(A6<=PERCENTILE.INC(valuesRange,0.2), 1, A6<=PERCENTILE.INC(valuesRange,0.4), 2, A6<=PERCENTILE.INC(valuesRange,0.6), 3, A6<=PERCENTILE.INC(valuesRange,0.8), 4, TRUE, 5))Extract any k-th quintile cutoff directly
Pass 0.2, 0.4, 0.6, or 0.8 as the second argument of PERCENTILE.INC.
=PERCENTILE.INC(A2:A7, 0.8)Inputs and interpretation
Finite samples and ties need not yield equal-sized groups. Values equal to a cutoff enter the lower band under this <= policy. Require a nonempty numeric reference range and numeric target; quantile interpolation conventions affect results.
Try the example
Use the original values or change them to test your understanding.