Condition

Function IF – Examples with payments

Reading Time: 3 minutes

The IF function is very useful to "customize" the result of your logical test.

Logical Test

A logical test is the starting point of the comparison between 2 values or formulas.

In this article, we have present many situations where a logical test is necessary

Which age is greater than 21

But a logical test returns only TRUE or FALSE or 0 and 1 with this tricks. So to customise your result, you have to write your test in a IF function

The IF function

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

The IF function is build with 3 parameters

  • The logical test
  • The result if the test is TRUE
  • The result if the test is FALSE

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

Example: Check the invoices

We want to check if the money received by your company is the same as the value of the invoices. Everything looks correct here but let's have a closer look 🧐.

Let's make a test to check if the amount is the same for each rows.

Invoice payment

Does the amount of the invoice equal the amount paid?

The test to compare the 2 amounts is very simple to create. We have to compare if the value in column B equal the amount in column C. The test is:

=B2=C2

And when you copy this test to the others cells you have this result.

  • 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
Test between 2 cells

Customize the result with a text

Leave TRUE or FALSE is not user friendly (indeed). So we will customise the result of the test. This is where the IF function will help us.

  • First parameter, the same test
  • Second parameter, the word "OK"
  • Third result, 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")

The result is obviously the same 😉

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

Why the function IF returns FALSE?

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

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

The IF function returns FALSE why

How to return a result "empty"?

The mistake in the previous step it's because the IF function has no indication when the result is FALSE. We have forgotten the 3rd arguments of the function 😱😱😱

If you want to return an empty result with the test is FALSE, you must fill the third parameter with "" (2 double quotes without space between).

=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 enhanced 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 returns the difference it's 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 result. In column D, we have the difference only for the case when the 2 values are not equal. That's what we want 😉😃

Related posts

Function COUNTIF

Frédéric LE GUEN

Nested IF in Excel

Frédéric LE GUEN

Functions AND – OR

Frédéric LE GUEN

7 comments

Aliu 19/02/2020 at 14:36

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

Reply
António Pereira 15/09/2019 at 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 at 16:11

Thanks ☺

Reply
Francis 21/07/2019 at 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 at 07:08

No, they are all copyrighted

Reply
Patricia Murphy 06/08/2018 at 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
JESUS LOVESBRIAN 11/01/2018 at 11:50

Grading of students in class according to their average

Reply

Leave a Comment