The IF Function with Excel

The IF Function with Excel
Last Updated on 12/05/2024
Reading time: 3 minutes

The IF function is helpful in "customizing" the result of your logical test😀

Logical Test

A logical test is the starting point for comparing 2 values together.

Which age is greater than 21

The IF function

The IF function analyzes a test and returns a custom result in the case when the test is TRUE and another result when the test is FALSE.

  1. The logical test

    To compare two data

  2. Result if the test is TRUE

    Generally a text or the result of a formula

  3. Result if the test is FALSE

    It could be an empty result

=IF(test, the test is TRUE, the test is FALSE)

Example: Check the invoices

We want to check if your company's money received is the same as the value of the invoices. Everything looks correct here, but let's have a closer look 🧐 Let's do a test to check if the amount is the same for each row.

Invoice payment

Does the amount of the invoice equal the amount paid?

The test to compare the two amounts is simple to create. We must compare if column B's value equals column C's amount. The test is:

=B2=C2

You get this result when you copy this test to the other cells.

  • When the test is TRUE, it's because the amount is the same in both cells
  • When the test is FALSE, the amount is not equal
Test to detect if a payment is the one expected or not

Customize the result with a text

Leave TRUE or FALSE is not user-friendly. So we will "customize" the test result. This is where the IF function will help us.

  1. The first argument of the IF is the logical test
  2. Then, the second argument, the word "OK"
  3. The third argument, the word "Wrong amount"

=IF(B2=C2,"OK","Wrong amount")

Display Ok or Wrong result instead of True or False

Optional: Reverse the test

There is not only one way to write a test. If you prefer to test if the amount between the 2 cells is different (symbol <>), you must change the order of the result accordingly.

=IF(B2<>C2,"Wrong amount","OK")

In function of the logic of the test the result must be written accordingly

Why does the function IF return FALSE?

But, we don't need to write a message when it's "Ok" to have too much information in our document. Let's remove the third argument, then.

=IF(B2<>C2,"Wrong amount")

The IF function returns FALSE why

How do you return a Null result?

The mistake in the previous step is that the IF function has no indication when the result is FALSE. We have forgotten the 3rd argument of the function 😱😱😱 If you want to return an empty result with the test FALSE, you must fill the third parameter with "" (two double quotes without spaces between them).

=IF(B2<>C2,"Wrong amount","")

IF function can return empty result when the test is FALSE

Return the Gap between the amounts

To finish, we can enhance our formula. Instead of writing "Wrong amount," we prefer to display the difference between the amount received and the amount of the invoice 🤩🤩🤩 The formula to return the difference is this simple function (B2-C2). So, we can write the IF function like this now.

=IF(B2<>C2,C2-B2,"")

IF returns the gap between the amount of the invoice and the amount paid

Now, it's easy to analyze the results. In column D, we have the difference only for the case when the two values are unequal. That's what we want 😉😃

7 Comments

  1. Aliu
    19/02/2020 @ 14:36

    I am just begining learner from uganda.Please kindly assist me in excel.I need your support

    Reply

  2. António Pereira
    15/09/2019 @ 16:56

    Thank you for this excellent teaching site. Is one of the most complete and clear.

    Reply

    • Frédéric LE GUEN
      16/09/2019 @ 16:11

      Thanks ☺

      Reply

  3. Francis
    21/07/2019 @ 00:34

    Do you have these exercises anywhere in an excel file that we can download to do the exercises

    Reply

    • Frédéric LE GUEN
      23/07/2019 @ 07:08

      No, they are all copyrighted

      Reply

  4. Patricia Murphy
    06/08/2018 @ 23:25

    Very nice explanation. My students and sometimes myself has a difficult time with functions. Do you have any practice lessons I can share with my students?

    Reply

  5. JESUS LOVESBRIAN
    11/01/2018 @ 11:50

    Grading of students in class according to their average

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

The IF Function with Excel

Reading time: 3 minutes
Last Updated on 12/05/2024

The IF function is helpful in "customizing" the result of your logical test😀

Logical Test

A logical test is the starting point for comparing 2 values together.

Which age is greater than 21

The IF function

The IF function analyzes a test and returns a custom result in the case when the test is TRUE and another result when the test is FALSE.

  1. The logical test

    To compare two data

  2. Result if the test is TRUE

    Generally a text or the result of a formula

  3. Result if the test is FALSE

    It could be an empty result

=IF(test, the test is TRUE, the test is FALSE)

Example: Check the invoices

We want to check if your company's money received is the same as the value of the invoices. Everything looks correct here, but let's have a closer look 🧐 Let's do a test to check if the amount is the same for each row.

Invoice payment

Does the amount of the invoice equal the amount paid?

The test to compare the two amounts is simple to create. We must compare if column B's value equals column C's amount. The test is:

=B2=C2

You get this result when you copy this test to the other cells.

  • When the test is TRUE, it's because the amount is the same in both cells
  • When the test is FALSE, the amount is not equal
Test to detect if a payment is the one expected or not

Customize the result with a text

Leave TRUE or FALSE is not user-friendly. So we will "customize" the test result. This is where the IF function will help us.

  1. The first argument of the IF is the logical test
  2. Then, the second argument, the word "OK"
  3. The third argument, the word "Wrong amount"

=IF(B2=C2,"OK","Wrong amount")

Display Ok or Wrong result instead of True or False

Optional: Reverse the test

There is not only one way to write a test. If you prefer to test if the amount between the 2 cells is different (symbol <>), you must change the order of the result accordingly.

=IF(B2<>C2,"Wrong amount","OK")

In function of the logic of the test the result must be written accordingly

Why does the function IF return FALSE?

But, we don't need to write a message when it's "Ok" to have too much information in our document. Let's remove the third argument, then.

=IF(B2<>C2,"Wrong amount")

The IF function returns FALSE why

How do you return a Null result?

The mistake in the previous step is that the IF function has no indication when the result is FALSE. We have forgotten the 3rd argument of the function 😱😱😱 If you want to return an empty result with the test FALSE, you must fill the third parameter with "" (two double quotes without spaces between them).

=IF(B2<>C2,"Wrong amount","")

IF function can return empty result when the test is FALSE

Return the Gap between the amounts

To finish, we can enhance our formula. Instead of writing "Wrong amount," we prefer to display the difference between the amount received and the amount of the invoice 🤩🤩🤩 The formula to return the difference is this simple function (B2-C2). So, we can write the IF function like this now.

=IF(B2<>C2,C2-B2,"")

IF returns the gap between the amount of the invoice and the amount paid

Now, it's easy to analyze the results. In column D, we have the difference only for the case when the two values are unequal. That's what we want 😉😃

7 Comments

  1. Aliu
    19/02/2020 @ 14:36

    I am just begining learner from uganda.Please kindly assist me in excel.I need your support

    Reply

  2. António Pereira
    15/09/2019 @ 16:56

    Thank you for this excellent teaching site. Is one of the most complete and clear.

    Reply

    • Frédéric LE GUEN
      16/09/2019 @ 16:11

      Thanks ☺

      Reply

  3. Francis
    21/07/2019 @ 00:34

    Do you have these exercises anywhere in an excel file that we can download to do the exercises

    Reply

    • Frédéric LE GUEN
      23/07/2019 @ 07:08

      No, they are all copyrighted

      Reply

  4. Patricia Murphy
    06/08/2018 @ 23:25

    Very nice explanation. My students and sometimes myself has a difficult time with functions. Do you have any practice lessons I can share with my students?

    Reply

  5. JESUS LOVESBRIAN
    11/01/2018 @ 11:50

    Grading of students in class according to their average

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *