Excel: The IF statement

One of the more useful Excel formula to have in your locker is the IF statement. It’s a simple way to apply logic and decision making to your spreadsheets, reducing the need for manual work. Here you’ll find a simple run through of how it works but drop me a message or comment if you have any questions.

The IF statement is split into three clear sections, we’ll look at each section in turn, but first, the start of the formula itself… =IF(    this helps explain the first section, which is called the logical test, or rule, you’re basically saying “If the following logic rings true…..move to the next section of the formula…”.

1 - The Logical Test

This first section of the IF statement is where you set your logic, and it’s best for me to explain using some examples.

Let’s say you have a set of data, which is for various mobile phones/tablets, each has a cost and you want to apply a 20% mark-up to Apple products and 15% mark-up to everything else to get to a price. Therefore my logical test becomes, “check if the ‘Make’ cell contains the word “Apple”, I’m only interested in Apple as they are the only product to have a different mark-up %.

You do this in a logical way using the IF formula, see below.

I’ve started with =IF( in the price cell, then clicked on the make, and used the logical test =”Apple”. The use of “ “ lets Excel know that it’s looking for text. The formula helpfully lets you know what’s coming next, [value_if_true].

2 – Value if True

You use this section of the formula to let Excel know what to do next if the logical statement you’ve set turns out to be true.

Using our example, if the make is Apple, we want Excel to calculate the price using a 20% mark-up. Therefore our value if True becomes the cost multiplied by 120%. Shown below, you add a comma after the logical test, then add the formula to multiply the cost cell by 120%. 

The formula is almost complete, Excel knows what to do if the Make is Apple, we now need to tell it what to do if it’s any other brand.

3 – Value if False

The third and final stage to a simple IF formula, Value if False. You’ve told Excel what to do when your logical test is true, it now needs to know what to do if it’s not true. In our example we know any other brand attracts a 15% mark-up. Therefore we tell Excel that if false, it’s the cost multiplied by 115%, per below. 

If you then close your brackets and drag the formula down, or copy the formula into the other cells, it will apply the same logic to every row, giving you the following result. I’ve added the section to the right so you can see how the mark-up % is changing depending on the brand.

That’s it for a simple use of the IF statement – there are ways you can make more sophisticated IF formula, such as nesting more than one IF together or making Excel consider if more than one thing is true (IF -> AND) or if one of many things are true (IF -> OR) but I’ll look to cover those in a future article.