IF functions (often called IF statements) are one of the most versatile functions in Excel. Want to sift through a long list of data? Perfect match. Want to set up your spreadsheet to check any input you type in? They can do it! Want to automate your spreadsheet? These guys can literally do anything…
An IF function checks whether a certain condition is true and then displays a certain value if the test turns out to be true, and another if it turns out to be false. A real-life use could be to test if the water in the kettle is boiling. If it is (true), then pour the coffee. If it isn’t (false), then turn on the kettle. Another use might be to check if today is going to be a sunny day. If it is (true), then wear shorts. If it isn’t (false), then take an umbrella!
Below, you’ll find a 1500 word article exploring most of the uses of an IF function. If you encounter another, please let me know in the comments. So let’s get started!
First of all, to use the IF function, we need the following syntax:
- logical test = the test to be carried out (generally a comparison of cell values)
- value_if_true = the value to display if the above test turns out to be true
- value_if_false = the value to display if the above test turns out to be false
All three of these criteria are required for the function to work.
How to Use it
There are countless ways in which to use the IF function. Here are but a few:
Check if a Value is Equal to Another Value
We can do a basic test with the IF function like this:
In the image above, I indicated how many dogs I own in cell B1. Then I ran a test in cell B2 to see if I own exactly 5 dogs. In other words, I used an IF function in cell B2 and performed a test on the value of cell B1. If the test turned out to be true, I displayed the word “Yes”, and if not, I displayed the word “No”.
Now watch what happens when I change the value in cell B1.
Since the value in cell B1 is not exactly equal to 5 (as our test requires), the test turns out to be false and Excel displays the word “No”.
Here’s a tip: Whenever you work with text in an Excel formula (as with the “Yes” and “No” above, you have to enclose it in double quotation marks.
Check if a Value is Greater Than Another Value
We can also test whether some value is greater than or smaller than another, like so:
In this example, I tested whether reps at Nelly’s Bookshop met their required quota for the month, which is 10 books. The if function in cell C3 checks if the value in cell C2 if greater than 10. If it turns out to be true, Excel displays a “Yes”, and if not, Excel displays a “No”. This function was then copied down into the rows below. Each if function checks the value of its corresponding cell.
Here’s a tip: If you want to copy any formula down to the rows below it, do this: Select the formula you want to copy, then click on the small square in the lower right hand corner of the selected cell and drag it down as far as you like.
Notice in line 5 where Excel returns a “No” for Lisa who has sold 10 books. This is because our function tests whether a value if strictly greater than 10. To fix this issue, we would need to check whether the value is equal to or greater than 10, like so:
In this case, the function returns “Yes” when checking if Lisa’s 10 books met the quota.
Check if a Value is Less Than Another Value
Similar to the section above, we can also check whether some value is less than another:
And we can also check if some value is less than or equal than another. Note how James’ answer switches from “No” to “Yes” when you introduce this change.
Check if a Value is Not Equal to Another Value
We can also check is some value is not equal to another value by using the operator <> (opening angle bracket followed by a closing angle bracket). If you think about it, this operator checks whether some value is either less than or greater than another value, but not equal to it.
A great place to use this is to check is someone threw a 6 using a dice. He will only win the jackpot if he throws a 6 (no more, and no less). We can check whether he won like this:
Compare Text Using an IF Function
Another great use for the IF function is to compare text. This can be very helpful if you are working with names of people or objects. For instance, we can find all the occurrences of the name Bob in a list of contacts, like so:
Notice that any text in a formula has to be enclosed in double quotation marks. If not, you will encounter an error. The only exception to this rule is when you use the keywords TRUE or FALSE.
Also notice that this text comparison is case insensitive, meaning that either “Bob” or “bob” will be counted, as seen in row 6 in the image above. If you wish to make a case sensitive comparison, you might want to investigate the EXACT function, i.e. =IF(EXACT(A2,”Bob”,1,0).
It is then very easy to count the number of occurrences of Bob’s name by using the SUM function:
Check if a Cell Has a Certain Property
We can check whether a cell has a certain property by using any of the functions starting with the letters IS. All of these functions test a cell’s contents and returns either TRUE or FALSE as an answer, which is what we need for the logical test in the IF function. Below is a short list of the most common ones. To see all of them, click in any cell in Excel and type =IS without pressing enter. Excel will display a list of functions starting with the letters IS.
|=ISBLANK()||Checks whether a cell is blank/empty.|
|=ISEVEN()||Checks whether the value in a cell is even.|
|=ISODD()||Checks whether the value in a cell is odd.|
|=ISNUMBER()||Checks whether a cell contains a numeric value.|
|=ISTEXT()||Checks whether a cell contains text.|
|=ISNONTEXT()||Checks whether a cell does not contain text|
|=ISLOGICAL()||Checks whether a cell contains a logical value (either “TRUE” or “FALSE)”.|
|=ISFORMULA()||Checks whether a cell contains a formula.|
|=ISERROR()||Checks whether a cell contains an error (#N/A, #NAME?, #REF!, etc.).|
|=ISNA()||Checks whether a cell contains the #N/A error.|
For instance, we can easily check if a cell is blank, like this:
Logical Functions Within an IF Function
Logical functions are very useful inside an IF function. They allow us to conduct two checks on our data and manipulate the outcome based on which of them is true. These are two common logical functions used in Excel, AND and OR. And checks that both tests are TRUE, while OR checks that either one or the other tests are true. Below is a so-called truth-table (used by computer programmers) that tells you what each of these functions do in a certain situation:
|Operator||Test A is true||Test B is true||Both tests are true||Both tests are false|
As an example, say you wish to buy a used car. You have obtained a few candidates that you like, and you want to use Excel to help you make the final decision. Let’s suppose you want a vehicle that was made in 2008 or later, with less than 60 000 km on the odometer. The word “and” in this sentence signals a perfect situation to use the AND function.
For this, we will setup two tests. Firstly, we’ll test if the year (column D) is greater than or equal to 2008. Secondly, we’ll test if the odometer (column E) is less than 60 000. Our spreadsheet will like the one below (column G contains the IF function):
Excel displays a “Yes” only if both tests turn out to be true, and “No” for all other situations.
On the other hand, let’s suppose you want any car as long as it is made by either Toyota or Ford. The “or” word in this statement signals the use of an OR function. To do this, we will setup two tests. Firstly, test that the make (column A) is equal to “Toyota”. Secondly, test that that the make (same column A) is equal to “Ford”. See the spreadsheet below:
Excel displays a “Yes” if either one or the other test is true, and “No” if none are true.
There you have it – a quick rundown of everything you can do with an IF function. But this is not the end of the road, by far! There is so much more you can do with them!
Have you found another use for an IF function? Please share it in the comments below.