Right now you’re probably wondering what is an Excel IF Statement? Why should I be interested in them? Well, let me tell you that they are one of the most used functions in MS Excel.
Hopefully you’ve been reading my posts for a while now, but if you haven’t and are a bit confused about ‘functions’ have a read of Formulas and Functions – What you need to know, and then come back to this post.
What’s an IF Statement?
This is a great first question.
So with this function, you can ask Excel to look at some data, and depending on what it is, do one of two things.
Note: Before you ask, while the standard function has two outcomes, you can be a bit more clever and add more options – but that’s for another post. 😀
Why should I use this function?
Oh, there’s so many reasons why, here’s a few that I can immediately think of:
- Check stock levels
- Is a bonus relevant (affiliate marketing?)
- Have targets been met
- Has a step/condition been met, are you ready for the next process
- Basically any questions that has the answer of true or false
Are you sold yet?
How to use the Excel IF Statement
The function is looks like this:
=IF(logical_test, value_if_true, value_if_false)
Let’s break the three sections down…
Logical_Test
Let’s start at the beginning.
It’s called a logical test, which is simply saying that the answer has got to be either true or false.
In other words you can’t have a ‘question’ where the answer is 3, or another number, or random text.
When you start to think about it, there are only a number of questions that you can ask, where the answer is true or false:
- equal to something
- not equal to something
- greater than something
- greater than or equal to something
- less than something
- less than or equal to something
Equal to/Not equal to
Probably the easiest option, as your question can be equal to a number or text.
And of course is it not equal to something – although with numbers that gives us a much larger option for no (false).
Greater than or equal to
I know that this sounds like the same thing, but there is a very subtle difference.
Let’s say the question is “greater than 50”.
Of course, we know that any number between 1-49 the answer is false.
And, any number above 51 is true.
So greater than is pretty straight forward.
But what if the answer is 50?
In this case for the question is 50 greater than 50 the answer is false. And that’s fine if what you’re looking for is 51 and higher.
But if you’re looking for 50 and higher, then you need to use ‘greater than or equal to’. That will give 50 and higher a true value.
As I said a subtle difference, but depending on what you need an important one.
Less than or equal to
If you’ve read the one above (greater than) then you already know all about the differences.
On the other hand, if you’ve skipped it (why? 😕) let me explain.
Let’s say that you are looking for anything below 100.
0 – 99 is below, and will give a true answer in this example.
101 and higher will give the answer false.
All straight forward, but what happens when the answer is 100?
As with the greater than option, you need to decide what works for you.
If it’s 100 or less, then you need to use less than or equal to, to include it.
In math terms (a calculation)
At the end of the day, we need to write this into our function/formula, so there needs to be symbols to represent it:
| Meaning | Symbol |
| Equal to | = |
| Not Equal to | <> |
| Greater than | > |
| Greater than or equal to | >= |
| Less than | < |
| Less than or equal to | <= |
Example Logical Tests
Let’s take a look at how all of this can come together to create our test:
A1=3
B2=”pdf”
D3>A1
C4>21
C3<=100
F2<=A3
OK – so these examples don’t really make much sense, but hopefully you’ve got the point that you can reference cell addresses, number or text.
Note: For text to be accepted inside a formula it needs to be in double quotes (“).
Answers
Now that we’ve got our test, it’s time to look at the outcome.
Value_If_True
First option – if the answer is true.
Value_If_False
The second option is what should happen if the result is false.
Actions
I’ve grouped these final two sections together, as they can use the same options.
It boils down to:
- Display something – number or text
- Perform a calculation (another formula or function)
To display something specific in the cell you just type it in.
Remember: If it’s text, then it needs to be in “double quotes”.
Simple Example – Display Text
Let’s start with a simple example. Here we have one thing to look at – the distance – and check if it’s more than 1000.
Note: I’m following the example as it’s written, however if you want to switch it around, you could say ‘less than 1000’. Remember that means you need to switch the true and false options around.
Logical Test = Is the number in Distance, greater than 1,000?
True = display “Long Haul”
False = display “Short Haul”
Note: In the image, the function needs to be finished with a closing bracket at the end ).
For a calculation, then it’s (almost) exactly what you’d type into a cell anyway.
The big difference is that you don’t need another equal sign (=) at the beginning.
Time to take IF up a level…
Excel IF Statement – Multiple Conditions
It’s pretty straightforward to look at just one thing, but what if (😂) you need to look at more?
Remember we can add more options into the statement.
Our next example, we need to check that the price (per person) is at a minimum level (above 150) AND that at least 2 people are travelling.
Note: These are not realistic airfares!
To compare multiple options we need to use another function.
The ones that are most often used with the IF Statement are AND / OR.
AND = everything that you include must be correct to equal true
OR = at least one of the included items must be correct to equal true.
An easy starting point is to speak the question out loud.
What did you automatically say? AND / OR
Given that it’s in the question, this example needs an AND, both things needs to be correct for the option to be applied.
=AND(logical1, logical2, logical3)
The logical bits inside the brackets are the same options as logical test above.
Looking at our example, Airfare (Col D) needs to be greater than (>) 150.
Additionally, Number of Passengers (Col E) needs to be greater than (>) 2.
Together in the AND function, makes our logical_test.
For the true statement, we have a number of choices:
- Display if they get a Discount, or 15% Discount
- Work out the amount that they are saving (i.e. the discount amount)
- Total to pay, with the discount already taken off.
Rows 6, 7 & 8 – I’ve gone with the easier option of displaying the answer.
But in row 9, I’ve chosen to go the user friendly answer. The total to pay, with the discount applied, is worked out.
In this case, the false is the easy option – it can be either the text No Discount, or 0.
Useful tip: How to show nothing
There are times that you don’t want anything to be displayed. In the above example if you don’t get a discount if can be easier to have the cell blank, highlighting the cells that do get the discount.
To display nothing, use the text option, in the double quotes leave a space – ” “.
Next Steps
Over to you – open up your fav spreadsheet and recreate one of the examples, or adjust it to your specific options.
Hopefully the IF Statement now makes a bit more sense.
If you want to take it to the next level (again), then it’s over to Nested Statements.
Related Posts
- How to choose your favourite online spreadsheet
Unsure of which online spreadsheet program to choose? Find out here the features and benefits of both Microsoft’s Excel and Google Sheets, as well as some tips on how to pick the right one for your needs. - What’s the point of the Excel Information Functions?
What are the information functions in MS Excel? How can you make use of the data they add into your spreadsheet. Read the post to learn more. - How to use Date and Time Functions in Excel
Unsure how to work with dates and times in Excel? This guide will show you how, with a variety of date and time functions explained. - VLookup in Excel: What Do You Need to Know Step by Step
Learn how to use the VLookup function step by step and you’ll find that it’s actually not as difficult as you might think. Check out this guide to some of the basics! - Excel IF Statement: What do you need to know
Unsure of how the IF statement in Excel works? This guide will walk you through all the basics, as well as some more advanced features, so that you can start using this function to its full potential.
excel if statement multiple conditions, excel if statement contains word, excel if statement
2 thoughts on “Excel IF Statement: What do you need to know”
Pingback: VLookup in Excel: What Do You Need to Know Step by Step
Pingback: How to use Date and Time Functions in Excel
Comments are closed.