IF and IFS: Decision-Making in Formulas
IF is the formula that turns a spreadsheet from a calculator into a decision-maker. This lesson covers the anatomy of a basic IF statement, how to nest IFs for multiple outcomes, and when to switch to the cleaner, more modern IFS function instead. By the end you'll be able to build formulas that classify, flag, and route data automatically based on conditions you set.
Why this skill matters professionally
IF-based logic shows up in nearly every business spreadsheet: flagging overdue invoices, assigning pass/fail grades, applying discount tiers, routing approval levels, or labeling inventory as 'reorder' versus 'in stock.' It's also one of the most common live-test questions in Excel job interviews and skills assessments, because it proves you can translate a business rule ('if the balance is over 30 days, mark it late') directly into a working formula without asking a developer to build it for you.
Learning objectives
- Write clean IF statements that return numbers or text.
- Replace nested IFs with IFS.
Background concepts
The anatomy of IF
Every IF formula has exactly three parts separated by commas: the logical test, what to return if the test is true, and what to return if it's false.
=IF(logical_test, value_if_true, value_if_false)
Logical tests use comparison operators
IF's first argument must resolve to TRUE or FALSE. That's built using operators like =, >, <, >=, <=, and <> (not equal).
- B2>=60 — true if B2 is 60 or more
- C2="Yes" — true if C2's text exactly matches 'Yes'
- D2<>0 — true if D2 is not zero
Nesting has a breaking point
You can put another IF inside the false argument to test a second condition, and a third inside that. Two levels is usually fine to read; three or more becomes a maze of parentheses that's hard to debug and easy to break with one missing closing parenthesis.
IFS replaces long nested chains
IFS (available in Excel 2019+/365) takes pairs of condition, result and evaluates them top to bottom, stopping at the first TRUE. It reads far more like a decision table than nested IFs do.
Step-by-step
1. 1. Write your first IF
In an empty cell type =IF(B2>=60,"Pass","Fail") where B2 is a test score. If B2 is 75, it returns Pass; if 45, Fail.
2. 2. Return numbers instead of text
IF can return numbers or even other formulas: =IF(B2>1000, B2*0.9, B2) applies a 10% discount only to orders over 1000, leaving smaller orders unchanged.
3. 3. Combine with AND/OR for multiple conditions
=IF(AND(B2>=60,C2="Present"),"Pass","Fail") requires both conditions true. =IF(OR(B2<60,C2="Absent"),"Fail","Pass") fails on either condition alone.
4. 4. Nest a second IF for three outcomes
=IF(B2>=90,"A",IF(B2>=80,"B","C")) — if the score isn't 90+, it checks the next tier. Each nested IF replaces the 'false' slot of the one before it.
5. 5. Build a full grading scale with IFS
As soon as you need four or more tiers, switch to IFS for readability.
=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", B2>=60,"D", TRUE,"F")
6. 6. Always include a catch-all in IFS
IFS returns #N/A if none of the conditions are true. Ending with TRUE,"default value" guarantees every row gets a result, the same way the final 'else' works in nested IF.
7. 7. Test edge cases
Before trusting a grading or tiering formula, manually test boundary values — exactly 90, exactly 59.99 — to make sure >= versus > is doing what you intend.
8. 8. Wrap in IFERROR when needed
If your logical test itself could error (say, dividing to get a percentage), wrap the whole thing: =IFERROR(IF(B2/C2>0.5,"High","Low"),"N/A").
Real-world workplace examples
Invoice aging flag
=IF(TODAY()-DueDate>30,"OVERDUE","Current") flags any invoice more than 30 days past due for a collections team.
Commission tier
=IF(Sales>=50000,"Gold",IF(Sales>=25000,"Silver","Bronze")) sorts reps into commission tiers automatically each month.
Shipping cost rule
=IF(Weight>50,"Freight",IF(Weight>20,"Standard","Small Parcel")) routes orders to the correct shipping method based on weight brackets.
Attendance eligibility
=IFS(DaysAbsent=0,"Perfect",DaysAbsent<=2,"Good",DaysAbsent<=5,"Warning",TRUE,"Review") sorts employees into HR review categories.
Loan approval pre-screen
=IF(AND(CreditScore>=680,DTI<=0.36),"Pre-Approved","Refer to Underwriter") automates the first pass of a loan officer's intake sheet.
Practical scenarios
Grading spreadsheet with five tiers
A teacher initially writes four nested IFs for A/B/C/D/F, but keeps mistyping a parenthesis and getting formula errors. Switching to IFS with a clean list of condition-result pairs, ending in TRUE,"F", fixes the errors and makes the rule sheet self-explanatory to a substitute teacher covering the class.
Discount logic gone wrong
A retail analyst writes =IF(Qty>10,Price*0.9,Price*0.95) intending a bigger discount for bigger orders, but the values are reversed — orders of 10 or fewer actually get less discount than logic intended, once a colleague catches that '=10' should have been included in the first branch using >=. The fix highlights why testing the exact boundary value (10) before shipping a formula matters.
Common mistakes beginners make
Missing a closing parenthesis on nested IFs
Each nested IF adds another closing parenthesis needed at the end. Fix: count your open and close parens, or better, switch to IFS to avoid deep nesting entirely.
Using = instead of == or forgetting quotes on text
=IF(C2=Yes,...) errors because Yes is treated as an undefined name. Fix: text comparisons need quotes — C2="Yes".
Wrong operator direction (> vs >=)
A tier boundary written as >90 instead of >=90 silently excludes anyone who scores exactly 90. Fix: always decide inclusive vs exclusive boundaries deliberately and test them.
IFS with no catch-all
Omitting a final TRUE,default clause means any row that doesn't match a listed condition returns #N/A. Fix: always end with TRUE, followed by a sensible default.
Overusing nested IF instead of IFS or lookup tables
Five or six nested IFs become unreadable and error-prone. Fix: use IFS for ranges of conditions, or a lookup table with VLOOKUP/XLOOKUP for exact-match tiers.
Best practices
- Keep nested IFs to two levels max; switch to IFS beyond that.
- Always include an else/default value so no row returns blank or #N/A unexpectedly.
- Decide >= vs > deliberately at every tier boundary and document it.
- Use AND()/OR() inside the logical_test argument rather than trying to chain multiple IFs for compound conditions.
- Test formulas against boundary values, not just typical mid-range values.
- Wrap formulas that might error in IFERROR only after confirming the logic itself is correct.
- Add a comment or helper column explaining complex condition logic for future editors.
Professional tips
- IFS evaluates top to bottom and stops at the first TRUE — order your conditions from most specific to least specific.
- You can nest an IFS or IF inside another function like SUMPRODUCT for advanced conditional totals.
- Named ranges (like ThresholdRate) make IF formulas dramatically easier to read than raw cell references.
- F9 while editing a formula lets you highlight part of it and see its evaluated result — great for debugging nested logic.
- For simple two-outcome flags, IF is still faster to write and read than IFS; save IFS for three or more branches.
Practice exercises
Pass/fail flag
Build a 10-row gradebook and write one IF formula that returns Pass or Fail based on a 60-point cutoff.
Three-tier shipping calculator
Create a weight column and write a nested IF (or IFS) that assigns Small Parcel, Standard, or Freight.
Compound eligibility check
Combine AND() inside an IF to flag rows that meet two separate numeric thresholds simultaneously.
Convert nested IF to IFS
Take a 4-level nested IF you've written and rewrite it as an IFS statement, confirming both return identical results.
Review questions
What are the three required arguments of IF?
The logical test, the value to return if true, and the value to return if false.
Why might you prefer IFS over nested IF?
IFS reads as a clean list of condition-result pairs evaluated top to bottom, which is easier to write, read, and debug than multiple layers of nested parentheses.
What happens if no condition in an IFS formula is true and there's no default?
The formula returns a #N/A error.
How do you test two conditions that both must be true inside an IF?
Wrap them in AND(), e.g. =IF(AND(cond1,cond2),true_val,false_val).
Why is testing a boundary value like exactly 60 or exactly 90 important?
It reveals whether your operator (> vs >=) matches the intended business rule, preventing silent misclassification at the edge.
Key takeaways
- IF always needs exactly three arguments: test, true result, false result.
- Text comparisons inside IF require quotation marks.
- Nested IFs work but become unreadable past two levels.
- IFS evaluates conditions in order and stops at the first match.
- Always add a default/catch-all so every row returns a value.
- AND()/OR() let you test compound conditions inside a single IF.
- Test formulas at boundary values, not just typical values.
Frequently asked questions
Is IFS available in all Excel versions?
IFS requires Excel 2019, Microsoft 365, or Excel for the web; older versions only have nested IF.
Can IF return a formula instead of plain text or numbers?
Yes — either branch of IF can be another formula, function, or even another IF.
What's the difference between IF and SWITCH?
SWITCH compares one value against a list of exact matches; IF/IFS evaluate any logical expression, including ranges and comparisons.
Does IF work with dates?
Yes, dates are numbers internally, so comparisons like B2>TODAY() work directly.
Can I leave the false argument blank?
Yes, =IF(B2>60,"Pass") returns FALSE automatically if the condition isn't met, but it's clearer to specify an explicit value.
How many conditions can IFS handle?
Up to 127 condition-result pairs, though realistically more than 6-8 should become a lookup table instead.
Why does my IF formula return TRUE or FALSE instead of my text?
You likely omitted the value_if_true/value_if_false arguments, so Excel defaults to the raw boolean result.
Related lessons
12 min • Beginner
15 min • Easy
20 min • Intermediate
20 min • Advanced