Lesson 5 of 16 · Level 2: Intermediate
Excel IF Function: Nested IF, IFS, AND, OR and IFERROR
- ⏱ 11 min read
- ❓ 4 review questions
- ✍️ Exercise with solution
- 📥 Downloadable template
In this lesson
- Make decisions in your sheets with the IF function.
- Combine conditions with AND, OR and NOT.
- Handle several outcomes with IFS instead of nested IFs.
- Control errors with IFERROR without hiding real problems.
- Grade an entire class with three formulas.
Calculate the average, pass/fail status and letter grade for 15 students. When you're done, =COUNTIF(G2:G16,"Yes") should return 9.
- practice-excel.xlsx · Grades sheet
So far your formulas have always done the same calculation. With the IF function you teach Excel to make decisions: pass or fail, apply a discount or not, flag an order as late. It's one of the most-used functions in Excel and the gateway to truly automatic spreadsheets.
- The IF structure
- Calculations inside IF
- AND, OR and NOT
- IFS
- IFERROR
The structure of IF
=IF(logical_test, value_if_true, value_if_false)
- Logical test: a comparison that can only be TRUE or FALSE, such as
B2>=70. - Value if true: what it returns when the test is met.
- Value if false: what it returns when it isn't.
Example: if the score in column B is 70 or higher, the student passes.
=IF(B2>=70, "Pass", "Fail")
Text goes inside double quotes; numbers and cell references don't.
Comparison operators
| Operator | Meaning | Example |
|---|---|---|
= | Equal to | C2="Texas" |
<> | Not equal to | C2<>"Texas" |
> / < | Greater / less than | D2>1000 |
>= / <= | Greater than or equal / less than or equal | D2<=0 |
1. What does =IF(B2>=70,"Pass","Fail") return if B2 is exactly 70?
>= means "greater than or equal to," so 70 meets the condition.
IF with calculations inside
The results can be formulas too. An online store gives 10% off orders of $500 or more:
=IF(D2>=500, D2*0.9, D2)
Better still, keep the inputs in cells (lesson 3). With the minimum in H1 and the discount in H2:
=IF(D2>=$H$1, D2*(1-$H$2), D2)
To leave a cell looking blank when there's nothing to calculate yet, use two double quotes: =IF(E2="","",E2*1.07). That way rows without data don't show zeros or errors.
Combine conditions with AND, OR and NOT
When a decision depends on more than one condition:
- Returns TRUE if
- Every condition is met.
- Example
AND(B2>=70, C2>=80): minimum score and attendance.
- Returns TRUE if
- At least one is met.
- Example
OR(B2="Rewards", E2>50): loyalty member or a large order.
- Returns TRUE if
- The condition is false.
- Example
NOT(ISBLANK(A2)): the cell has something in it.
Example: a sales rep earns a bonus with more than $10,000 in sales and fewer than 3 returns:
=IF(AND(C2>10000, D2<3), "Bonus", "No bonus")
Free shipping for rewards members or orders over $50; otherwise $7.99:
=IF(OR(B2="Rewards", E2>50), 0, 7.99)
2. A bonus is paid if sales (B2) exceed $10,000 and tenure (C2) is at least 1 year. Which formula is correct?
AND requires every condition to be met; OR would settle for just one.
More than two outcomes: nested IF and IFS
What if there are more than two possible results? Letter grades are the classic American case: 90 or more is an A, 80 or more a B, 70 or more a C, 60 or more a D, and anything lower an F.
The classic way: IF inside IF
=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))
It works in every version, but it's hard to read and easy to break with one parenthesis too many or too few.
The modern way: IFS
Available in Excel 2019, 2021, 2024, Microsoft 365 and Google Sheets:
=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", B2>=60,"D", TRUE,"F")
Read it in pairs: condition, result; condition, result… The final pair TRUE,"F" works as "in every other case." Without it, a score below 60 would return #N/A.
Excel returns the result of the first condition that's true. If you put B2>=60 first, a 95 would land there and come out as a D. Always order conditions from the strictest to the least strict.
With many tiers (commission schedules, shipping rates by weight), don't keep nesting. Build a small tier table and use XLOOKUP or VLOOKUP with an approximate match. You'll learn that next lesson; it's much easier to maintain.
3. With IFS you assign "Excellent" to 90 or more, "Good" to 70 or more and "Fair" to the rest. In what order do you write the conditions?
IFS returns the result of the first true condition. With ≥70 first, a 95 would come out as "Good."
Worked example: grading a class in Columbus, Ohio
The Grades sheet in the practice file holds 15 students from a high school course in Columbus, with two exams (B and C), a project (D) and attendance (E). The teacher's syllabus says:
- Average: each exam counts 35% and the project 30%.
- A student passes with an average of 70 or higher and attendance of at least 80%.
- Letter grade: A (90+), B (80+), C (70+), D (60+), F (below 60).
F2: =B2*35% + C2*35% + D2*30%
G2: =IF(AND(F2>=70, E2>=80), "Yes", "No")
H2: =IFS(F2>=90,"A", F2>=80,"B", F2>=70,"C", F2>=60,"D", TRUE,"F")
Copied down to row 16, here are some of the results (check them in your file):
| Student | Exam 1 | Exam 2 | Project | Attend. % | Average | Passed? | Grade |
|---|---|---|---|---|---|---|---|
| Emma | 95 | 96 | 100 | 90 | 96.85 | Yes | A |
| Mia | 88 | 86 | 80 | 90 | 84.90 | Yes | B |
| Chloe | 70 | 70 | 70 | 85 | 70.00 | Yes | C |
| Logan | 82 | 66 | 74 | 70 | 74.00 | No | C |
| Kayla | 70 | 66 | 56 | 95 | 64.40 | No | D |
| Grace | 46 | 64 | 42 | 90 | 51.10 | No | F |
Look at Chloe: an average of exactly 70.00 is the boundary case, and >= correctly passes her. Now look at Logan: his average earns a C, but he doesn't pass because his attendance is below 80%. The two columns follow different rules, so they don't contradict each other. Here's the whole class:
- A (90 or more) 2Emma and Ben.
- B (80 to under 90) 4One of them, Noah, doesn't pass: his attendance is 75%.
- C (70 to under 80) 5The largest group. Logan doesn't pass because of attendance.
- D (60 to under 70) 2Kayla and Olivia: below the 70 needed to pass.
- F (below 60) 2Grace and Isabella.
4. Noah has an average of 83.50 and attendance of 75. What does =IF(AND(F2>=70, E2>=80), "Yes", "No") return?
AND needs both conditions to be true. 83.50 ≥ 70 is TRUE, but 75 ≥ 80 is FALSE, so AND returns FALSE and IF shows "No."
Common IF mistakes (and the fix)
Typing text without quotes: =IF(B2>=70,Pass,Fail).
Text goes in double quotes. Without them, Excel looks for a name and returns #NAME?.
Putting a number in quotes: =IF(B2>="70",…).
Write B2>=70. With quotes, Excel compares against text and the result is silently wrong.
Using > when the rule says "70 or higher."
"Or higher" means >=. Always test the boundary: a score of exactly 70.
Ordering IFS tiers from lowest to highest.
Start with the strictest (≥90) and finish with TRUE.
Before writing a complex IF, put just the condition in a helper cell (=AND(F2>=70, E2>=80)) and confirm it returns TRUE or FALSE where you expect. Once it works, wrap it in the IF and delete the helper.
IFERROR: hide errors, but wisely
IFERROR(formula, value_if_error) returns the formula's result or, if it produces an error, whatever you specify:
=IFERROR(D2/C2, 0)
=IFERROR(VLOOKUP(A2, Customers, 2, FALSE), "Not found")
Use it for errors you expect (an empty divisor, a code that doesn't exist yet), not to cover up broken formulas: if everything returns 0, you'll never know something failed.
A comparison on its own already returns TRUE or FALSE: =F2>=70 is enough for a check column. And to count how many students passed, you don't need another IF column: =COUNTIF(G2:G16,"Yes") returns 9 on the Grades sheet. You'll master COUNTIF two lessons from now.
Helper functions worth knowing
ISBLANK(A2): TRUE if the cell is empty.ISNUMBER(A2): TRUE if it's a number (great for catching numbers stored as text).TODAY(): today's date. Combined with IF:=IF(D2<TODAY(),"Overdue","Current").
Practice exercise: order tracking
You have a list with these columns: Order (A), Amount (B), Promised date (C), Delivered (D, "Yes" or "No"). Create column E, Status, that shows:
- "Delivered" if D is "Yes."
- "Late" if it hasn't been delivered and the promised date has passed.
- "On time" in every other case.
Then, in column F, calculate a 5% customer credit only for late orders over $1,000.
See solution
E2: =IFS(D2="Yes","Delivered", C2<TODAY(),"Late", TRUE,"On time")
F2: =IF(AND(E2="Late", B2>1000), B2*5%, 0)
Without IFS: =IF(D2="Yes","Delivered",IF(C2<TODAY(),"Late","On time")). Test it with an order of exactly $1,000: it should get no credit, because the rule says "over."
Summary
IFevaluates a condition and returns one of two values.ANDrequires every condition;OR, at least one.IFSreplaces nested IFs; order tiers from strictest to least strict and end withTRUE.IFERRORhandles expected errors; don't use it to hide broken formulas.- Always test boundary cases (a score of exactly 70) to catch a
>that should be>=.
Next lesson: VLOOKUP and XLOOKUP: How to Look Up Data Between Tables. You'll learn VLOOKUP step by step, XLOOKUP, INDEX and MATCH, tiered lookups and how to fix #N/A errors.
Create your account in 30 seconds, mark your lessons and download your certificate at the end.