Excel Median Formula Not Working? How to Fix It

Errors & fixes · Updated November 19, 2026

If Excel’s median formula is not working, check that the range contains actual numbers and that the formula points to the right cells. In Microsoft 365 for Windows, the basic formula is =MEDIAN(A2:A10); it returns the middle value, but text, errors, blanks, and filtered rows can make the result differ from what you expect.

What MEDIAN calculates

The median is the middle value after numbers are arranged from smallest to largest. With an odd number of values, it is the one value in the center. With an even number, Excel averages the two middle values. Unlike the mean, or average, the median is not strongly affected by an unusually high or low value.

For example, with numbers 4, 7, 9, 12, and 20 in A2:A6, the middle value is 9. With 4, 7, 9, and 12 in A2:A5, the middle pair is 7 and 9, so the median is 8. The formula in either case is straightforward:

With the five example values in A2:A6, enter =MEDIAN(A2:A6). The result is 9.

MEDIAN is available in current desktop Excel and Excel for the web. Its syntax is MEDIAN(number1, [number2], ...): you can supply a range, individual numbers, or multiple references. Usually a single range is easiest to check and maintain.

Check the formula and the selected range

A simple reference mistake is a common reason for a surprising answer. The formula might omit the newest row, include a total at the bottom, or point to a different column than the one containing the measurements. Click the formula bar and inspect each cell reference, then compare its boundaries with the data you intend to summarize.

  • Confirm the formula starts with an equals sign and uses the function name MEDIAN.
  • Check that the referenced cells contain the intended observations, not headings, subtotals, or unrelated values.
  • Look for a range that stops one row too early, such as A2:A20 when the data continues through A21.
  • When copying a formula, check whether a relative reference shifted. If the range should stay fixed when copied, use dollar signs, as in =MEDIAN($A$2:$A$100).

If the displayed cell shows the formula text instead of a result, the cell may be formatted as Text, or Excel may be in Show Formulas mode. Change the cell format to General, re-enter the formula (press F2, then Enter), and check Formulas > Show Formulas. On Windows, Ctrl+` toggles formula display; on Mac, the equivalent shortcut is Control+`. If the formula appears to calculate but the workbook is not updating, check Formulas > Calculation Options and select Automatic.

Numbers stored as text and other nonnumeric cells

One of the most frequent causes of a wrong median is numbers that look numeric but are stored as text. This can happen after importing a CSV, copying from a website, or receiving values with leading apostrophes or unexpected spaces. In a range reference, MEDIAN ignores text, so a column that looks full of values may contribute only some of them—or none.

Check a suspicious cell by selecting it and looking for a warning icon, a leading apostrophe in the formula bar, or left alignment that differs from the other numeric cells. Formatting a text value as Number does not necessarily convert it; the underlying cell value must be numeric. When Excel offers a warning menu, choose Convert to Number. You can also use Data > Text to Columns and complete the wizard to convert consistently formatted imported values.

For a single text-formatted number in A2, a conversion formula is =VALUE(A2). It returns the numeric value when the text can be interpreted as a number, though decimal and date interpretation can depend on regional settings. For a large dataset, convert the source column rather than building a complicated median formula around mixed data types.

Blank cells in a referenced range are ignored. A formula that returns an empty string, such as "", is text and is also ignored by MEDIAN when it appears in a referenced range. A genuine zero is different: zero is a numeric value and participates in the calculation. If zero means “missing” in your dataset, decide whether it should be excluded before calculating; do not remove legitimate zero measurements just to make the result look more plausible.

Understand errors and the #NUM! result

MEDIAN returns #NUM! when it has no numeric values to use. This commonly happens when the selected cells are empty, contain only text, or contain formulas that all return empty strings. Check whether the range includes at least one real number. If a formula elsewhere returns an error, such as #VALUE! or #N/A, that error can also make MEDIAN return an error rather than a number.

Find the first error in the input range and correct its source if possible. Wrapping the entire median calculation in IFERROR can hide a problem without fixing it, so use that only when a deliberate fallback is appropriate. For example, with observations in A2:A10, =IFERROR(MEDIAN(A2:A10),"Check the data") displays a message if the median calculation errors. It does not identify which input cell caused the problem, so inspect the range when the message appears.

Logical values such as TRUE and FALSE, and text representations of numbers, are generally ignored when they are in referenced cells. Values supplied directly as arguments behave differently: logical values and numeric text typed directly into the argument list can be evaluated. For reliable results, put observations in cells and reference a consistent numeric range instead of mixing values and text in the formula itself.

Dates, times, and unusual values

