Every spreadsheet eventually needs a decision: pass or fail, approve or reject, bonus or no bonus. Here is how to write those formulas so you can still read them next month.
In this article
A single IF
=IF(C2>=40, "Pass", "Fail")Several outcomes: nested IF
Check the highest band first, then work down:
=IF(C2>=85,"A", IF(C2>=70,"B", IF(C2>=55,"C", IF(C2>=40,"D","F"))))💡 Press Alt+Enter inside the formula bar to put each IF on its own line. Excel ignores the line breaks, and the formula becomes much easier to read.
More than one condition: AND / OR
=IF(AND(B2="Sales", D2>=100000), "Bonus", "")
=IF(OR(E2="Delhi", E2="Mumbai"), "Metro", "Other")Hiding errors: IFERROR
Lookups and divisions throw #N/A or #DIV/0!. Wrap them:
=IFERROR(C2/B2, 0)⚠️ IFERROR hides every error, including real mistakes such as a typo in a range. Use it at the end, after you know the formula is correct.
When nesting gets out of hand
If you pass four or five levels, switch to a small lookup table and VLOOKUP approximate match. Tables are easier to change than formulas, and anyone can read them.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong