AVERAGE & friends — Excel guide
The center of your data — and when the plain mean misleads: blanks vs zeros, outliers, and the conditional and robust variants.
AVERAGE
The arithmetic mean of the numbers in a range.
- Blanks and text are excluded from BOTH sum and count; zeros fully count. Whether missing data is 0 or blank changes the answer.
- All-text or all-blank range → #DIV/0! — there's nothing to divide by.
- Weighted averages need SUMPRODUCT(values, weights)/SUM(weights); AVERAGE weights everything equally.
AVERAGEIF
Average only the cells meeting a condition.
- AVERAGEIFS (multiple criteria) puts the average range FIRST, mirroring SUMIFS.
MEDIAN
The middle value — outlier-resistant where the mean isn't.
- Skewed data (salaries, home prices) usually deserves the median.
- Even count → mean of the two middle values. TRIMMEAN is the compromise: drop the tails, average the rest.