AVERAGEIF and MEDIAN Practice
AVERAGEIF averages only the cells in a range that meet a condition — the average equivalent of SUMIF. MEDIAN finds the middle value of a dataset once sorted, rather than the arithmetic mean. The two solve different problems: AVERAGEIF filters which numbers get averaged; MEDIAN changes how the "typical" value is calculated in the first place.
Worked example
=AVERAGEIF(A2:A20, "East", B2:B20)Averages column B only for rows where column A equals "East"
MEDIAN takes no condition — wrap it with IF as an array formula, or filter the range first, to get a conditional median.
Why MEDIAN sometimes tells a truer story than AVERAGE
AVERAGE is pulled hard by outliers — one $2 million house in a dataset of $300,000 homes drags the average up far past what's "typical". MEDIAN ignores the size of outliers entirely and just finds the middle value, which is why real estate and salary data are usually reported as medians.
AVERAGEIFS for more than one condition
Like SUMIF/SUMIFS, AVERAGEIF only takes one condition. For "East region AND this quarter", use AVERAGEIFS with multiple range/criteria pairs.
Practice this for real
Try the "AVERAGEIF & MEDIAN" exercises
Type the formula into a real spreadsheet and get instant feedback.