Median by Group

Excel Formulas › Statistics

Excel 365Array alt

There’s no MEDIANIF function, so the median for one group — the typical West-region order, median salary by department — takes MEDIAN + FILTER (Excel 365) or a classic MEDIAN(IF()) array formula.

Quick formula: for the median of column B where group (A) equals E2 (Excel 365):
=MEDIAN(FILTER(B2:B100, A2:A100=E2))
FILTER keeps one group’s values; MEDIAN finds their middle value.

Functions used (tap for the full reference guide):

MEDIANFILTERIF

The example

Order amounts by region. The median West order.

AB
1RegionAmount
2West$120
3East$90
4West$180
5West$150
6West median:$150

The formula

The median West order (middle of 120, 150, 180):

=MEDIAN(FILTER(B2:B5, A2:A5="West")) // {120,150,180} → 150

How it works

Filter to the group, then take the median:

  1. FILTER(B2:B5, A2:A5="West") returns just West’s amounts: {120, 180, 150}.
  2. MEDIAN(…) sorts them and returns the middle value — 150 (with an even count, it averages the two middles).
  3. The median resists outliers far better than the average — one huge order won’t skew it.
  4. In older Excel, use the array form =MEDIAN(IF(A2:A5="West", B2:B5)) with Ctrl+Shift+Enter.

Build a per-group table: spill a UNIQUE list of regions, then put the MEDIAN(FILTER()) beside each — an instant median-by-group summary.

Try it: interactive demo

Live demo

Pick a region; see its median order.

Median:

Variations

Legacy array formula

Any version — Ctrl+Shift+Enter:

=MEDIAN(IF(A2:A100=E2, B2:B100))

Median with two conditions

Filter on more than one field:

=MEDIAN(FILTER(B2:B100, (A2:A100=E2)*(C2:C100="Widget")))

Average by group instead

When you want the mean:

=AVERAGEIF(A2:A100, E2, B2:B100)

Pitfalls & errors

#CALC! if no rows match. FILTER returns nothing for an unknown group. Add an if_empty: =MEDIAN(FILTER(B:B, A:A=E2, "")) — but note MEDIAN of "" errors, so guard with IFERROR.

Median vs average. Median is the middle value (robust to outliers); average is the mean. They answer different questions — choose deliberately.

FILTER needs Excel 365/2021. Older versions need the MEDIAN(IF()) array formula, entered with Ctrl+Shift+Enter.

Practice workbook

📊
Download the free Median by Group practice workbook
Orders by region with the median-by-group result, the legacy MEDIAN(IF()) array and two-condition variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate the median for a group in Excel?
There's no MEDIANIF, so use =MEDIAN(FILTER(values, group=g)) in Excel 365, or the array formula =MEDIAN(IF(group=g, values)) entered with Ctrl+Shift+Enter in older versions.
Why use median instead of average by group?
The median is the middle value and resists outliers, so a few extreme records won't skew it the way they skew the average. Use median for typical-value questions.
How do I find the median with multiple conditions?
Multiply the conditions inside FILTER: =MEDIAN(FILTER(values, (group=g)*(type=t))).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Average by group · Percentile & quartile · Standard deviation

Function references: MEDIAN · FILTER · IF