In this video we're going to learn how to use the IF formulas in Excel. And the IF formulas are bascially Excel's version of If-Then. So, we're
going to set parameters within the formulas. And, if those parameters are met, then we'll need to let Excel know what
result we want and if the parameters are not met, we'll have to specify that result as well.
And, the formulas go from your basic, simple IF to a little more complex. So,
to walk you through what those could look like, I created this imaginary sales report
that has your salesperson, what items they were selling and how many dollars they sold. Then, to figure out their commission, we'll need
to know that they need to sell a minimum $40,000 to get a 10% commission,
$50,000 for a 15% commission and then, after $50,000 of sales, they'll get a special award. Anything less than 40, they get nothing. So,
to get started, um, with the sample basic formula,
we just want to know, does this to salesperson even qualify for a commission - Yes or No.
So, to do that, we'll type =IF , excuse me, open parentheses. And we need to reference for this the Dollars Sold.
So, we'll say IF D4 is greater than or equal to
$40,000 then we'll say "Yes".
And, if not, it'll be "No". Now, notice that I'm using quotation marks. That's because this is text. If you try to type text in these formulas...
whoops! I'm missing a quotation mark here. And, um, and you don't use the quotation marks, Excel gets a little confused and you'll
get an error. So,
we already know from looking at is that the first person, Jamie, qualifies for a commission, so she'll be receiving one. Now, I just want to copy
this down. I'm going to do a Paste Special just because I don't want to ruin my formatting - and paste as formulas.
Then we want to know, did they qualify for a special award. Again, $50,000 or more worth of
sales is a special award. So we'll do another basic formula
of =IF , again referencing Dollars Sold. Whoops!
=IF , referencing Dollars Sold, is greater than or equal to $50,000.
Then, if it is, then our response will be
"Special Award"
And, if it's not, this time we just want to leave it blank. So, I'm going to type my quotation marks, a space,
another quotation mark and then I'm going to close my parentheses. And I'm going to copy this down again
and Paste Formulas, and we see that only 2 people, 2 salesperson...sales people,
Emerson and Jamie, receive a special award.
Now, you can also use the IF formula
By having it look at different things. So - AND or OR. So, in the OR formula,
you can give it certain parameters and at least one of those parameters needs to be met for your result to be, um,
to be True. And, if it's not, if none of those are met, then it's False. Um, with the AND, everything
has to be met. So, to illustrate this so you can see what I'm saying, we need to know who sold food.
Cough.
The only food items here between Shirts, Pillows, Pants, Bread and Eggs are Bread and Eggs.
So what we'll do is, we'll type =IF ,
open our parentheses and we'll type OR,
open our parentheses again, and you see how our formula changes? So now we're in the OR formula and we need to type what those
logical values are. So, we need to know IF
Items Sold, so in this case cell C4, equals to, and I'm going to put in, in quotation marks "Bread".
Then I need another logical value here for Excel to look for. So if C4 equals to "Eggs", whoops! - I need quotation marks!
Now I'm going to close this parentheses and, when I do that, this changes again and now I'm back to the IF formula. So if this is True, make
sure to have that comma here, that's True, then the answer will be "Yes".
If it's not True, the answer will be "No".
And, again, this doesn't need to be, um, case sensitive. I've typed it in lowercase - it's uppercase here
and,
as I reference this cell, when I copy the formula down, the reference will stay with it.
So, we see here that the only 2 Yeses are Buddy and Newbie. They were selling Bread and Eggs.
So, an example of the AND formula - it's pretty much the same as the OR, except all of the parameters need to be met. But, in this case,
we want to know who received a commission and an award. And I know this is already pretty much, um,
illustrated here, but I want to show you how to write the formula.
So,
to do that, to figure this out, what we'll do is we'll type =IF(AND
and then we'll open our parentheses and we'll say if cell
E4
equals 2 - "Yes"
and, then again, if cell F4 equals to "Special Award"
those are our parameters. And, if both of those are met, then
we'll type in "Hooray!"
and, if those are not met, this time we're just going to leave it blank. So I'm going to do my quotation marks,
space, quotation marks again and close your parentheses. And we'll copy this formula down
and we see that, again, Emerson and Jamie where the only ones to receive a commission and an award.
Now, sometimes you need to combine multiple IF formulas to figure out, um, your result. So, in this case, we want to know the commission
amount. The reason we need to know...we need to combine the IF formulas is because there's
multiple break points: at $40,000 you get one commission - 10%, at $50,000 they get 15%. So we need to somehow, um,
note that in Excel.
So, we're going to do =IF
and actually for the first one I'm going to do an AND, so we'll do AND.
And what we need is for cell
D4, the Dollars Sold, to be greater than or equal to $40,000.
Now, the reason I did AND is because we also need for D4 to be less than
$50,000. Right, because $50,000 is a different price...is a different, um, percent commission.
So
we close the parentheses,
then comma, then we're actually going to type IF again because now we need to know what to do at the 50,000.
So, IF(
cell D4 again,
is greater than or equal to
$50,000...
whoops! actually I missed something's so let's...let me delete this for a minute. We're just going to delete that and we're going to go back
to here. So, again, greater than 40,000, less than 50,000. We want to say D4 times
.10
Then we're going to type IF.
Because now that's going to be IF this is not True, then move on to the next IF formula. So, at that point, we go back
to D4 and now it needs to be greater than or equal to 50,000.
So, value if True will be, if it's $50,000 or more, will be D4 times 0.15.
If it's not True, then we're just going to leave it as zero.
Actually, I'm just going to type "0".
We'll close our parentheses.
and there was another parentheses which I didn't do, but Excel auto fixed it. So, we see that this first commission,
Jamie will receive $8,196 in commissions. So, I copy this down,
copy the formulas and you can see that
she did receive 15% commission.
Sasha received $4,600 in commission and so on. And, Newbie, who had less than $40,000 in sales
didn't receive any commission. So I hope you found this video, um, useful. And that this helped walk you through the IF formula a little
bit easier, helped to clarify them.
If you liked the video, please make sure to Like it and to subscribe to the channel
so that way you'll be the first to know when any new videos come out. Thanks!