How to Use MEDIAN, PERCENTILE, and QUARTILE in Excel
Introduction
You check the average delivery time and it comes back at 5 days, but most orders arrive in 2-4. One late shipment is pulling the number up, and the average doesn't flag it. MEDIAN, PERCENTILE, and QUARTILE give you a clearer picture of where your data actually falls.
The Dataset
Here's a delivery log for a small fulfillment center:
| A | B | |
|---|---|---|
| 3 | Order | Days to Deliver |
| 4 | 1001 | 3 |
| 5 | 1002 | 5 |
| 6 | 1003 | 2 |
| 7 | 1004 | 17 |
| 8 | 1005 | 3 |
| 9 | 1006 | 4 |
| 10 | 1007 | 2 |
| 11 | 1008 | 3 |
| 12 | 1009 | 6 |
The average delivery time is 5 days:
=AVERAGE(B4:B12)
That looks high for a dataset where eight of nine orders arrived in under a week.
MEDIAN
MEDIAN returns the middle value when you sort the data from smallest to largest. The syntax is:
=MEDIAN(number1, [number2, ...])
- number1: A range or set of values.
To find the median delivery time:
=MEDIAN(B4:B12)
The result is 3. To see why, line up the values in order:
2, 2, 3, 3, 3, 4, 5, 6, 17
With nine values, the middle one is the fifth: 3 days. The 17-day outlier sits at the far end of the list, but it has no more influence on the median than the 6 next to it.
| Measure | Result | What It Tells You |
|---|---|---|
| AVERAGE | 5 | Arithmetic mean, pulled up by the outlier |
| MEDIAN | 3 | Middle value, unaffected by extremes |
If the average says 5 and the median says 3, something at one end of the data is pulling the average off center. The median gives you a better read on the typical value.
When the count is even, MEDIAN averages the two middle values. If you removed Order 1009, the sorted list would be 2, 2, 3, 3, 3, 4, 5, 17, and the median would be (3 + 3) / 2 = 3.
Key takeaway: MEDIAN finds the center of your data without being pulled by extreme values. When the average and median differ significantly, your data has outliers, and the median is the more reliable measure.
PERCENTILE
MEDIAN returns the 50th percentile, the center of the data. PERCENTILE.INC lets you pick any percentile, not just the middle. The syntax is:
=PERCENTILE.INC(array, k)
- array: The range of values.
- k: A decimal between 0 and 1 representing the percentile (0.75 for the 75th, 0.9 for the 90th).
To find the delivery time that 75% of orders come in under:
=PERCENTILE.INC(B4:B12, 0.75)
The result is 5. Three-quarters of deliveries arrive in 5 days or fewer, a better number to put in an SLA than the average.
Other percentiles fill in the picture:
| Percentile | Formula | Result |
|---|---|---|
| 25th | =PERCENTILE.INC(B4:B12, 0.25) | 3 |
| 50th | =PERCENTILE.INC(B4:B12, 0.5) | 3 |
| 75th | =PERCENTILE.INC(B4:B12, 0.75) | 5 |
| 90th | =PERCENTILE.INC(B4:B12, 0.9) | 8.2 |
The 50th percentile matches the median. The jump from 5 at the 75th to 8.2 at the 90th reflects the outlier's pull near the top of the distribution. When a percentile falls between two data points, PERCENTILE.INC interpolates. That's where the 8.2 comes from.
Excel also offers PERCENTILE.EXC, which uses a slightly different interpolation method. PERCENTILE.INC is the standard for most work.
QUARTILE
The 25th, 50th, and 75th percentiles show up so often that Excel wraps them in a dedicated function. QUARTILE.INC returns the same values with a simpler syntax:
=QUARTILE.INC(array, quart)
- array: The range of values.
- quart: An integer from 0 to 4.
| quart | Returns | Result |
|---|---|---|
| 0 | Minimum | 2 |
| 1 | 25th percentile (Q1) | 3 |
| 2 | Median (Q2) | 3 |
| 3 | 75th percentile (Q3) | 5 |
| 4 | Maximum | 17 |
=QUARTILE.INC(B4:B12, 3) returns 5, matching =PERCENTILE.INC(B4:B12, 0.75). If you only need the standard quartile breakpoints, QUARTILE.INC saves you from thinking in decimals.
The gap between Q3 and Q1 is the interquartile range (IQR), which measures how spread out the middle 50% of your data is:
IQR = Q3 - Q1 = 5 - 3 = 2
An IQR of 2 means the middle half of deliveries spans just a 2-day window, while the maximum of 17 sits far outside it.
Conclusion
AVERAGE gives you one number for the whole dataset. MEDIAN, PERCENTILE, and QUARTILE show you where the data actually sits and how far it spreads.
For basic aggregation, see our guide on SUM, AVERAGE, and COUNT. For finding the nth highest or lowest value by position, check out our guide on LARGE, SMALL, and RANK.
Level up your Excel skills
Bite-sized lessons, drills, and daily challenges to build real spreadsheet skills.