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.

>>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.