Sometimes you’re not sure what’s going to be on your sheet and in your cells, and you want to show a value or piece of text, depending on what’s in those cells. This is where the IF function comes in. It’s like what we use in English, IF this happens, I’ll do this, IF NOT I’ll do something different. It’s one of the functions I use all the time, and I’m sure you will!
Example 1 – Has the student passed or failed?
Here I have 4 students who have done an exam and I want to show who has passed or failed. The pass mark is 60% and clearly with just 4 students this is easy to see, but imagine a sheet with for example, 100 students.
In cell C3, start off with by typing =IF(
Here the help box will appear. In the first line it’s showing you what it needs you to fill out. There are 3 parts:
“logical expression” – This just means what are you trying to test for. It needs to be a true or false question. For example in this case, Is the exam mark more than or equal to 60%?
“value_if_true” – This is what you want to put in the cell, if the answer to your question is TRUE, i.e. the exam mark IS more than or equal to 60%.
“value_if_false” – This is what you want to put in the cell, if the answer to your question is FALSE, i.e. the exam mark IS NOT more than or equal to 60% (in other words, less than).
So, to continue with our function, next we write the question we want answering, the logical expression. So, we want to know if the exam mark in cell B3 is more than 60%. First, we add the cell reference B3.
Then we add the more than symbol and the equals sign, which together mean ‘more than or equal to’, then we add the number, in this case 0.6 (note, this is not 60, as 60% = 0.6).
Then we need to tell it, what to do if the above question is TRUE or FALSE. I want it to add the word “PASS” if it’s true and “FAIL” if it’s not. Add a comma, then in double quotes write PASS and FAIL as below. Then close the bracket. So your function should now look like this.
Press Enter and as you can see in cell C3, the word PASS has been displayed, as John has indeed got more than 60% in his exam.
Click on cell C3 and drag the little blue square down to copy the function down to the other cells. Straight away we can see George and Paul haven’t passed the exam.
Example 2 – Referring to a cell
What happens if the teacher believes the exam was too hard and the pass mark should now be 50%? In the example above, we would have to go back into the function and change the 0.6 to 0.5. Possible but more work for us. We can avoid that by referring to a cell where the pass mark has been written in.
Here the pass mark is in cell B15.
Here’s the original formula:
Let’s change the 0.6 to the cell reference, B15. Note I’ve had to put the dollar signs in, as this makes it an absolute reference, i.e. it will always refer to that cell, even when I copy the formula down the rows to the other students. See my post on relative and absolute references.
I copy it down the rows as before and it changes George’s result to a PASS.
Now, by changing the pass mark in cell B15, this will automatically update the Pass/Fail results in the table.
So, certainly use references if you may change the formula values in the future.
Example 3 – Dealing with blank spaces and making your data look prettier
With the examples so far, before adding the exam marks, the table would look like the one below, i.e. in the pass/fail column it’s stating that they have failed because the Exam mark cells are blank.
To get round this, there is a really good use of the IF function, which only shows the content in the cells, if another cell has something in it.
We need to add a check at the start of our formula, so see if the cell has an exam mark in it. If it’s blank then we want it to make the pass/fail column blank.
The start of the formula is as below. It looks in cell B19 and if it IS blank (two double quotes with nothing in between means nothing), then we add nothing (again two double quotes).
So this is if the check is TRUE, now we end with the original formula, as this will run if the cell ISN’T blank (i.e. the false result). Then we end it all with a second closed bracket, as follows:
So, just to summarise that, the first part checks to see if B19 is empty, if it is it makes C19 empty. If it isn’t it checks to see what has been added in B19 is more than 60%, if it is it writes PASS, if not it writes FAIL in C19.
Here’s what the table looks like now. Nice and tidy waiting for results to be added.
As soon as we do, the function starts working out if the exam mark is pass or fail.
Example 4 – Nested IF functions and multiple options
In the example above, we used two IF functions together. This is called nesting and this can be extremely powerful and allow you to check the multiple situations, not just the basic is one thing true or false. The above example provided us with 3 possible outcomes:
1) Cell B19 was blank -> Make C19 blank
2) Cell B19 wasn’t blank -> Was C19 more than or equal to 60%? -> Yes, so write “PASS”
3) Cell B19 wasn’t blank -> Was C19 more than or equal to 60%? -> No, so write “FAIL”
In this example, let’s look at a company where they offer different discount rates to their customers, depending on the quantity they buy. Here’s the table summarising the discounts:
So, up to 99 products you get a 2% discount, up to 499 you get a 5% discount, and so on.
So, we want to create a formula which will check if they have bought 1000 or more, if so, apply the 10% discount, if not check if they have bought 500 or more, if so, apply the 7 % discount, etc.
First, we check for the largest quantity: Is the quantity in cell F11 more than or equal to 1000, if it is, then put the 10% discount from cell F6.
Second, we check for next discount. If the quantity in F11 is more than or equal to 500, if so, put the 7% discount from F5.
Note, that in the first test we checked if it was 1000 or more, then IF NOT, then check to see if is 500 or more.
We continue until we get to the last possibility, which is, is it 1 or more. If it isn’t, then it must be 0 (assuming no mistakes or typos), so we end the formula with a 0, to show we would offer a discount if they didn’t buy anything!
Even though they can look complicated and sometimes be quite long, a nested IF statement always follows the same pattern:
IF(check a condition, action if TRUE, if FALSE check a condition, action if TRUE, if FALSE check a condition….until the last one…action if TRUE, action if everything is FALSE)
So, in our example, we have a customer, the quantity they bought, the price of the product. Then the discount which will be applied based on the quantity they bought, using the formula above, and finally the cost with the discount (quantity x price-discount).
Here Widgets Ltd, have a 7% discount as they bought more than 500 but less than 1000.
As before, we copy the formula down the rows and we can see the different discounts that have been applied immediately.
Example 5 – Making decisions based on words not just numbers
So far, we’ve looked at whether a figure is larger than another, but we can also set an IF function to do something if it matches a piece of text.
Here we have the same customers, but this time the company’s decided to offer certain customers an extra discount. In column J, they’ve written YES or NO to determine who gets the extra discount.
Here I want an IF statement that will add the extra discount to the total if it states “YES” in column F or to leave the total the same as it is in column I.
So, for the first customer, I write the following formula:
This checks to see if J19 is YES, and if it does, it gets the total and multiplies it by 0.95 (to work out a 5% discount), and if it doesn’t, then it just gets the original total from I19.
Below we can see in the green shaded parts, that the extra discounts have been applied.
The IF function is very powerful and can automate so many things, just as long as you create a condition (or question) that can be answered TRUE or FALSE. It will then use just pure logic to give you the answer.
When checking for various values such as in example 4, the IF formulas can get quite long, in these cases it would be better to use something like VLOOKUP, which looks up the value in the table, but that’s in a future post. For now, enjoy trying out the IF function.
eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:
- “Beginner’s Guide to Google Drive” – iBooks store / Kindle store
- “Beginner’s Guide to Google Forms” – iBooks store / Kindle store
- “Beginner’s Guide to Google Sheets” – iBooks store / Kindle store
- “Beginner’s Guide to Google Docs” – iBooks Store / Kindle store
- “Beginner’s Guide to Google Slides” – iBooks Store / Kindle store
- “Google Sheet Functions – A step-by-step guide” – iBooks Store / Kindle Store