In this video we will look at how to use the Excel IF Function in 4 scenarios right after this.
Hello, I'm Tony from Ready Steady Excel. I make videos helping you to get more out of Excel.
Excel is more than just a calculator. It's full of Formulas and Functions helping you to get more out of your spreadsheets.
One of my favorite functions is the Excel IF Function.
I have saved so much time over the years by avoiding manual tasks, manual inputs and manual calculations by using Excel IF Functions.
Think of the Excel IF Function as a Yes/No question.
If the answer is Yes, what do you want to happen?
If the answer is No, then what is the alternative?
In this video today, I've got four Excel IF Function scenarios.
we'll look at a simple Excel IF Function but then look at adding multiple functions and we're end with a Nested IF.
This is when one Excel IF Function is not enough.
Are you ready for the first example?
Are you ready to Excel?
Let's Go!! Hear at The Dunder Mifflin paper company salespeople who have passed their trial are entitled to commission.
They get 5% off their Sales.
If they haven't passed their trial, they get zero commission. So there's two possible results.
We need to use the Excel IF Function
Now I could type in the Excel IF Function simply into the cell.
I could just type it out in full, but I always find it's easier to work with a dialog box for the Excel Function itself because it's just easier to to break it.
It's makes it easier later, especially if you're not too confident with the functions or formulas or if you're new to it.
So let's have a look at that one
I click here up on the formulas tab.
I can go to the insert function to search for it. But an easier way is just to go to Logical category.
Here you have all your logical functions, and I'm going to go to IF.
I got my dialog box here, the first one is the Logical Test so what's my condition? What am I looking for?
I'm looking to see if this cell
Is equal to yes, because that tells us if they've passed their trial.
If that statement is true and they have passed their trial what do I want to happen?
I want to give them Commission based on their Sales so for that it's this number here.
multiplied by the Commission
The Commission percent. I always like to put the percentage into a separate cell. It just makes it easier to adapt later.
So if I decided that I want to give 7% commission then I just simply change that cell B3 rather than changing the formula.
It just helps to keep your spreadsheet nice and flexible
and because it's always going to be in B3 I'm going to lock it in.
I just press F4 on the keyboard and that surrounds B3 in dollars. So it always will be B3
It just makes it easier when I come to copy the formula
If they haven't passed their trial they then get zero Commission
So that's all done. click OK
Let's look at the results. Dwight Schrute is entitled to £750 and that's because he's passed his trial.
He gets five percent of his Sales.
I double click here to copy it down
Pam Beesly got zero commission because she hasn't passed her trial yet.
I mentioned earlier about changing the percent. So if I decided, actually they're entitled to 7% now
It just updates it nice and easy
Updates the formulas automatically.
Okay on to the next one. So in this example
they will get Commission if they have passed the trial and their Sales are more than the target
I've got a target column there.
I want to check if their Sales are more than or equal to
Their target and they need to have passed their trial. So let's have a look at that one
I go to Logical category
and
I'm gonna go to IF
Now typically as it stands Logical Test only looks at one thing at a time, only looks at one condition.
If you want to do multiple conditions or criteria, you need to add another function.
So for this example because I need two things to be true
AND is the function I need.
I could type in the Excel AND Function here. That would work. I could type it into that field.
But again, I prefer to work with a dialog box here.
Up towards the top left
Click on the drop-down and it's on this list because I've used it recently but if it wasn't on that list
I just simply go to more functions to search for it
I'm going to select the AND function
And as you can see, the dialog box has changed. It's just bringing up logical one and logical two
So these are my conditions
Now don't worry. The IF function is still in the background
If you look there at the formula bar, we still have the IF Function there at the back in the formula bar.
I'm going to come back to that one in a second
So this is my AND Function. This dialog box allows me to have up to 255 conditions which is more than you would ever need. I would hope so!
What I do here in logical one? I'm just checking to see if this one is equal to yes
And I also need to check if the cells are more than the target.
Okay, so that's my two conditions. So those two conditions need to be met
They need to be true before that person can get commission.
Now I need to get back to the IF Function. To get back to the IF Function I just simply click up here on the IF.
And it takes me back to the IF Function. Like I said you could have just typed in the And Function into the into the logical test and that would work.
I always prefer to go to a dialog box. It just makes it easier to set it out.
Helps me to separate it in my own little brain.
So if that statements true
What do I want to happen? I want to give them Commission. So again the Commission is this number.
Multiplied by the percent. I will lock B3 in by pressing F4.
if they haven't met that condition, it's false and they get zero commission. Click OK
Gives me my answer and I double click to Copy it down.
So again
Dwight Schrute has got the commission because he's passed his trial and
His Sales are equal to or more than his target
Let's check another one further down.
Meredith she's passed her trial and the Sales are more than the target
Again, she's entitled to commission. Whereas the other ones here with the zeros
They might have passed their trial but their Sales are below the target
Let's look at a variation of this one
For this example, we want to check if they've worked at the firm for more than 60 months
More than 5 years or if their Sales are more than the target
So again, I'm going to go to the logical category. I'm going to go to the Excel IF Function
Remember, typically the logical test and looks at one condition at a time, but you can add another function.
so for this function we need to add the Excel OR Function
okay, so either condition needs to be met for them to be able to get a commission
Again, I could just type it into this field
but I'm going to bring it in by going up to here and then go into OR
It's a similar dialog box that we had before but this one's for the OR Function. So remember either condition needs to be met
Before the person can get the commission. So let's try this one. So logical one
I want to check if this number here is more than, I'm gonna say equal to 60 months
OR the Sales are more than their target, I want to get back to my Excel IF Function. Where do I click?
I click here on IF
It takes me back. As I said before I could just type in the logical test
I could type in the OR Function straight in there and this would work.
So either of these statements need to be true and if it is true if they are then entitled to the commission. So it's this number
Multiplied by this
Otherwise they get zero. Click OK.
Double click to copy it down. So that's the all function.
So for the last scenario we're going to look at the target status.
So what I want to do for this one
I want to show in text, whether they're Above their target, Below their target or On target.
This can make it easier when I'm using things like Excel Autofilters
So perhaps I can filter the data out. Check who's below the target so I can chase them up if needed
Let's have a look at this one
I'm going to look at the IF Function. I go to Logical and I select the IF Function.
Remember before we had two possible results
So for this example, we've got three possible results..Above, Below or On Target.
We need to use something called a Nested IF Function.
Now you can have multiple IFs within this function here
but there is an easier way so you could have IF after IF after IF...
But there's a better way, a quicker way!
There's something called IFs.
Which not a lot of people know about, it's been here for a while. I go for IFs, I click on that one.
It works in a similar way to the IF Fucntion, in that we have a logical test one.
What am I checking for? I'm checking to see if this number here, if more than
this number 0:11:43.999,0:0:11:52.209 if it's true, I want to say that it's Above Target
Then I go to my logical test 2. For this one, I want to check if this number is less than this one.
If it is..
...it's Below Target
For my final one, if this number is equal to the target
Then
The answer is going to be On Target.
I find it's a lot easier than doing the traditional
Nested IF Function. I use IFs instead, it's a lot lot easier. I click OK to this one.
There's my answer. This person is on target
Sales are equal to the target and let's fill this one down. Double-click
I can easily identify
Who's Above, Below or On their target. I can filter this data and then chase up the people if needed.
Now you might notice that I've got some colors there; Red, Orange and Green.
I've used the conditional formatting and I'm going to show you how to do that one in a separate video.
So there we have it
The Excel IF Function
4 Straightforward scenarios, which helps you to get a lot more out of your spreadsheets and can save you a lot of time as well.
So you're not having to do things manually.
So, how was that tutorial for you? Did you find it useful?
If you have any questions with your spreadsheets
If you need help with any Excel IF Functions that you might have in mind
Then please pop your questions into the comments and I'll be happy to answer and get back to you.
If you want to see more videos like this, please click Subscribe and I hope to see you again soon.
Bye for now.