INSTRUCTOR: Hello.Now, in this lesson, we're going to begin to talkabout the IF function in Excel.The IF function is basically usedto have a conditional statement, that you can basicallychoose to put something in a cell basedon the contents of another cell.It sounds kind of complicated.But basically, this is kind of, in words,
INSTRUCTOR [continued]: what an IF function does.I mean, we all know what IF functions are,but this is kind of how humans read them.Standard shipping on an order is $7.99.Orders over $100 get free shipping.We all go to websites where we see stuff like this.What this means is, if your order is greater than $100,then shipping is $0.
INSTRUCTOR [continued]: Otherwise, shipping is $7.99.That is what the IF statement is when you translatethis kind of wording into that phraseology in termsof an IF statement.Now, in this case, I have a little table going on here,and I'm going to format all of these as numbers.Let me go down here to Format Cells,and then I will select Accounting, two decimal places,
INSTRUCTOR [continued]: dollar sign.So now we're dealing with money.And Hewlett-Packard bought $50 of merchandise from us,Intel bought exactly $100, Microsoft bought $101,and Apple bought $200 of material from us.So basically, we want--I can type this in.We know, from Hewlett-Packard, you can read this,
INSTRUCTOR [continued]: they should get basically shipping at $7.99.And Intel, it's right on the edge.It says, orders over $100.That means one penny over $100 gets free shipping.So actually, Intel doesn't get free shipping.Microsoft is just over the threshold,so they should get $0 shipping.Apple should get $0 shipping.We can type in.But if I had 5,000 customers here,
INSTRUCTOR [continued]: I surely would not want to be typing it in,so we're going to use an IF statement.So what you do is you put an equal sign,and then you type IF.That's basically what it is.You can see that Excel is alreadysuspecting that you want to use the IF statement,and it tells you.It checks whether a condition is met and returns a value of trueand another different value if it's false.
INSTRUCTOR [continued]: So it's either/or.Either the statement's true or it's not.And depending on if it's true or notis what we'll basically put in the field here, in the cell.So since we're going to use IF, I'm going to hit Tab.Once you get this little dialog box here,you can press Tab when it's highlighted,and that will put it in there.And now, it puts the capital IF there with an open parentheses,and it's trying to give us a hint
INSTRUCTOR [continued]: and tell us what we're going to haveto type in to this function.The first thing we have to type in is the logical test.That's what we're testing.That's going to basically figure out,and we're going to tell Excel to look and seeif the order total is actually greater than $100 or not.If that statement is true, then we'regoing to give them $0 shipping.If that statement is false, which means less than $100,
INSTRUCTOR [continued]: then we're going to put $7.99 in this field.So what we're going to do is we're going to test.In this case, since we have the cell here,we're going to test this cell, and it'sgoing to put in B5 there.You could type it, but we can just as easily click.We're going to test, is B5 greater than 100?
INSTRUCTOR [continued]: And when we use the mathematical sign greater than, like this,it means 101 tiny little decimal higher than that.In other words, the value of 100 exactlyis not greater than this.That would be equal to.So it has to actually be greater than $100in order to evaluate as true.So we're going to do that.
INSTRUCTOR [continued]: Notice, we've put the logical test in place.So now we put a comma, because that'swhat's in this little hint for us here.And then it highlights and tells usthat we need to put the value in if this is true.So if this price here or this order totalis greater than $100, according to our little textwe should get free shipping.So I'm going to put a 0.That means, if this evaluates to true,
INSTRUCTOR [continued]: a 0 is going to drop into the cell.Now, if it's false, we have a fixed shipping cost hereof $7.99.So let me close the parentheses.And this is your IF statement.If the cell is greater than $100, then put 0 in the cellhere, C5.And if not, then put $7.99.It's either going to be true or not.
INSTRUCTOR [continued]: There are no other possible--there's no other possible way youcould evaluate this guy other than being true or not.Let's go and hit Enter.And in this case, they're charged$7.99 shipping, because this, obviously, isless than the $100 threshold.Now, for the grand total--this is a straight calculation-- we'regoing to create a formula where we're going to take the order
INSTRUCTOR [continued]: total, and we're going to add to it the value of the shippingthat we have just calculated.And we're going to hit Enter.And of course, Hewlett-Packard will thenbe charged $57.99 for that order.Now, the nice thing about Excel-- we've said many,many times--once you have a formula in place,in this case it's just looking at the left-hand column
INSTRUCTOR [continued]: right there, it knows the relationshipbetween these two cells.I can then copy--I can Copy-Paste, or I can use the autofill to drag down here.Let's see what the shipping costs do.Now, in this case, we see up herethat $7.99 is what we're charged here,because this is actually less than--it's not greater than $100.
INSTRUCTOR [continued]: This is actually not greater than $100either, so we're still charged $7.99.This is greater than $100, and this is greater than $100.And it kind of puts a dash there.And this comes about because sometimes whenyou have accounting format, which is what I've chosen here,then you get dashes to indicate zeros, basically.So in order to fix that, we can highlight all these cells,right-click.Go to Format Cells.
INSTRUCTOR [continued]: And instead of choosing Accounting,we'll just choose Currency, just to make it easy.Still going to be dollar sign, but it's just goingto look a little bit different.So now we see the dollar sign, 0, 0 there.So this makes sense that we have $7.99 shippingappear for these two and zero shipping for these two.Then we can then take this formula that we've--basically sum these guys together,
INSTRUCTOR [continued]: and we can copy it down.And we can see, again, that Microsoft is notcharged shipping and so on.So that is basically a simple, simple exampleof how you would use possibly an IF statement in Excel.If you have a situation where you need to,basically, have an either/or situation
INSTRUCTOR [continued]: and the dependency on that either/oris coming from the contents of a cell on this worksheet,then you could quickly write an IF statementand make that happen.It's much easier than going down 5,000 recordsand typing in the shipping.It's easier just to copy a formula thatwould then do that for you.Now, the one thing I want to point out before I leave hereis the relationship that you can put into an IF.
INSTRUCTOR [continued]: In this case, we use greater than,but you can also use less than.We could have written this terms of--we could have said, is this number less than 100?We could have done it that way.But maybe, in some cases, you might want to ask,is it greater than or equal to 100?So that's written as greater than with an equal signdirectly afterwards.
INSTRUCTOR [continued]: Or you could say less than or equal to.Less than or equal to, the mathematical definition of lessthan or equal to, you might use that.Or, of course, you could use equal.We may have many, many times whenwe're doing a logical comparison,checking to see if a cell is equal specificallyto something.And if it is, then we can go and use the value there.
INSTRUCTOR [continued]: So IF statement.Very, very powerful.If cell greater than a number, then you have a true statement,then you have a false statement.It's a very quick way to do conditional checking in Excel.You can do lots and lots and lots of things with IF.We've just touched the surface hereto get you comfortable with it.I encourage you to play around with itand get some experience for yourself
INSTRUCTOR [continued]: to use this powerful function.
Series Name: Mastering Microsoft Excel: Essential Skills
Publication Year: 2013
Methods: Practical skills
Segment Num.: 1
Jason Gibson explains how to write and use an IF formula in Excel.
Looks like you do not have access to this content.
Jason Gibson explains how to write and use an IF formula in Excel.