The IF formula

Definition

What does the IF formula do? Normally you test something, if it’s true you do something and if it’s false you do something else. This concept is very important, because something can be correct or not correct. You can test if something is more than 2, then ANY other answer is false. Or you can test if something is blue, if it’s blue the answer is true, if it’s ANY other color the answer is false.

So true only test for one variable and false for the rest.

The makeup of the formula

The basic if comprises of 3 fields:

• The logical test fields, where you will insert something you want to be tested, for example if something is more than 1000. (x>1000)
• The true field, where you would put the action to be taken if your test is true, for example if the number is over a 1000 (>1000) then a certain action must be taken. You can do whatever you want here. You can put another formula here, or just type a word if the number is more that a 1000, for example the word “yes”.
• The false field, where you would put the action to be taken if your test is NOT true, for example if the number is under 1000 (not >1000) then the word “NO” must be given. (REMEMBER! Everything else that is NOT true, is false)

Basic Examples

This basic IF as discussed above, would look like follows:
=if(500>1000,”YES”,”NO”). The logical test is if 500 is bigger than 1000. If 500 was over a 1000 then the result would be true, thus “YES”, in ANY other case the result would be false, thus “NO”.

Also see the example below, for more such examples.

Example

So what other things can you do with “IF”

• Instead of “YES” or “NO” you can insert any other wording you would prefer, or any other formula can be done, if the result is true or false.
• In the test field, you can test anything. Go through the example below

Example

In this example we want to test if the cells in the data column are “yes” or “no”

        
Also note that the formulas are not case sensitive.

More complex examples

Ok, so now you now the basics. What if there were not only a “yes” or a “no” answer to your test, but also a “don’t know” answer. How would this work? In these instances one would rather put another “IF” formula in an existing one. WOW!!! This sounds difficult. No it isn’t. Stick to the basics and it’s actually easy.

The first part would look the same. First test if the answer was “yes”. If it’s true then still put “y” in the true field. Remember that any other result is false. So in effect the false field can be “no” or
“don’t know. In the false field, start another “IF”, just like our first simple one, first test if the remaining answers are no in the logical test, if the result is true the answer will be “n”. The false field in this second if, will be anything else, thus “don’t know”. Ok, did we lose you there???

Let’s do an example:

Example

You see, in our previous examples the answer were only “yes” or “no. Thus we could test if something was “Yes”. If it was not “Yes” it had to be “No”, thus in the false field we could only put “no”.

In this more complex example, we again first test if the answer was “Yes”, if it is yes, then we could give a “y” (Check Blue). If the result is not yes, we do not want to only insert a “no” in the false field, because there are two possible answers left (“no” and “don’t know”). Thus, we use another IF function in the false field (Check red).

The formula function bar

Another way of easily understanding this is when you type in “=if(”, click on the function button to give the detail, it’s easy to understand it. Remember to first open the bracket of the formula before clicking there.
 

When you click on the formula bar function the following would pop up:

You can do your formulas in here, which will explain every field. Our complex formula would have looked like follows in this table:

See! It’s easier to understand in this table when you are not yet experienced in formula building.

Troubleshooting

So why do people struggle with IF functions? Remember, you are testing something; it’s either true or not. Test everything, if there are three fields to test, test them all with multiple if formulas.

The most common problems are as follows:

• Don’t forget to open the bracket before clicking on the formula function bar, otherwise it will open a list of all the formulas
• When using more than one if function, keep track of the brackets. The brackets may become a lot after a while. Excel will auto complete the brackets if you did not mess them up to much.
• It’s sometimes easier to build the formula inside out. Start with the middle If function and finish it, then build another if function around the first one. This helps with bracket problems as well. Make sure everything works before doing the next one
• Remember when testing text to put it in inverted commas (“…”) otherwise you will receive an error. Excel does not recognize text when it is without inverted commas.

Things you can do with the if formula

• Identify spaces in your data
• Test any logical equation

<-----Back to index

Next topic----->