Welcome to Office 2013 class video number 44.
If you want to download this workbook and follow along,
click on the link below the video
or go to our class website.
Hey, we're still studying Excel.
This is Excel basics 26, and we want
to talk about the If function.
Now lots of people think the If function is hard.
But guess what?
It's not.
It's very simple.
The If function is for putting one of two things in a cell.
Now we're going to start with a bonus example.
And here's you.
You sold 65,000 for the period.
And your contract says if you sell more than 50,000,
you get 500 extra dollars as a bonus.
So check this out.
If this is the cell in your payroll spreadsheet
where they put your bonus, right?
There's only two numbers that can go here.
65,000 since you're bigger than the hurdle, you get 500.
Notice, what do we put in this cell?
500.
Now what a bummer.
You only sold 10,000.
Well that's not bigger than 50,000, so you get zero.
Next period, you sell 75,000.
Oh, well here's the cell where you either
get your bonus or not.
So now you get $500 Next period, you sell $1.00.
Well, now you get a zero.
That's it.
That's what the If functional will do for us.
If you have a cell in your spreadsheet
that's only going to get one of two things.
And the two things here are zero or 500.
Then forget doing it manually.
You want to do with the If function.
Now I'm going to put 65,000.
We'll start back right where we started and delete this.
Now before we can do the If function,
we have to remind ourselves what a logical formula is.
Now earlier in the class, we saw a logical formula.
Well, all a logical formula is is
something that tells you true or false.
Did you exceed or equal the hurdle?
Notice I said, your sales, did they
exceed, that means bigger, or is equal to,
because you get your bonus if you're exactly 50,000.
So really you could say it this way.
Is 65,000 greater than or equal to 50,000?
Well, what's the answer?
It's true.
What if I change this to $1.
Is $1 greater than or equal to $50,000?
False.
Well that's an example of a logical test.
Now we don't ever want to have to do it by hand.
We want formulas to do it for us.
So watch this.
What's the character that tells Excel
we're going to do a formula?
It's the equal sign.
But notice it's the equal sign as the very first character
in the cell.
So now I ask the question, are your sales--
and now we have to use a comparative operator.
Here's a list of the comparative operators.
We've looked at this a few times in our class-- greater than
or equal to.
There's no single character, so you have to type greater than
or equal.
Two characters.
Then you click on the bonus.
That's a question, a logical test.
Are your sales greater than or equal to the bonus?
Well, of course, we're going to get false.
But as soon as we change it to 65,000-- boom, true.
Now any time you have a test that
has an operator and an equal sign, what are we going to do?
We're going to check to see if our logical formula is working.
Below, I'm just going to give it one buck.
Whoa, it gave me a false.
Way above, great, we had 75,000.
And you have to check exactly right on.
Remember, your contract says if you sell exactly 50,000, that
means you equaled the hurdle.
You get the bonus.
I love it.
True means I get-- or, no these are
your sales-- you get the $500.
All right, so that's a logical formula.
Why did we do this before doing the If?
Because the If function uses a logical formula.
So now, let's come down to this cell.
Here's the big moment.
The If function equals If, and look at this,
there's three arguments.
Logical test, that's our logical formula.
And whatever you put there's got to come out true or false.
And then the rest of it is hey value
of true, what do you want me to put in the cell if it's true.
And then value of false, that's what
you want me to put in the cell if this comes out false.
So our logical test, are your sale
greater than or equal to the hurdle?
If that's true we type a comma.
And the value of true, well what do we want?
$500.
Otherwise, we type a comma, what's the value of false?
Since it's always going to be zero, we just type it in.
Notice that's the thing that goes into the cell
if that logical test comes out false.
That's the thing that goes into the cell
if this comes out true.
Remember the If puts one of two things
in the cell, $500 or zero.
Now I'm going to close parentheses, and here we go.
Control, Enter, $500 I love it.
Now let's test this.
Aw, I only sold one.
Or even worse $49,999.99.
Oh, this would be a bummer.
One penny shy.
But no problem.
They rounded it up to the dollar, so it's $50,000, boom,
we got the bonus.
$100,000 or whatever number's bigger, we got the bonus.
The If function, putting one of two things in the cell.
Now let's look at another example.
Over here, we have debits and credits.
And they always have to be in balance.
But hey, we're human.
I see number formatting.
Maybe there's a problem.
Guess what?
Formulas won't get tricked by this number formatting.
So any time you have numbers that
have to be in balance, a logical formula or an
If function off to the side to verify that everything's
in balance is a good idea.
So let's just say equals, hey, that
tells Excel I'm doing a formula.
Is that cell equal to-- and that's a comparative operator--
this cell?
The first equals sign says, I'm doing a formula.
The second one is a comparative operator saying, are you equal?
Control, Enter, false.
Now we've gone over this a lot in this class.
Immediately, when we see this, we have to investigate.
We probably know by now that the eraser for number formatting,
general number formatting.
And sure enough, boom, there's an extra penny somewhere.
So as soon as your formula says false, you go and investigate.
Now I'm going to Control Z. Sometimes
we don't want false, right?
We want to say in balance or not in balance.
So this is an example of a cell that's
going to get one of two text strings or phrases,
either in balance or not in balance.
I'm going to use the If function.
The logical test, something that comes out true or false,
are you equal to this other cell?
If that's true, then I want-- now we have to remember,
if we're going to type in balance,
what is it that we have to do to text in formulas?
Put it in double quotes.
The formula will not recognize in balance
unless you put it in double quotes.
That's what we want in the cell if it's true.
If it's false, which means we have some trouble, right?
Not in balance, or whatever you want.
Maybe you'd put debits don't equal credits.
Now notice I spelled something wrong.
If I Control, Enter, right now, if I
were to highlight these cells and hit
F7, the keyboard shortcut for spell check,
notice it says spell check is complete.
You're good to go.
That's because once you put text into a formula and enter it,
spell check won't find it.
But if I'm in edit mode.
If I hit F7, it can find it.
Now I'm going to be careful here.
This is trying to correct the spaces and everything.
For example, right there there's an extra space.
But now I see how to spell it.
So I'm going to click Escape, and I'm going to change it.
Control, Enter.
So not in balance?
As soon as I track down the error
and change it to a zero, boom, it
tells me true for the logical formula and in balance for the
If function.
One of two things, in balance phrase, not in balance phrase.
One of two things, $500 or zero.
Now let's go over to this other sheet.
If two.
We want to see similar examples.
But we have to copy them down the column.
So this is our payroll sheet, right?
We want to know, did each employee
surpass the $20,000 hurdle?
So I want a logical formula in this column.
Equal sign, are your sales greater than
or equal to the hurdle?
And I have to lock this with the F4 key.
Control, Enter, double click and send it down.
Go to the last cell and hit F2.
That's working perfect.
Now I want yes or no.
So here we want one of two words.
So equals If and the logical test.
Well, it's going to be the same logical test,
are your sales greater than or equal to?
Now whether or not it's equal or not
depends on the language in your contract.
If it says greater than, then it's the greater than symbol.
We want equal.
We have a nice boss, so if we make exactly $20,000,
we get the bonus.
If our sales are greater than or equal to our hurdle,
F4 to lock it, then what do we want?
In double quotes, yes.
That's the value of true.
That's the first possible thing that can go into the cell.
Comma.
Otherwise, the value of false, no.
This is the second thing that can go into the cell.
One of two words.
Double click and send it down.
Click in the last cell F2.
It's working perfect, we have yes, no.
Now what about the bonus amount?
This is a practical example.
Many payroll spreadsheets around the world
have formulas like this.
If the employee sales, are they greater than
or equal to whatever the hurdle is in B9 locked.
I'm going to hit the F4 key.
The value if true, whatever's in cell B10.
And I'm going to hit F4 to lock it.
Comma.
And the value of false?
We're typing in zero.
Close parentheses, Control, Enter.
Double click and send it down.
Go to the last one and F2.
The If function saves so much time.
What if you had hundreds of employees?
You would not want to go and eye this.
Lots of mistakes that way.
Plus that would be slow.
If function, that's fast.
So anytime you have one of two things, the
If function can come to your rescue.
Now there's some other things that we
can do with the If function.
And after you finish Business 216
and you go on to high line Excel class Business 214,
we'll talk about other things the If function can do.
If you can't wait, Excel Magic Trick 452 has 16 examples.
Some amazing things that the If function can do.
All right, this is our second to last, our penultimate,
Excel basic videos.
Our next video, which will be our last video for Excel,
will be about the look up.
All right, see you next video.