If( ) function in Excel


Uploaded by eLearningCentralia on 14.06.2010

Transcript:
[Silence]
>>This will be discussing writing a formula with the
"if" logical function.
This is a great formula, I think you're going to love it
and potentially use frequently as you work with Excel,
very common, very good formula.
We have a very simple example we're going to begin with here.
We've got a sales department, the name of the sales person,
the amount of sales they had, and we would
like to have Excel calculate whether
or not they've earned the bonus.
Very small one, and you could visually see,
but that will also help us determine
that we've done the formula and calculations correctly.
The condition for this bonus is that their sales must be greater
than 700 dollars, and if they are,
they will get a bonus of 100 dollars.
In this case, I like to think about an "if" statement
and think about this function and what the arguments are
that are necessary to make this successful.
An "if" statement has three parts.
First, there is some test or condition that must be met,
and the requirement to this is that when you ask the test,
or give the condition, you must be able to answer yes
or no, true, or false.
That's the first part.
And then, each of the arguments are separated by a comma,
so our formula would then have a comma.
The second part is what we would do if the answer
to that question was true, or yes, comma, and the third
and last part of it would be what we would do if the answer
to that question was false or no.
And that's all there is to it.
Easy, right?
So, now that we've talked about what an "if" statement is,
let's take our example and turn it into words
that describe it as an "if" statement.
In other words, our formula would be something like,
all formula's begin with an equal sign,
so it would say equal if, open paren, sales are greater
than 700 comma 100 dollar bonus comma work harder.
And now let's translate that actually
into what the formula would look like if it is input correctly,
and we'll do that in just a moment,
but we'll take a look at it first.
It would say equal sign if, and the first thing I need
to is look for where is the cell that the first sales are listed,
and in this example, where are Jane Doe's sales.
So, if B5 is greater than 700 comma,
and we could just then put in 100 comma,
and I have to decide exactly how do I want to tell them to,
encourage them to work harder, and I actually want
to put the words in work harder, so when I'm using words
in a formula, rather than numbers,
they must be input in quote.
And so I would actually, within quotes, for the false part
of this, put in the words work harder
and then close my parenthesis off.
Remember, there's always more than one way
that you could start your formula or do the calculation.
I'm going to, just for this example; use the insert function
to help me do that, so you ought to practice using that also.
I could have simply typed it into the formula bar,
I would not necessarily have to use the insert function,
but I want to practice using it.
As before, I'm going to click where I want my answer to go.
I'm just going to use the short cut for the insert function,
rather than going to the formula's tab,
I'm going to click on the insert function here,
I could chose a category or,
because in my select a function recently used list,
the "if" statement is here, I'm simply going to chose it
from here and then say Ok.
I'm going to take the title bar of this, move it over so
that I can see my worksheet, and as we discussed before,
here are the boxes that list those three argument necessary
for the "if" statement.
So, the first thing is my logical test.
I like to rule out some of my error as a human, so I'm going
to actually go out to my spreadsheet, click on B5,
which is where my first salesperson's numbers are,
I don't have anywhere I can click on the greater than sign,
so that's something I'm going to have to type
in using the keyboard, and then I'm going to say 700.
Notice immediately the function arguments tell me that it will,
for this person, find the answer is false.
So, instantly, I know what my answer is going to be.
Remember that if you look at the function arguments,
anything that is not listed in bold is not required, however,
I do want to put both of these other arguments in, so my value,
if true, I simply click in that next box,
my value of true is going to be 100.
I can simply type that number in, and my value of false,
I'm going to click in there.
Remember we were going to use words, and I said words had
to be inserted in quotes, so I'm going to put my quotes in
and the words I want exactly as I want them to appear and,
again, it shows me over here.
The answer to this question is going to be false,
it tells me here the formula result,
when I finish this one is going to be
that the words work harder will be inserted.
I'm going to click on Ok, and I get my answer.
I would like to, as always, make sure I'm efficiently
and effectively using Excel, so the easiest way for me
to copy the contents of this formula
into the next cells might be using auto-fill.
I know that I don't have anything in this example
that should be other than a relative formula reference,
and so I'm going to go to the little right-hand corner,
get my auto-fill handle and then drag and copy
that formula down into the others.
I'm going to click off of it, just so you can see it,
and just exactly as you should have expected,
you can see that Jane Doe and Dean Martin need
to work just a little bit harder in order to earn their bonus.
You can see this is a great formula,
and if you think your way through it,
in just those three easy steps,
I think you'll find it easy to use.
Later, when we get more complicated, you can do things
like nested formulas where you can set multiple conditions up,
but for now, here's our "if" statement.