The MEDIAN function finds the middle number in a list of values

Excel's MEDIAN function returns the middle value when your numbers are arranged from smallest to largest. If you have an odd number of values, MEDIAN picks the exact middle one. If you have an even number of values, it averages the two middle numbers together. This is different from AVERAGE, which adds all numbers and divides by how many there are.

The basic syntax is =MEDIAN(number1, number2, number3...) or =MEDIAN(range). You can type individual numbers separated by commas, or point to a range of cells like A1:A10. Excel handles both approaches the same way.

Key Takeaways

  • MEDIAN finds the middle value in a dataset, making it useful when you want to ignore very high or very low outliers.
  • Type =MEDIAN(A1:A10) to find the median of cells A1 through A10, or =MEDIAN(5,10,15,20,25) to use specific numbers.
  • With an odd count of numbers, MEDIAN returns the exact middle value; with an even count, it returns the average of the two middle values.
  • MEDIAN ignores empty cells and text, but will return an error if you include cells with text mixed in your range.

Using MEDIAN with a range of cells

The most common way to use MEDIAN is to point to a range. Click the cell where you want the result, type =MEDIAN(, then click the first cell in your data and drag to the last cell. Excel will show the range in the formula bar as you select. When you release the mouse, type the closing parenthesis and press Enter.

For example, if your sales numbers are in cells B2 through B15, you would type =MEDIAN(B2:B15) and press Enter. Excel calculates the middle value instantly. If you later change one of those numbers, the median updates automatically.

Typing numbers directly into the formula

You can also type numbers straight into the MEDIAN formula without using cells. Type =MEDIAN(100,250,175,300,225) and press Enter. Excel will return 225, because when arranged in order (100, 175, 225, 250, 300), 225 is the middle value.

This approach works for small lists, but becomes tedious with many numbers. For larger datasets, pointing to a range is faster and easier to edit later.

How MEDIAN handles odd and even counts

With five values like 10, 20, 30, 40, 50, the median is 30 — the exact middle. With six values like 10, 20, 30, 40, 50, 60, the median is 35, because Excel averages the two middle numbers (30 and 40). This matters when you are working with datasets where the count changes or where you need to know whether the result is an actual value in your list or a calculated average.

You can check this yourself: type =MEDIAN(1,2,3,4,5) in one cell (result: 3) and =MEDIAN(1,2,3,4,5,6) in another (result: 3.5). The difference shows how Excel handles even-numbered lists.

MEDIAN versus AVERAGE and MODE

MEDIAN, AVERAGE, and MODE each tell you something different about your data. AVERAGE adds all values and divides by the count — useful for typical values but skewed by very high or very low numbers. MEDIAN finds the middle point — useful when outliers exist. MODE finds the most frequently occurring value — useful when you want the most common result.

If you have sales of $100, $150, $200, $250, and $5,000, the AVERAGE is $1,140, the MEDIAN is $200, and there is no MODE. The median of $200 better represents a typical sale than the average inflated by the $5,000 outlier.

Combining MEDIAN with other functions

You can nest MEDIAN inside other formulas. For example, =IF(MEDIAN(A1:A10)>100, "High", "Low") returns "High" if the median is greater than 100, otherwise "Low". Or use =MEDIAN(IF(B1:B10>0, B1:B10)) to find the median of only positive numbers (entered as an array formula with Ctrl+Shift+Enter on Windows or Cmd+Shift+Enter on Mac).

These combinations let you filter or condition your median calculation based on other criteria in your spreadsheet.

Troubleshooting MEDIAN errors

If MEDIAN returns #VALUE!, you likely have text or special characters in your range. MEDIAN ignores truly empty cells, but it stops if it encounters text. Check each cell in your range — even a single letter or space can cause the error. Delete any non-numeric content and try again.

If MEDIAN returns an unexpected number, verify that your range is correct. Click the cell with the formula and look at the formula bar to confirm the range shown matches what you intended. Also check whether any cells contain formulas that return errors — those will break MEDIAN too.

Frequently Asked Questions

Does MEDIAN work with negative numbers?

Yes. MEDIAN treats negative numbers the same as positive ones. For example, =MEDIAN(-10, 0, 10, 20, 30) returns 10. The function arranges all numbers from smallest to largest, regardless of sign, and finds the middle value.

Can I use MEDIAN on multiple non-adjacent ranges?

Yes, but you must list each range separately. Type =MEDIAN(A1:A5, C1:C5, E1:E5) to find the median across three separate ranges. Excel treats this as one combined dataset and returns the middle value of all cells together.

What happens if I use MEDIAN on a range with blank cells?

MEDIAN ignores blank cells completely. If you have 10 cells but 2 are empty, MEDIAN calculates the middle value of the remaining 8 numbers. This is different from AVERAGE, which also ignores blanks but counts them differently in some contexts.

Is MEDIAN case-sensitive or affected by cell formatting?

No. MEDIAN only looks at the numeric value stored in each cell, not how it is formatted or displayed. A cell formatted as currency, percentage, or date still works in MEDIAN as long as it contains a number underneath.

Can MEDIAN work with dates?

Yes, because Excel stores dates as numbers. =MEDIAN(A1:A10) on a range of dates returns the middle date. The result appears as a number until you format the cell as a date, then it displays as a date again.