The basic “IF” function

The IF function is one of the first formulas you should learn to use.

In basic English it says:

IF something happens do this, otherwise do that. For example, depending on whether our bank balance is positive or negative there is a different interest so graphically this is what it may look like.

In Excel it can be used in a number of places. We use it to:

• create alternate scenarios
• take different actions depending on a cell number
• error checks e.g. is the balance sheet balancing, do the percentages add up to 100%

To follow along go to 24.1

A useful example is a bank account.

Let’s say that if you have a negative balance (overdraft) the bank will charge you 10% and if you have a positive balance they will give you 6%. The problem is that in a spreadsheet the bank balance could be negative or positive and it can change.

So what we need is a function that will allow for this. The IF function allows for this.

The syntax of the IF function is

=IF(  logical test    ,  value if true    , value if false )

The Logical Test Part of the IF function

Excel needs an expression or test that will allow it to come up with a true or false answer. So if we said   A1>4 and A1 contains a 5 then Excel would return a TRUE.

In our example we want to test whether a bank balance is positive or negative. A way to express this mathematically would be whether the bank balance is bigger than zero (if it isn’t bigger than zero then it must be equal to or less than zero).

So assuming we have a bank balance in cell A1 our logical test would be

A1>0

By looking at the contents of A1 you would be able to answer the statement yourself and therefore so can Excel.

The common mistake people make is to perform the test in their head and not tell Excel what to do. So we often see a logical test of

A1

In the person’s head they have thought ‘bigger than zero’ but they haven’t told Excel that.

A logical test will typically contain a =, >, <, >= and <= and will have cell references and/ or numbers in front of it and behind it. So any one of the following is a valid logical test

• A1=A2
• A1>100
• A1<=50
• A1+B1>C1+D1
• A1<=VLOOKUP(A100,B100:D200,3,false)

The ‘Value if True’ part of the IF function

Once you have the logical test, all you need to do is tell Excel what must happen if the test is true and false. The first one is what must happen if it is true.

You can put any valid entry here including text (include it in “inverted commas”), numbers, formulas or Excel functions. You can even put another IF function as one of the answers (more about this later).

In our bank example we want it to say 6%

The ‘Value if False’ part of the IF function

In the same way as you need to tell Excel what must happen if the logical test is true, you also need to tell it what must happen if it is false. Again you can include text (include it in “inverted commas”), numbers, formulas or Excel functions.

In our example, as the logical test was A1>0, a negative number in A1 implies an overdraft and results in a FALSE. So Excel will look at whatever you typed into this section to provide an answer.

Watch another video explaining the IF function

Another video on the same example in a slightly different way

To follow along go to 24.1.1