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

By Ali, Founder, Excel Lessons

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.

Read more about this

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 tests something and gives one of two answers.

In D2, type:

=IF(B2>=B8,"Met","Missed")

B8 holds the call target.

Remember: text goes in quotes, 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 tests something and gives one of two answers. In D2, type: =IF(B2>=B8,"Met","Missed"). B8 holds the call target. Remember: text goes in quotes, 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. The third argument.

  3. 3

    In D6, write the same test for Ines. Shape: =IF(test, if_true, if_false). Ines answered exactly 60 calls.

    Hint. B6 this time.

  4. 4

    IF's two outcomes can be different kinds of value. In D3, show Met if Marek reached the target. Otherwise, show B8 minus B3. Remember: numbers need no quotes, only text does.

    Hint. Third argument is a subtraction.

  5. 5

    Hitting the target pays a bonus of 25; missing it pays 0. In E2, write a formula for Priya's bonus.

    Hint. Test, 25, 0.

  6. 6

    IF can run with just two arguments. Read =IF(B3>=B8,25) for Marek and say what it returns.

    Hint. Nothing was given for the no-case.