These functions summarise a list of numbers quickly.
The basics
=MAX(B2:B100) returns the highest value.=MIN(B2:B100) returns the lowest value.=MEDIAN(B2:B100) returns the middle value, which is less affected by extremes than the average.=MODE(B2:B100) returns the most frequent value.
Average vs median
If a few very large salaries are in the list, the average rises but the median stays close to a typical value. For salary and price data, the median is often the better summary.
MAXIFS and MINIFS: with conditions
=MAXIFS(max_range, criteria_range, criteria)
Highest sales in the North region:
=MAXIFS(C2:C100, A2:A100, "North")
MINIFS works the same way for the lowest value. They are available in Excel 2019 and Microsoft 365.
Find who has the highest value
=INDEX(A2:A100, MATCH(MAX(B2:B100), B2:B100, 0)) returns the name next to the highest number.
Ignored values
These functions ignore text and empty cells in a range.
Worked example
Marks in B2:B6 are 40, 55, 70, 90, 95. =MAX(B2:B6) is 95, =MIN(B2:B6) is 40, =MEDIAN(B2:B6) is 70 (the middle value) and =AVERAGE(B2:B6) is 70.
Common mistakes
- Using AVERAGE when a few extreme values distort it. MEDIAN is steadier.
- Numbers stored as text are ignored.
- Expecting MAX to ignore a condition. Use MAXIFS for that.
Frequently asked questions
When should I use MEDIAN instead of AVERAGE?
When the data has outliers, such as salaries or house prices.
What does MEDIAN do with an even count?
It averages the two middle values.
How do I find the max for one category?
Use MAXIFS.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: MAX, MIN, MEDIAN, MAXIFS.
Start Excel practice → Case studies
