AVERAGE vs MEDIAN: which 'typical value' to trust

Both answer 'what's the typical value?', but differently: AVERAGE (the mean) adds everything up and divides, so a few extreme values drag it around; MEDIAN is the middle value once sorted, so it barely moves when an outlier appears. On skewed data — salaries, house prices, response times — they can tell very different stories.

The differences at a glance

	AVERAGE	MEDIAN
How it's computed	Sum ÷ count	Middle value when sorted (average of the two middle values if the count is even)
Sensitivity to outliers	High — one huge value shifts it a lot	Low — extreme values barely affect it
Best for	Roughly symmetric data with no wild extremes	Skewed data (income, prices, durations)
Empty cells & text	Ignored (use AVERAGEA to count text as 0)	Ignored
Zeros	Counted as values	Counted as values
Compatibility	Universal	Universal — identical in every Excel, Sheets & LibreOffice version we test

Which should you use?

AVERAGE — Data is fairly even and you need a figure that reflects the total (e.g. average order value where the sum matters). Pair with a conditional form via AVERAGEIF/AVERAGEIFS.
MEDIAN — A few big or small values would distort the mean — salaries, home prices, load times. The median reports the true 'middle person'. There's no MEDIANIF; use an array MEDIAN(IF(...)) for a conditional median.

Compatibility (from executed tests)

Both execute identically across every version of all three apps we test. Two things to know: neither has a built-in *IF variant except AVERAGEIF/AVERAGEIFS (there is no MEDIANIF — use MEDIAN(IF(range=criteria,values)) array-entered), and when the mean and median diverge sharply that gap is itself the signal your data is skewed — report both.

Example formulas

AVERAGE — the mean	`=AVERAGE(B2:B100)`
MEDIAN — the middle value	`=MEDIAN(B2:B100)`
Conditional median (array-entered)	`=MEDIAN(IF(A2:A100="East",B2:B100))`

Full per-version details on each function page: AVERAGE · MEDIAN.