When I train this subject my delegates normally say “oh no I find this difficult and don’t want to use it”. But they need this knowledge. So here I am going to explain how to use this in the simplest way.
You do this feature every day and don’t realise it. If it’s raining outside you take an umbrella if it isn’t you leave it at home. There you just competed and If Function or Statement. This is exactly as you would use it in Excel. The If Function is made up of three parts.
- The test.
- What to do if the result is yes.
- What to do if the result is no.
So here is the same thing but in Excel speak.
You need the commas. They are very important. They distinguish between the three parts, so don’t forget them.
You don’t have to type this function. Excel comes equipped with a lovely dialog box that helps you through. Especially the first few times. When you’re really comfortable with the function you can start typing and still Excel will help you on the right path with the tip that shows up.
So to start we need a scenario to work to. Let us say we have a spreadsheet that holds the income for each of our sales people and we need to calculate a commission based on their sales.
As you can see I have set a target of £1000 each sales person needs to achieve before getting a 12% commission. First off you can see who will get a commission and who will not so we are going to use the If Function with results in words not numbers. No calculations involved. The Status column will house “yes” or “no”.
Select the D8 cell and click on the fx button on the formula bar. This will reveal the Functions Dialog box.
Select the drop down menu from the second part in this box, called the ‘Or select a category’. The ‘Most Recently Used’ category is the default which shows you the functions you have recently used. You can either select the All category and scroll down to find IF or select the logical category and scroll down a shorter list to find IF.
Select the OK button.
Here you can see the three parts separately making your life a little easier. The first line asks for the logical test to be typed in. Read the bit after the bold which explains it a little.
So in our case we need to say to test the cell that has the sales figure in to see if it is bigger (or smaller) than the target we have in cell D3. In maths there is a symbol to express bigger than > or smaller than <.
Start typing C8>D3
The result is show on the right as TRUE. But what will happen if someone gets exactly the same amount as the target. Well they won’t get a commission as 1000 is NOT bigger than 1000. To make the example work so the person who has 1000 will get a commission we need an or equals to as well.
The equals is ALWAYS after the symbol be it greater than or less than.
Now select the second line down and type the word “Yes” as you want to give the person a commission if they reach the target. On the other hand, Type “No” in the third line down as you don’t want to give them a commission if they don’t reach the target.
Select the OK button.
You cannot just autofill (see my earlier Tip Tip – Autofill explained in MS Excel ) down the sheet as you have no absolute referencing in your formula. Refer to my previous tip Tips- Understanding referencing in Excel . Your formula should read as follows.
Now you can autofill down the sheet for the rest of the answers.
Using calculations in the IF Function
Now we can move onto using calculations in the IF Function. You can have a mixture i.e. a calculation if the result is true and a word if it is False such as I have here.
You can have up to 7 IFs in a nested formula. I shall explain this next time.