The IF function is helpful in "customizing" the result of your logical test😀
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.
The IF function is built with three arguments
- The logical test
To compare two data
- Result if the test is TRUE
Generally a text or the result of a formula
- 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 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 do a test to check if the amount is the same for each row.
Does the amount of the invoice equal the amount paid?
The test to compare the two amounts is simple to create. We have to compare if the value in column B equals the amount in column C. The test is:
And when you copy this test to the other 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
Customize the result with a text
Leave TRUE or FALSE is not user-friendly (indeed). So we will customize the result of the test. This is where the IF function will help us.
- The first argument, the same test
- The second argument, the word "OK"
- The third argument, the word "Wrong amount"
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.
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.
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 argument of the function 😱😱😱
If you want to return an empty result with the test FALSE, you must fill the third parameter with "" (2 double quotes without space between).
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 it's this simple function (B2-C2). So, we can write the IF function like this now.
NOW it's easy to analyze the result. In column D, we have the difference only for the case when the two values are not equal. That's what we want 😉😃