Excel Practice
Lessons Lesson 26

IF

About this lesson

IF checks a test. It returns one value when the test is TRUE and another when it is FALSE. So a cell can decide for itself what to show.

By the end you can

  • write an IF with a test, a value for yes and a value for no.
  • return text or a calculation from an IF, with quotation marks in the right place.
  • say what a missing third argument returns and why it matters.

Practises IF IFS AND OR

The idea

Three arguments, in the order you would say them: if this, then that, otherwise the other. The test is a comparison from the last two lessons. The two outcomes can be anything: words in quotation marks, numbers, a calculation, or a reference to another cell. They need not be the same kind of thing as each other.

The mistake is the missing third argument. IF(test, 25) returns FALSE when the test fails. That is neither a number nor a blank. A column of bonuses with a FALSE in it will not add up. Write the no-case every time.

The mistake to watch for

Leaving out the third argument. IF with two arguments returns FALSE when the test fails. FALSE in a column of bonuses is not a number. It breaks the total, and it looks like a value somebody meant. Always write the third argument, even if it is 0 or two quotation marks with nothing between them.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

IF takes three things: a test, what to show if the test is TRUE, and what to show if it is FALSE. IF(test, if yes, if no). The target is in B8.

In D2, type =IF(B2>=B8,"Met","Missed").

Did Priya reach the target? Text results go in quotation marks. Numbers do not.

D2
Row A B C D E F G
1 Agent Calls answered Avg handling min
2 Priya N 68 4.2
3 Marek D 51 6.1
4 Joy A 74 3.8
5 Tom B 59 5.5
6 Ines R 60 4.9
7
8 Target calls 60

Every step

  1. 1

    IF takes three things: a test, what to show if the test is TRUE, and what to show if it is FALSE. IF(test, if yes, if no). The target is in B8. In D2, type =IF(B2>=B8,"Met","Missed"). Did Priya reach the target? Text results go in quotation marks. Numbers do not.

    Hint. Test, yes-value, no-value.

  2. 2

    Read =IF(B3>=B8,"Met","Missed") and say what it returns. Marek answered 51.

    Hint. Third argument.

  3. 3

    In D6, write the same test for Ines, who answered exactly 60, and see which word comes back.

    Hint. B6 this time.

  4. 4

    The two outcomes do not have to be the same kind of thing. In D3, show Met if Marek reached the target. Otherwise show how many calls short he was: B8 minus B3, with no quotation marks, because it is a calculation.

    Hint. Third argument is a subtraction.

  5. 5

    Agents who hit the target get a bonus of 25, and everyone else gets nothing. In E2, write a formula using IF that works out Priya's bonus. Numbers need no quotation marks.

    Hint. Test, 25, 0.

  6. 6

    Leave the third argument out and see what IF does with a failed test. Read =IF(B3>=B8,25) for Marek and say what it returns.

    Hint. Nothing was given for the no-case.