Course outline / Advanced Excel
Lesson 60 of 75 · Advanced · 6 exercises
Advanced formulas
Handle errors, several conditions and multi-way decisions.
What you'll practise
- Handle errors with IFERROR
- Average with a condition
- Highest with a condition
- Count with two conditions
- Test and add the same column
- Several outcomes with IFS
Handle errors with IFERROR
When a lookup finds no match it shows the #N/A error. IFERROR catches any error and shows something friendlier instead.
=IFERROR(formula, value if error)
In cell F2, look up "Zed" in the table B2:C5 with VLOOKUP (column 2, exact match), wrapped in IFERROR so it shows "None" instead of an error.
Average with a condition
AVERAGEIF averages only the rows that pass a test, just as SUMIF totals them.
=AVERAGEIF(test range, test, average range)
In cell F2, find the average Sales (C2:C5) where the Region (A2:A5) is "East".
Highest with a condition
MAXIFS finds the highest value among the rows that pass your tests. Like SUMIFS, the range to search comes first.
=MAXIFS(max range, range 1, test 1)
In cell F2, find the highest Sales in C2:C5 where the Region (A2:A5) is "West".
Count with two conditions
COUNTIFS counts rows that pass several tests at once, with each range followed by its test.
=COUNTIFS(range 1, test 1, range 2, test 2)
In cell F2, count the rows where the Region (A2:A5) is "West" and the Sales (C2:C5) are over 400.
Test and add the same column
A test range can be the same column you're adding up. That's how you total only the big values within a group.
In cell F2, use SUMIFS to total Sales (C2:C5) for the "East" Region (A2:A5), counting only sales over 300.
Several outcomes with IFS
IFS checks a list of tests in order and returns the result for the first one that's true. Using TRUE as the last test catches everything else.
=IFS(test 1, result 1, test 2, result 2, TRUE, otherwise)
In cell F2, show "Hit" if Ben's Sales (C3) are at least his Target (D3), "Close" if C3 is at least half of D3, and otherwise "Miss".
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.