Excel stores valid dates and times as numbers, so MEDIAN can calculate a middle date or time. The result may look like an ordinary number if the result cell is formatted as General. Apply a date or time number format using Home > Number to display it appropriately. A date that merely looks like a date but is stored as text is ignored, just like other text.

To check a text date in A2, =DATEVALUE(A2) converts a recognizable date string to Excel’s date serial number. The interpretation depends on the date format and regional settings, so confirm that the converted date is the one intended before using it in a summary. For mixed date formats, clean and standardize the source column first.

Also consider whether the range contains outliers or values in the wrong unit. A median can be mathematically correct and still be an unexpected summary if a measurement was entered in centimeters among values in meters, or if a date column accidentally includes a numeric code. Sort or inspect the source values and verify units before changing the formula.

Filtered rows and conditional medians

A standard MEDIAN range includes numeric values in rows hidden by a filter. That is a frequent source of confusion: filtering a table to show one category does not automatically limit =MEDIAN(A2:A100) to visible records. If you want a median for matching records, use a condition-based calculation rather than relying on the visible filter alone.

In Microsoft 365, FILTER can return only the matching values for MEDIAN. With measurements in A2:A100 and region labels in B2:B100, this formula returns the median for rows labeled East:

=MEDIAN(FILTER(A2:A100,B2:B100="East"))

FILTER is available in Microsoft 365 and Excel 2021 or later. If no row matches, FILTER returns #CALC! unless you supply an if_empty value; supplying a text fallback does not create a meaningful median, so it is better to check that the condition has matching numeric data. In older Excel versions without FILTER, use a helper column to isolate qualifying values, then calculate the median from that column.

If you specifically need to ignore hidden rows in a reference, AGGREGATE supports a median operation. With values in A2:A100, =AGGREGATE(12,5,A2:A100) calculates the median while ignoring hidden rows. This is useful for a filtered list, but it does not ignore error values under option 5; an error in the visible data can still affect the result. Confirm that the hidden-row behavior matches your reporting needs.

Why the median differs from AVERAGE

MEDIAN and AVERAGE answer different questions. The average adds all numeric observations and divides by their count; the median identifies the center of the ordered observations. For the values 2, 3, 3, 4, and 100, the median is 3, while the average is 22.4. Neither result is inherently wrong: the median describes the central observation, while the average reflects the total and can be pulled by extreme values.

For values in A2:A6, =MEDIAN(A2:A6) returns 3, while =AVERAGE(A2:A6) returns 22.4. If your result looks different from a colleague’s, check whether one of you used a different statistic, included different records, or handled blanks and outliers differently. A median is not a way to exclude bad data automatically; it still depends on choosing the correct records and units.

A quick troubleshooting sequence

When the result is wrong, work from the source data outward rather than replacing the formula immediately. This sequence often isolates the cause:

  1. Click the result cell and verify the formula and its exact range.
  2. Check whether Excel is displaying formulas or treating the formula cell as Text.
  3. Inspect the source cells for text-formatted numbers, date strings, spaces, blanks, and errors.
  4. Confirm whether zero values should count and whether hidden or filtered rows belong in the calculation.
  5. Recalculate the expected median from a small sample sorted in ascending order.

For a sample with an even number of values, remember to average the two center values; choosing just the lower or higher center is not the median. For a large dataset, use a temporary copy of the range and sort it to verify the central item or pair. Sorting the original table can disrupt associated rows if it is not selected and sorted as a complete table, so use a copy or sort the entire dataset safely.

Frequently Asked Questions

Why does Excel MEDIAN return #NUM!?
It has no numeric values to calculate. Check that the referenced range contains at least one genuine number and is not made up entirely of text, blanks, or formulas returning empty strings.

Does MEDIAN ignore blank cells?
Yes. Blank cells in a referenced range are ignored. Text is generally ignored too, but a numeric zero is counted, so distinguish missing data from a real zero.

Does MEDIAN include filtered-out rows?
A normal MEDIAN reference includes numeric values in filtered-out rows. To calculate a conditional median in Microsoft 365, use FILTER with MEDIAN; to ignore hidden rows in a reference, consider AGGREGATE with function number 12 and option 5.

Why is my median showing as a date or a decimal?
The underlying calculation may be correct while the result cell uses an unexpected number format. Set an appropriate format under Home > Number. A median of an even number of dates or measurements can legitimately fall between two recorded values.

Is there a MEDIANIF or MEDIANIFS function?
Excel does not provide built-in MEDIANIF or MEDIANIFS functions. In Microsoft 365 or Excel 2021 and later, combine MEDIAN with FILTER for a condition-based median; in earlier versions, use a helper column to select the qualifying values.

Related

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.