Unit 4 of 12 Free
Logic with IF
Make your sheet decide things.
Functions in this unit: IF, AND, OR, IFS
Key ideas
IF asks a yes or no question
=IF(B2>=70,"Pass","Fail")Test first, then the result when true, then the result when false.
Text needs quotes
"Pass"Words inside a formula go in double quotes. Numbers and cell references do not.
Lesson 1: IF basics
What is the correct order of IF arguments?
- Test, value if true, value if false
- Value if true, test, value if false
- Test, value if false, value if true
- Value if false, value if true, test
Show answer
Test, value if true, value if false. IF(test, yes, no).
A score of 70 or more passes. In C2, show "Pass" or "Fail", and make it work filled down to C7.
A B C 1 Student Score Result 2 Maya 92 3 Leo 67 4 Priya 85 5 Sam 58 6 Nora 70 7 Eli 96 Hint:
=IF(B2>=70,"Pass","Fail")Show answer
=IF(B2>=70,"Pass","Fail")in C2. Note Nora scored exactly 70. >= includes 70, > would not.Build: show "Over" when B2 is above 100, otherwise "OK"
Show answer
=IF(B2>100,"Over","OK"). Text results need double quotes.What will B1 show?
A B 1 5 B1 contains
=IF(A1>5,"High","Low")Show answer
Low. 5 is not greater than 5, so the false branch runs.
Which operator means "not equal to"?
- <>
- !=
- =/=
- ><
Show answer
<>. Excel uses <> for not equal.
Lesson 2: AND and OR
What will A1 show?
A 1 A1 contains
=AND(TRUE,FALSE)Show answer
FALSE. AND is only TRUE when every test is TRUE.
A bonus needs a Score of 90+ AND 40+ Hours. Show "Bonus" or "No" in E2, filling down to E7.
A B C D E 1 Name Dept Score Hours Bonus 2 Ava Sales 94 42 3 Ben Support 81 38 4 Cara Sales 88 45 5 Dev Ops 97 36 6 Eva Support 91 41 7 Finn Ops 73 44 Hint: Put AND(...) where the test goes inside IF.
What does OR(A, B) return?
- TRUE if at least one test is TRUE
- TRUE only if both are TRUE
- TRUE if both are FALSE
- The larger of A and B
Show answer
TRUE if at least one test is TRUE. OR needs just one TRUE.
Flag a product "Review" if Region is "West" OR Units are over 200. Otherwise show "OK". Start in E2, fill to E7.
A B C D E 1 Product Region Units Price Flag 2 Notebook East 120 3.5 3 Pen West 340 1.25 4 Stapler East 45 12 5 Binder North 80 6.75 6 Marker West 210 2.4 7 Folder North 150 1.9 Hint:
=IF(OR(B2="West",C2>200),"Review","OK")What will A1 show?
A 1 A1 contains
=OR(3>5,2=2)Show answer
TRUE. 3>5 is FALSE but 2=2 is TRUE, so OR is TRUE.
Lesson 3: Nested decisions
Give a letter grade in C2: 90+ is "A", 80+ is "B", anything else "C". Fill to C7.
A B C 1 Student Score Grade 2 Maya 92 3 Leo 67 4 Priya 85 5 Sam 58 6 Nora 80 7 Eli 96 Hint: Put a second IF in the false slot of the first:
=IF(B2>=90,"A",IF(B2>=80,"B","C"))What problem does IFS solve?
- It avoids deeply nested IFs by listing test/result pairs
- It sums with conditions
- It handles errors
- It looks up values
Show answer
It avoids deeply nested IFs by listing test/result pairs. IFS(test1, result1, test2, result2, ...) reads top to bottom.
What will B1 show?
A B 1 75 B1 contains
=IF(A1>=90,"A",IF(A1>=70,"B","C"))Show answer
B. 75 fails the first test, passes the second.
Show "Over" if Actual is greater than Budget, otherwise "Under". Start in D2 and fill to D7.
A B C D 1 Category Budget Actual Status 2 Groceries 600 684.35 3 Rent 1500 1500 4 Transport 250 198.4 5 Dining 200 262.9 6 Utilities 180 171.25 7 Fun 150 95 Hint: Compare Actual (C) to Budget (B).
Show answer
=IF(C2>B2,"Over","Under")in D2. Groceries and Dining are over budget. Rent is exactly on budget, so it shows Under.Build the same grade logic with IFS
Show answer
=IFS(B2>=90,"A",B2>=80,"B",TRUE,"C"). TRUE as the last test acts as "everything else".