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
  1. The structure of IF
  2. IF with calculations inside
  3. Combine conditions with AND, OR and NOT
  4. More than two outcomes: nested IF and IFS
  5. Worked example: grading a class in Columbus, Ohio
  6. Common IF mistakes (and the fix)
  7. IFERROR: hide errors, but wisely
  8. Helper functions worth knowing
  9. Summary
What you'll learn
  • 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.
📥 Practice this lesson

Calculate the average, pass/fail status and letter grade for 15 students. When you're done, =COUNTIF(G2:G16,"Yes") should return 9.

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.

3parts make up IF: the test, the value if true and the value if false
127conditions fit in one IFS, though past 4 or 5 a lookup table is better
64is the limit for nested IFs; after the third one they're hard to read
  1. The IF structure
  2. Calculations inside IF
  3. AND, OR and NOT
  4. IFS
  5. 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

OperatorMeaningExample
=Equal toC2="Texas"
<>Not equal toC2<>"Texas"
> / <Greater / less thanD2>1000
>= / <=Greater than or equal / less than or equalD2<=0

1. What does =IF(B2>=70,"Pass","Fail") return if B2 is exactly 70?

  • Pass
  • Fail
  • An error

>= 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:

AND(cond1, cond2, …)All at once
Returns TRUE if
Every condition is met.
Example
AND(B2>=70, C2>=80): minimum score and attendance.
Strict
OR(cond1, cond2, …)Any one is enough
Returns TRUE if
At least one is met.
Example
OR(B2="Rewards", E2>50): loyalty member or a large order.
Flexible
NOT(cond)Flips it
Returns TRUE if
The condition is false.
Example
NOT(ISBLANK(A2)): the cell has something in it.
Reverse

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?

  • =IF(OR(B2>10000,C2>=1),"Bonus","")
  • =AND(IF(B2>10000,C2>=1))
  • =IF(AND(B2>10000,C2>=1),"Bonus","")

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.

Order matters

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.

More than 4 or 5 tiers?

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?

  • From least strict to strictest (≥70 first)
  • From strictest to least strict (≥90 first)
  • The order doesn't matter

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):

StudentExam 1Exam 2ProjectAttend. %AveragePassed?Grade
Emma95961009096.85YesA
Mia8886809084.90YesB
Chloe7070708570.00YesC
Logan8266747074.00NoC
Kayla7066569564.40NoD
Grace4664429051.10NoF

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:

Letter grades for 15 students A: 2 students, B: 4, C: 5, D: 2 and F: 2. Nine of the 15 students pass. 9/15 pass
  • 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.
Passing: 15 students − 4 with a D or F − 2 below the attendance minimum = 9.

4. Noah has an average of 83.50 and attendance of 75. What does =IF(AND(F2>=70, E2>=80), "Yes", "No") return?

  • "Yes," because his average is above 70
  • An error, because attendance doesn't have a % sign
  • "No," because he doesn't meet the attendance condition

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.

Build long formulas in pieces

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.

You don't always need IF

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

  • IF evaluates a condition and returns one of two values.
  • AND requires every condition; OR, at least one.
  • IFS replaces nested IFs; order tiers from strictest to least strict and end with TRUE.
  • IFERROR handles 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.

🎓 Save your progress and get a free certificate

Create your account in 30 seconds, mark your lessons and download your certificate at the end.

Enroll for free