Goal
You’ll be able to use IF, AND, and OR to build genuine conditional logic into a spreadsheet, and understand how to combine them for conditions more complex than IF alone can handle.
Learn
The IF function returns one value if a condition is true, and a different value if it’s false:
=IF(A1>=60, "Pass", "Fail")
This checks whether A1 is 60 or greater; if true, it displays “Pass”; if false, “Fail.” The three parts, separated by commas, are always: the condition, what to return if true, what to return if false.
AND and OR let you combine multiple conditions. AND requires every condition to be true; OR only requires at least one to be true:
=IF(AND(A1>=60, B1="Complete"), "Pass", "Fail") =IF(OR(A1>=90, B1="Honors"), "Distinction", "Standard")
The first example only returns “Pass” if the score is 60+ and the status is genuinely “Complete” — failing either condition returns “Fail.” The second returns “Distinction” if either the score is 90+ or the honors flag is set — only needing one of the two.
IF statements can also be nested — an IF inside another IF — to handle more than two possible outcomes:
=IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", "F")))
This checks each threshold in order, only reaching a lower check if the higher one wasn’t met — genuinely useful for grading scales or tiered categories, though nesting many levels deep can become hard to read, and is often better replaced with a lookup-table approach for very long lists of tiers.
Decision Task
You need a formula that only returns “Approved” if a loan applicant’s credit score is at least 700 AND their income is at least 50000. Before reading on: would you use AND or OR to combine these two conditions, and why?
Show Answer
AND — since the requirement is that both conditions must genuinely be true simultaneously (credit score high enough, and income high enough), not just one or the other. OR would incorrectly approve someone meeting only one condition, which doesn’t match the actual “both required” business rule described.
Common Mistake
Confusing AND and OR, especially under the pressure of writing a formula quickly — using OR when a rule genuinely requires every condition to be true, or AND when only one of several conditions should be sufficient. Always translate the actual business rule into plain language first (“both must be true” vs. “at least one must be true”) before choosing which function to use.
Practice Questions
1. Write a formula that returns “Eligible” if A1 (age) is 18 or older, and “Not Eligible” otherwise.
Show Answer
=IF(A1>=18, "Eligible", "Not Eligible")
2. Write a formula that returns “Qualified” only if BOTH B1 is “Yes” AND C1 is at least 3.
Show Answer
=IF(AND(B1="Yes", C1>=3), "Qualified", "Not Qualified")
3. Write a formula that returns “Priority” if EITHER D1 is “Urgent” OR E1 is greater than 100.
Show Answer
=IF(OR(D1="Urgent", E1>100), "Priority", "Standard")
4. Why might a long chain of nested IF statements (5+ levels deep) become a genuine maintenance problem?
Show Answer
It becomes genuinely hard to read and verify correctness at a glance, and modifying one threshold in the middle of a deeply nested chain risks accidentally breaking the logic for surrounding thresholds.
5. In =IF(A1>=60, “Pass”, “Fail”), what are the three separate parts of the formula, in order?
Show Answer
The condition to check (A1>=60), what to return if true (“Pass”), and what to return if false (“Fail”).
Try It Yourself
Without looking back, write a nested IF formula that grades a score in A1: “A” for 90+, “B” for 80-89, and “C” for anything below 80.
Show Answer
=IF(A1>=90, "A", IF(A1>=80, "B", "C")) — checking the highest threshold first, only falling through to lower checks if the higher one wasn’t met.
Quick Check
1. What are the three parts of an IF function, in order?
Show Answer
The condition, the value if true, the value if false.
2. What does AND require to return TRUE?
Show Answer
Every combined condition must be true.
3. What does OR require to return TRUE?
Show Answer
At least one of the combined conditions must be true.
4. What is a “nested” IF?
Show Answer
An IF function placed inside another IF function, to handle more than two possible outcomes.
5. Why might a very long chain of nested IFs be worth replacing with a different approach?
Show Answer
It becomes hard to read and error-prone to maintain as the number of nested levels grows.