Theory
Not two outcomes, but five
Meera wants a loyalty discount that depends on how much a customer has spent: over 10000 gets 15%, over 5000 gets 10%, over 2000 gets 5%, otherwise nothing.
A single IF from BCA105 handles two outcomes. This needs five tiers.
The answer is to nest IFs inside each other, building a decision ladder, exactly the if-else-if ladder you met in BCA104 C programming, now in a cell. And just like there, the order you test the tiers in decides whether it works. Logic returns for the third subject; this time it goes deep.
Theory
A cascade of sieves
Imagine sieves stacked from coarsest to finest. A pile of stones passes through the first sieve (biggest holes) and the largest stones are caught; whatever falls through meets the next sieve, and so on. A nested IF is that cascade: test the highest threshold first, catch those who qualify, and let the rest fall to the next test. Order the sieves wrong (finest first) and everything gets caught at the top, mislabelled.
Theory
Nested IF: the decision ladder
You put an IF inside another IF's 'otherwise' slot:
=IF(spend>10000, "15%", IF(spend>5000, "10%", IF(spend>2000, "5%", "0%")))
Read it as a ladder: 'over 10000? -> 15%. Otherwise, over 5000? -> 10%. Otherwise, over 2000? -> 5%. Otherwise 0%.' It is evaluated top to bottom, first true wins, then it stops (BCA104's ladder behaviour).
AND / OR / NOT combine conditions inside any IF: IF(AND(spend>5000, member="Yes"), ...). Modern Excel also offers IFS() to avoid deep nesting for many tiers.
Quiz
A customer spent 8000. What does =IF(spend>10000,"15%",IF(spend>5000,"10%",IF(spend>2000,"5%","0%"))) return?
- 10%, the ladder stops at the first true test (over 5000)
- 15%, because 8000 is a lot
- 5% and 10% both
- 0%, no tier matches
Show the answer
10%, the ladder stops at the first true test (over 5000)
The ladder tests top to bottom: 8000 > 10000? No. 8000 > 5000? Yes -> returns '10%' and stops, the lower tests never run. First true wins, exactly like BCA104's if-else-if ladder. If you answered 5%, you let it fall through past the winning tier, which is the mistake that happens when the ladder order is wrong.
Think first
Why order matters
Aryan accidentally writes the ladder from the LOWEST threshold first: =IF(spend>2000,"5%",IF(spend>5000,...)). A customer spends 8000. What goes wrong?
Show the answer
They get 5% instead of the correct 10%. Because 8000 > 2000 is the first test and it is true, the ladder stops there and never checks the higher tiers, everyone above 2000 collapses to 5%. A nested-IF ladder must test highest threshold first so the biggest spenders are caught before falling to lower tiers. Wrong order silently gives wrong answers with no error, the nastiest kind of bug.
Watch out
Where marks leak
Wrong ladder order (test highest threshold first, or everyone drops to a low tier). Confusing AND (all true) with OR (any true), the BCA102 logic. Forgetting quotes around text results ("15%"). Mismatched parentheses in deep nesting (count the closing brackets). And not knowing IFS() as the cleaner alternative for many tiers. Trace a nested IF one test at a time, exactly like reading a C if-else-if ladder.
Theory
The same logic, three subjects deep
IF-AND-OR is now in its third home: BCA102 truth tables, BCA104 if-else ladders, and Excel, and nested IF is literally the ladder from C in a spreadsheet cell. Recognising this transfer means you already understand it; you are just learning new syntax. Next lesson tackles a real annoyance: formulas that show ugly errors like #DIV/0!, and how IFERROR wraps them in a clean message.
Summary
Key takeaways
- IF(condition, value_if_true, value_if_false) chooses between two outcomes.
- Nested IF puts an IF in the 'otherwise' slot to build a multi-tier decision ladder.
- The ladder evaluates top to bottom, first true wins, then stops, so test the HIGHEST threshold first.
- AND (all true), OR (any true), NOT (invert) combine conditions inside IF.
- IFS() is a cleaner alternative to deep nesting; this is BCA102/BCA104 logic reused.
- Memory hook: a cascade of sieves, coarsest (highest threshold) first.