The IF function and nested IFs
Open the practice in the trainer →
IF compares a value with what you expect and returns one of two results:
=IF(logical_test, value_if_true, value_if_false)
Scenario from the manual: budgeting and expenditure. If actual spending exceeds the budget — overspent, otherwise — within budget:
=IF(B2>A2,"Overspent","Within budget")
Nested IFs
When there are more than two outcomes, put another IF inside the IF. Let's add a third option — the budget was not used at all:
=IF(B2=0,"Not used",IF(B2>A2,"Overspent","Within budget"))
Excel checks the conditions in order and stops at the first true one — order matters.
In the trainer both , and ; work as argument separators.
Tasks in the trainer
- In C2:C6: “Overspent” if actual is greater than budget, otherwise “Within budget”.
- In D2:D6: “Not used” if actual = 0; “Overspent” if actual > budget; otherwise “Within budget”.
- In E2:E6: a bonus of 5% of the savings (budget − actual) if actual is within budget and greater than 0, otherwise 0.
- In F2:F6 calculate the variance from budget in % ((actual − budget) / budget); return 0 if the budget is 0.
- In B8 count the items with status “Overspent” (column C).
Solve the tasks in the trainer →
Check yourself
- A1 = 0. What does =IF(A1>10,"many",IF(A1>0,"few","none")) return?
- What does IF return when the condition is false and the third argument is omitted?