MEDIAN Formula Generator

How MEDIAN works

Availability: Every version of Excel, Excel for the web, Google Sheets and LibreOffice Calc. The MEDIAN with FILTER pattern below needs Excel 2021 or Microsoft 365; the MEDIAN with IF version works everywhere but needs array entry in Excel 2019 and earlier.

MEDIAN sorts your numbers internally and returns the one in the middle: half the values sit at or below it, half at or above. With an even count there is no single middle value, so it averages the two nearest the centre - which is why a median can be a number that appears nowhere in the data, and why a median of whole numbers can come out as 16.5.

The reason to prefer it over AVERAGE is skew. One founder's salary, one enterprise contract or one 40-minute support call moves a mean a long way and a median barely at all. Salaries, house prices, response times, order values and anything else with a long tail are almost always reported as medians for exactly that reason. The mean is still the right summary when the values are roughly symmetrical, and it is the only one of the two that can be combined arithmetically - you can add means together, or weight them; medians of subgroups tell you nothing about the median of the whole.

Inside a range reference, MEDIAN ignores text, TRUE/FALSE and genuinely empty cells, and counts zeros as real numbers. That blank-versus-zero distinction is the usual reason a median jumps after somebody fills the gaps in with zeros. Values typed directly as arguments are treated differently: =MEDIAN(TRUE, 3, 5) counts TRUE as 1 and returns 3, whereas the same three entries in cells would give 4.

Excel has no MEDIANIF. A conditional median is built by feeding IF into MEDIAN, which passes an array of matching values and FALSE placeholders that MEDIAN then skips. In Microsoft 365 and Excel 2021 you can type it normally, or use MEDIAN(FILTER(...)), which reads better; in Excel 2019 and earlier the IF version must be committed with Ctrl+Shift+Enter or it returns the wrong single value.

Syntax

More worked examples

Common mistakes

Numbers stored as text are dropped from the count

A column pasted from a PDF or a web table can arrive as text, and MEDIAN ignores every text entry inside a range without erroring. Removing half the values moves the middle. Compare =COUNT(range) with =COUNTA(range); if they differ, convert the column before trusting the median.

A numeric header inside the range

Whole-column references are safe when the header is text, because MEDIAN skips it, but a header such as 2026 or a row of month numbers is a genuine number and joins the data set. Start the range at the first data row rather than relying on the header being ignored.

The conditional median entered as a normal formula

In Excel 2019 and earlier, =MEDIAN(IF(A2:A50="West", B2:B50)) typed with Enter returns the value from the row the formula happens to sit beside, or #VALUE!. Press Ctrl+Shift+Enter so the braces appear, or move to a version with dynamic arrays and use FILTER.

Medians that cannot be recombined

A median of four regional medians is not the median of the whole company, and no weighting fixes it - the information needed is not in the four numbers. Always compute the median from the underlying rows, which is why conditional medians usually have to be recalculated rather than aggregated.

A middle value that is not a valid value

On a one-to-five rating scale with an even number of responses, the median can come out as 3.5, which is not a rating anyone can give. That is arithmetically correct but often misleading in a report; quote the count alongside it, or use MODE.SNGL when the summary has to be an actual scale point.