Sometimes, when using Excel, you’ll want to perform different tasks depending on the result of a certain query. The IF function in Excel is perfect for that; it tests if a condition is true or false, and then performs the appropriate action.

Note: The terms “IF statements” and “the IF function” are used interchangeably in this article. Although it’s probably not technically correct, the IF function in Excel is more-or-less a basic version of the IF statement found in any modern programming language.

Also: Actually, about that: if you’ve used a programming language before, you’ll probably find this pretty straightforward to pick up. If you haven’t, though, just take it slowly and play around with the IF function. It will all sink in eventually :)

Example of an IF Statement Situation

In our introduction to VLOOKUPs tutorial, we used some sample data to decide whether or not VAT should be added on to certain sales we had made in the past year. So let’s use a similar example to demonstrate how IF statements can actually be more appropriate than VLOOKUPs.

Let’s say you had the below spreadsheet [“sales.xls”], with the name of each item sold, the quantity, and – most importantly for us – a column noting whether or not VAT is applicable.

What we need to do is calculate a total and factor in VAT where appropriate. Of course, we could go through the spreadsheet row-by-row and insert manual VAT calculations, but an IF statement will make our lives exponentially easier!

Format of an IF Statements

An IF statement is written with the following syntax:

=IF(Condition,Actions if True,Actions if False)

Let’s break that down:

• =IF() tells Excel that we’d like to use the IF statement.
• Condition is the condition our formula is going to be testing. Essentially, it’s saying: if this condition is true.
• Actions if True is where we tell Excel what to do if the condition is true.
• Actions if False is where we tell Excel what to do if the condition is not true.

Don’t worry if that’s confusing, it’ll hopefully become clearer in the next section as we write some example IF statements.

Example Usage of an IF Statements

Let’s try out an IF statement with the “sales.xls” spreadsheet we looked at before.

Note: Before we look into the VAT, let’s clarify how the total should be calculated. In our situation, all we need to do is multiply the price of the item by the quantity sold. So, for the Tuna in Row 2, total without vat would be ‘=C2*D2’.

**Note continued: ** To add VAT to that, we’d then multiply the result by 1.2 (to get the current price plus 20%). So, if VAT was to be added to the Tuna sale in Row 2, we’d type ‘=C2*D2*1.2’. We will only add VAT where the value in column E (“VAT applicable?”) is “Yes”.

So our formula in Cell F2 is going to look like this:

=IF(E2=”Yes”,C2*D2*1.2,C2*D2)

Make sense? Let’s break it down:

• =IF() is, of course, the basic IF formula for Excel.
• E2=”Yes” is the condition we’re checking. Depending on whether or not it’s true or false, we’re going to do perform a specific calculation.
• C2*D2*1.2 is the calculation to be performed if our condition is true. In other words, if B5 is “Yes”, multiply the price by the quantity by 1.2.
• C2*D2 is the calculation to be performed if our condition is not true. In other words, if B5 is not equal to “Yes”.

Since cell E2 is equal to “yes”, our formula will multiply the price by the quantity, and then multiply again by 1.2. Our result is ($2.19*5*1.2) = $13.14.

At this point, you can click and drag to repeat the formula down the column.

However, what I’m going to do is complete the formula for the next row manually, just to make it a bit easier for you to see what’s going on.

=IF(E3=”YES”,C3*D3*1.2,C3*D3)

Again, let’s break it down:

• =IF() is our basic IF formula.
• E3=”Yes” is the condition we’re testing.
• C3*D3*1.2 is the calculation to be performed if E3 is equal to “Yes”.
• C3*D3 is the calculation to be performed if E3 is not equal to “Yes”.

Because Cell E3 is not equal to “Yes”, we’ll be performing the calculation to be performed when our condition is false (i.e. “C3*D3”).

Note: You’ve perhaps already worked this out, but you could just as easily write the inverse of this formula to test whether or not cell E3 is “No”. In that situation, the formula would look like this:

=IF(E3=”No”,C3*D3,C3*D3*1.2)

Now, VAT will only be taken into account when our condition is false. In any case, this is just a demonstration of another way we could have written our IF formula; both will provide the exact same result.

This is my spreadsheet once the formula for all rows has been entered:

Summary

The IF statement is an absolutely fantastic tool, which can be harnessed to perform some very involved calculations when needed. At the same time, it can also perform relatively simple tasks, such as adding VAT to calculations depending on whether or not it’s needed.

Please don’t stress too much if it seems like a lot to take in – it is! Especially for non-programmers, IF statements can seem like a handful at first, but my advice is to just practice slowly and go easy on yourself – it will sink in!

If you’re curious, this w3schools article on IF statements might also be worth checking out!