Does MEDIAN ignore empty cells?

Yes. MEDIAN treats empty cells as if they don't exist, counting only numeric values in the calculation.

What's the difference between MEDIAN and AVERAGE for budget analysis?

AVERAGE sums all values and divides by count, so one unusually high expense shifts it significantly. MEDIAN finds the middle value, ignoring outliers. For budget variance data, MEDIAN better represents your typical month.

How does MEDIAN handle an even number of values?

It returns the average of the two middle values. For example, MEDIAN(1, 2, 3, 4) returns 2.5 (the average of 2 and 3).

Can I use MEDIAN with negative numbers and mixed signs?

Yes. MEDIAN treats all numbers the same way—it sorts them (including negatives) and finds the middle value. MEDIAN(-100, 50, 25) returns 25.

MEDIAN function

MEDIAN returns the middle value in a sorted list of numbers, or the average of the two middle values if the count is even.

=MEDIAN(number1, ...)

Generate a MEDIAN formula

Describe what you need. The generator will reach for MEDIAN where MEDIAN is the right tool, and tell you when it is not.

Arguments

How MEDIAN reads its arguments

Argument	Required	Description
number1	Required	Required. A number, cell reference, or range to include in the median calculation.
...	Repeating	Optional. Additional numbers, ranges, or arrays to include. MEDIAN accepts up to 255 arguments and ignores empty cells and text.

Returns

A number: the middle value (or average of two middle values) of the sorted input numbers.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find the median actual spending for groceries

Category	Month	Actual
Groceries	Jan	425
Groceries	Feb	380
Groceries	Mar	410

=MEDIAN(425, 380, 410)

Result: 410

The three actual spending values for Groceries are 425, 380, and 410. Sorted as 380, 410, 425, the middle value is 410. This represents the typical spending amount, less influenced by the high January expense than an AVERAGE would be.

2. Find the median variance across all budget categories

Category	Month	Variance
Groceries	Jan	-25
Groceries	Feb	20
Utilities	Jan	-5
Utilities	Feb	2
Entertainment	Feb	-10

=MEDIAN(-25, 20, -10, -5, 2, -10, 5, -10, 0)

Result: -5

With nine variance values from across the budget, sorting gives -25, -10, -10, -10, -5, 0, 2, 5, 20. The 5th value (middle of 9) is -5, which represents the typical over/under-budget outcome across all categories.

3. Find the median budgeted amount to understand typical allocation levels

Category	Budgeted
Groceries	400
Utilities	150
Entertainment	100

=MEDIAN(400, 150, 100)

Result: 150

With three budgets (400, 150, 100), the sorted list is 100, 150, 400, and the middle value is 150. This shows the typical budget allocation size across your spending categories.

Common errors

Which MEDIAN error are you seeing?

Error	Why it happens	How to fix it
#REF!	You referenced a cell or range that no longer exists (deleted or moved), such as =MEDIAN(A1:A10) after deleting column A.	Verify all cell references are valid; restore the deleted range or update the formula to reference the correct cells.
#VALUE!	You included a text value that cannot be interpreted as a number, such as =MEDIAN(400, "TBD", 150).	Remove non-numeric text from the range, or use IFERROR to exclude error values: =IFERROR(MEDIAN(range), "").
#N/A	All values in the range are non-numeric or the range is empty when using MEDIAN with array conditions that match no data.	Ensure the range contains at least one numeric value; verify filtering conditions haven't excluded all data.

Tips and when to use something else

MEDIAN automatically ignores empty cells and text, making it robust for real-world data with occasional blanks.
When your data contains outliers, MEDIAN is more representative than AVERAGE—one unusually high expense won't skew it as much.
Use MEDIAN when you want the middle value itself; use LARGE or SMALL if you need a specific position (like the top 10% of values).
If you need the most frequent value instead, use MODE.SNGL or MODE.MULT—they answer a different question about your data's center.

Related functions

AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.MODE.SNGLReturns the most commonly appearing numeric value in a dataset, ideal for identifying the most frequent priority level, rating, or resolution time.LARGELARGE returns the k-th largest value in an array, where k=1 is the maximum, k=2 is the second-largest, and so on.SMALLSMALL returns the k-th smallest value from an array, where k=1 gives the minimum and higher k values give progressively larger results.PERCENTRANK.INCReturns the percentile rank of a value within a dataset as a decimal from 0 to 1, showing where it stands relative to all other values.

Frequently asked questions

Does MEDIAN ignore empty cells?: Yes. MEDIAN treats empty cells as if they don't exist, counting only numeric values in the calculation.
What's the difference between MEDIAN and AVERAGE for budget analysis?: AVERAGE sums all values and divides by count, so one unusually high expense shifts it significantly. MEDIAN finds the middle value, ignoring outliers. For budget variance data, MEDIAN better represents your typical month.
How does MEDIAN handle an even number of values?: It returns the average of the two middle values. For example, MEDIAN(1, 2, 3, 4) returns 2.5 (the average of 2 and 3).
Can I use MEDIAN with negative numbers and mixed signs?: Yes. MEDIAN treats all numbers the same way—it sorts them (including negatives) and finds the middle value. MEDIAN(-100, 50, 25) returns 25.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17