A large part of analysis is labelling data: “High” or “Low” value orders, “Late” or “On time” deliveries, pass or fail. Excel’s conditional functions do this, and knowing which one to use keeps formulas short and readable.
IF: one condition, two results
=IF(C2>=50000, "High value", "Standard")
The pattern is IF(test, value if true, value if false). Text results need quotes; numbers do not.
AND and OR inside IF
=IF(AND(C2>=50000, D2="Bangalore"), "Priority", "Normal")
AND needs every condition to be true; OR needs at least one. They keep a formula to one IF instead of two.
Nested IF vs IFS
For several bands, older files nest IFs:
=IF(B2>=90,"A",IF(B2>=75,"B",IF(B2>=60,"C","D")))
IFS (Excel 2019 and Microsoft 365) is easier to read:
=IFS(B2>=90,"A", B2>=75,"B", B2>=60,"C", TRUE,"D")
Conditions are checked in order, so start with the highest band. The final TRUE acts as “everything else”.
When to use a lookup table instead
If you have more than four or five bands, or the thresholds change often, put them in a small table (minimum score and grade) and use XLOOKUP with approximate match: =XLOOKUP(B2, Bands[Min], Bands[Grade], , -1). Changing a threshold then means editing one cell, not every formula.
Common mistakes
- Numbers stored as text, so comparisons silently fail. Check with ISNUMBER.
- Overlapping or wrongly ordered conditions in IFS.
- Missing a final “else”, which produces #N/A in IFS.
- Hard-coding thresholds inside hundreds of formulas.
Related: Excel formulas every data analyst should know, XLOOKUP vs VLOOKUP and conditional formatting.
Excel, SQL and Power BI are the core of our 5-month Data Analytics course in Bangalore.
Book a free demo class at our BTM Layout centre, and ask a counsellor for batch details.
