2.1Logical functions: IF, AND, OR, IFS
The essentials in 30 seconds
- IF decides, test first: =IF(test, value_if_true, value_if_false) returns one or the other value — the test must produce TRUE or FALSE.
- AND/OR combine, IF concludes: you slip AND(...) or OR(...) into the IF's test — never one IF per condition.
- 3 cases or more = IFS: the first true test wins, TRUE last = otherwise — from the most restrictive to the broadest.
Running mini-case — Wassim and the invoices to validate
Wassim, an accountant in Algiers, checks dozens of supplier invoices every month: amount in B, supporting document received or not in C. Before, he reread everything by hand and annotated with a pen. He writes his first IF: =IF(B2>=100000,"To review","OK") to flag large amounts, then combines with AND to require document received AND amount verified before validating. When his boss adds four discount tiers, he replaces his nested IFs with a readable IFS. This decision grid of Wassim returns in 2.2 (find the rate by code), 2.3 (clean the names before comparing), 2.4 (color the rows to review) and 2.5 (filter the structured table).
Level 2 thread: Wassim's grid returns in the course, the diagram, the script and the 48h challenge — and continues in 2.2, 2.3, 2.4 and 2.5.
1. IF: return according to a test
=IF(test, value_if_true, value_if_false) evaluates a condition and returns one or the other value: =IF(B2>=100000, "To review", "OK"). The test uses comparison operators: =, <> (different), >, >=, <, <=. The returned text is written in quotes, numbers without. Wassim flags the 100,000 DA and above invoices with a single IF copied down.
2. AND / OR: combining conditions
AND(...) is true if all the conditions are; OR(...) if at least one is. You slip them into an IF's test: =IF(AND(B2>=100000, C2="received"), "Validated", "Pending"). This avoids stacking one IF per condition. Wassim validates an invoice only if the amount is verified AND the document received; a single missing supporting document (OR) is enough to block it.
3. IFS: no more Russian dolls
For several cases, you used to nest IFs in IFs — unreadable and fragile. IFS tests conditions in cascade: =IFS(B2>=500000,"Tier 4", B2>=200000,"Tier 3", B2>=100000,"Tier 2", TRUE,"Tier 1"). The first true test wins; TRUE last plays the role of «else». Mandatory order: from the most restrictive to the broadest, otherwise a broad threshold catches everything. Wassim classifies his supplier discounts into four tiers with a single readable formula.
🔎 Concrete example — 🔎 Concrete example — Wassim's discount tiers
Invoice amount in B2; Wassim assigns the discount tier:
=IFS(B2>=500000,"Tier 4", B2>=200000,"Tier 3", B2>=100000,"Tier 2", TRUE,"Tier 1")
compared to the nested one to ban:
=IF(B2>=500000,"Tier 4",IF(B2>=200000,"Tier 3",IF(B2>=100000,"Tier 2","Tier 1")))Reading: IFS rolls out the thresholds from top to bottom; the first true wins, TRUE catches all the other cases.
Lesson: for 3 cases or more, IFS stays readable where nested IFs become fragile.
Section vocabulary
- IF
- Returns value_if_true if the test is TRUE, value_if_false otherwise: Excel's basic decision.
Simple example: Wassim writes =IF(B2>=100000,"To review","OK").
What it is not: a function that adds or counts.
Reflex: a test = a TRUE/FALSE comparison.
- AND / OR
- AND is true if all the conditions are true; OR if at least one is; they slip into an IF's test.
Simple example: Wassim validates with AND(amount verified, document received).
What it is not: IFs stacked one inside the other.
Reflex: combine in a single IF.
- IFS
- Tests condition/value pairs in cascade; the first true test returns its value.
Simple example: Wassim classifies four discount tiers in one formula.
What it is not: five unreadable nested IFs.
Reflex: from the most restrictive to the broadest.
- «Else» clause
- TRUE as the last condition of IFS: catches all the remaining cases, the default case.
Simple example: Wassim ends with TRUE,"Tier 1" for small amounts.
What it is not: one more numbered condition.
Reflex: always finish with TRUE.
In practice — In practice — Make your formulas decide
- Write =IF(B2>=100000,"To review","OK") on a column of amounts.
- Add a condition combined with AND (amount AND document received).
- Replace a cascade of nested IFs with IFS.
- End IFS with TRUE,"…" for the default case.
Ready-to-say script — explaining the grid to your boss (30 s)
« Each invoice is tested automatically: above one hundred thousand dinars it goes to review, and it is validated only if the amount is verified and the document received. The four discount tiers are handled by a single IFS, from the highest threshold to the lowest, with a default case. A readable formula, copied over the whole month. »
Say it while showing Wassim's decision column, from the simple IF to the cascade.
Copy-paste template — decision + cascade
=IF(AND(B2>=100000,C2="received"),"Validated","Pending")
=IFS(B2>=500000,"Tier 4",B2>=200000,"Tier 3",B2>=100000,"Tier 2",TRUE,"Tier 1")Single threshold
A single cutoff (budget exceeded or not): Wassim keeps a simple IF, no cascade for a single test.
Double condition
Amount AND supporting document: Wassim combines with AND in a single IF instead of stacking two IFs.
Tiered scale
Four discount tiers: Wassim switches to IFS ordered from the highest threshold to the lowest.
Forgotten default case
Small amounts display an error: Wassim adds TRUE,"Tier 1" in the last position.
✓ Best practices
- A test = a comparison that gives TRUE or FALSE.
- Combine via AND/OR rather than stacking IFs.
- IFS for 3 cases and more, from the most restrictive to the broadest.
✗ Common pitfalls
- Nesting 5 IFs → unreadable and fragile to maintain.
- Forgetting the quotes around the returned text.
- Putting the IFS thresholds out of order (the broad one catches everything).
Key takeaways
- IF(test, if_true, if_false) decides according to a condition.
- AND = all true; OR = at least one true.
- IFS replaces nested IFs; the first true wins.
- TRUE last = default case; restrictive order first.
48h challenge — apply tomorrow
On a real table of Wassim (or yours): a threshold IF on a column, a double-condition decision with AND, then a 3-case IFS cascade ended by TRUE.
- I can write a threshold IF that returns two values according to TRUE/FALSE.
- I can combine conditions with AND/OR in a single IF.
- I replace nested IFs with ordered IFS, TRUE last.
Frequently asked questions
IFS or nested IFs?
IFS for a cascade of ordered cases (tiers, scales). Nested IF remains useful for tree-like logic (if… then if…). Beyond 3 levels of IF, prefer IFS or a lookup table (XLOOKUP, see 2.2).
Does the order of conditions matter in IFS?
Yes, absolutely: the first true test wins. Go from the most restrictive to the broadest (>=500000 before >=200000…), otherwise a broad threshold catches everything before the fine cases. Finish with TRUE for the default case.
Which function avoids nesting several IFs for a cascade of cases?