Excel MEDIAN Function Examples and How It Works

To find the middle value in Excel, use the MEDIAN function: with your numbers in B2:B8, enter =MEDIAN(B2:B8). Excel sorts the numeric values conceptually and returns the middle one, or the average of the two middle values when there is an even number of numbers.

What the MEDIAN function does

MEDIAN summarizes the center of a set of numbers. It is often useful for measurements such as delivery times, home prices, test scores, or customer wait times, especially when a few unusually large or small values could distort an average. The median is not the most frequent value; that is the mode. Nor is it the same as the arithmetic mean, which adds values and divides by their count.

The function syntax is MEDIAN(number1, [number2], ...). The first argument is required, and later arguments are optional. You can supply numbers, cell references, or ranges; Excel accepts up to 255 arguments. In everyday use, a single range is the clearest choice. MEDIAN returns one value, not a reordered version of your data, so the worksheet cells remain in their original order.

For example, the median of 3, 8, and 20 is 8, even though 20 is much larger than the other two numbers. That makes the median a useful description of a typical result when the data is skewed. It does not explain how spread out the values are, however, so a median alone may not tell the whole story.

A basic MEDIAN formula example

Suppose B2:B8 contains 12, 18, 21, 25, 29, 34, and 40. With those seven values in B2:B8, enter =MEDIAN(B2:B8). The result is 25: the values on either side of it are 21 and 29, and 25 occupies the central position in the sorted list.

You do not need to sort the worksheet first. MEDIAN determines the central position from the values it receives, while leaving the visible rows untouched. This is helpful when the range is part of a larger table where sorting could separate related records or change the displayed order.

To calculate a median for a different set, replace B2:B8 with the relevant cell range. For example, if response times are in D2:D101, use that range rather than copying values into a separate sorted list. Check that the selected cells contain the intended records; an extra numeric value just outside the data can change the answer.

Examples with odd and even numbers of values

When a list has an odd number of numeric values, one value sits exactly in the center after sorting. In the seven-number example above, that value is 25. With an even number of values, there is no single central item, so Excel averages the two central numbers.

For instance, assume C2:C7 contains 12, 18, 21, 29, 34, and 40. With those six numbers in C2:C7, enter =MEDIAN(C2:C7). The two middle values are 21 and 29, so the result is 25, calculated as (21 + 29) / 2. The answer does not have to be one of the original entries.

This averaging rule matters when explaining a result to someone else. A median of 25 does not necessarily mean that any person, order, or measurement had a value of 25. With an even-sized group, it can be a midpoint between two actual observations.

Using multiple ranges and separate arguments

MEDIAN can combine values from more than one place. This is handy when the relevant numbers are in separate columns or blocks, but make sure every range belongs in the same calculation. For example, assume B2:B4 contains 5, 9, and 20, while D2:D4 contains 11, 15, and 30. With those values in the stated cells, enter =MEDIAN(B2:B4,D2:D4). The combined sorted values are 5, 9, 11, 15, 20, and 30, so the result is 13, the average of 11 and 15.

You can also provide individual numbers as arguments, though references are usually easier to maintain as the data changes. A range-based formula updates when a referenced cell value changes. If you add more records below the range, however, a fixed reference such as B2:B8 will not automatically include those new rows unless you adjust it or use an Excel Table column reference.

A useful check is to confirm that your ranges include the same intended population. Combining one month of values from one column with a full year from another may produce a mathematically valid median but not a meaningful comparison.

How blanks, text, zeros, and dates are treated

When MEDIAN reads a cell reference or range, it ignores empty cells and text entries. It also ignores logical values such as TRUE and FALSE stored in referenced cells. Numeric zero is different: zero is a number, so it is included and can affect the result. A cell that looks blank but contains a formula returning zero is not an empty cell; its zero is counted.

For example, suppose B2:B7 contains 10, an empty cell, the text n/a, 0, 20, and 30. With those entries in B2:B7, enter =MEDIAN(B2:B7). Excel uses the four numeric values 0, 10, 20, and 30, giving a result of 15. If the zero represents missing data rather than a genuine measurement, the formula will still count it; clean or handle the data according to what that zero means.

