Course contents · Logical functions

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

  1. In C2:C6: “Overspent” if actual is greater than budget, otherwise “Within budget”.
  2. In D2:D6: “Not used” if actual = 0; “Overspent” if actual > budget; otherwise “Within budget”.
  3. In E2:E6: a bonus of 5% of the savings (budget − actual) if actual is within budget and greater than 0, otherwise 0.
  4. In F2:F6 calculate the variance from budget in % ((actual − budget) / budget); return 0 if the budget is 0.
  5. In B8 count the items with status “Overspent” (column C).

Solve the tasks in the trainer →

Check yourself

  1. A1 = 0. What does =IF(A1>10,"many",IF(A1>0,"few","none")) return?
  2. What does IF return when the condition is false and the third argument is omitted?