Dates and times are stored as numbers in Excel, so MEDIAN can calculate a middle date or time. Format the result cell as a date or time if Excel displays a serial number instead. Be careful not to mix dates with unrelated numeric measurements: Excel sees both as numbers, even if the combination does not make sense for your analysis.

Errors in the referenced data can cause the formula to return an error rather than a median. Inspect the source range if the result shows an error. Also note that a text value typed directly as a function argument is not treated the same way as text in a referenced cell; the safest approach is to keep observations in cells and pass their references to MEDIAN.

Finding a median for a group or condition

MEDIAN does not have a built-in criteria argument, and Excel has no MEDIANIFS function. In Microsoft 365 for Windows, you can combine MEDIAN with FILTER to calculate the median of rows that meet a condition. FILTER is available in Microsoft 365 and Excel 2021 or later; the example below uses the Microsoft 365 behavior.

Assume A2:A7 contains the regions North, South, North, South, North, North, and B2:B7 contains 40, 55, 60, 70, 80, 100 in the matching rows. With regions in A2:A7 and values in B2:B7, enter =MEDIAN(FILTER(B2:B7,A2:A7="North")). The North values are 40, 60, 80, and 100; the middle pair is 60 and 80, so the result is 70.

FILTER returns the matching values as an array, which MEDIAN then evaluates. If no row matches, FILTER normally returns a calculation error because there is nothing to return. To show a message instead, with the same layout but looking for West, enter =IFERROR(MEDIAN(FILTER(B2:B7,A2:A7="West")),"No matching rows"). This displays No matching rows when there are no West records. Use IFERROR thoughtfully: it can also hide other errors in the calculation, so check the source data if you expect matches.

Median versus average: choosing the right summary

The median and average answer different questions. The median identifies the center by position; the mean, commonly calculated with AVERAGE, reflects the total of all values divided by the number of values. A single extreme value can pull the mean substantially, while it often has less effect on the median.

Consider household incomes in a neighborhood: a few very high incomes may make the mean much higher than what most households earn, while the median can better describe the midpoint household. On the other hand, if every value contributes to a total that matters, such as total units produced per worker, the mean may be the more relevant summary. The right measure depends on the question, not on which formula is easier.

For a fuller picture, report more than one statistic when appropriate. A median paired with the minimum and maximum, or with quartiles, gives readers context about spread and outliers. Avoid describing the median as a typical value without considering the data: in a very uneven or multimodal distribution, one central number may conceal important groups.

Common problems and ways to check the result

Unexpectedly low median: check whether zeros or small placeholder values are included. MEDIAN counts numeric zeros in the range.
Unexpectedly high or low median: confirm the range boundaries and check for numeric values left over from earlier data.
Error result: inspect the range for errors, and if you used FILTER, verify that the condition matches at least one row.
A number displays as a date: change the result cell format using Home > Number Format, and choose an appropriate number format.
Formula is not updating for new records: expand the referenced range or convert the dataset to an Excel Table so its data range can grow more reliably.

When a result seems surprising, sort a copy of the numeric values or use a small sample to verify the center manually. Do not sort a live table casually if row relationships matter. For a large dataset, also verify how many numeric observations are included; blanks and text are skipped, so the count used by MEDIAN may be smaller than the number of occupied cells.

Frequently Asked Questions

Does Excel MEDIAN ignore blank cells? Yes. Blank cells in a referenced range are ignored, as are text and logical values in those referenced cells. Numeric zeros are included, so distinguish a true zero from a placeholder for missing information.

What happens when there is an even number of values? Excel averages the two central numeric values after ordering them. The returned median may therefore be a value that does not appear in the original data.

Can MEDIAN calculate a median that meets a condition? MEDIAN has no criteria argument of its own. In Microsoft 365 or Excel 2021 and later, combine it with FILTER, for example =MEDIAN(FILTER(B2:B7,A2:A7="North")), using the matching value and criteria ranges.

Why does MEDIAN return zero? Zero may genuinely be the center of the numeric values, or the range may contain zeros from formulas or placeholders. Check the source cells and decide whether each zero is a valid observation before changing the calculation.

Is MEDIAN available in Excel for Mac and Excel for the web? Yes. MEDIAN is a standard Excel worksheet function and works in Microsoft 365 for Windows, Excel for Mac, and Excel for the web. The basic formula is the same across those platforms.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.