All language subtitles for 4. The IFS Function

In the last lesson, I showed you how you can create a rather long nested if statement in order to perform

multiple logical tests.

And what we ended up with was a pretty long formula.

Now I did mention that in recent versions of Excel, there's been a new formula added called IFF s,

which makes constructing nested if statements a little bit more efficient.

And I emphasize the word a little bit.

It really just means you don't have to keep typing in if each time you can go straight into the logical

statement.

So it is a little bit more efficient, but don't be expecting it to drastically decrease the amount

of time you spend constructing this formula.

So let me show you a couple of different examples.

Now we're going to do the same example again, just so you can see how it compares to the nested if

statement on familiar data.

So if I want to do basically the same thing as what we did in the last lesson and output the bonus based

on the job rating, instead of doing all of those if statements, I could use the F.

S function.

I'm going to press the tab key to select it from the list.

Logical test one.

So we're doing the same thing here.

So if the job rating in G four is equal to five f four to lock that cell, if that is true, we want

it to return a bonus of three thousand f four to lock the cell.

Now, in the old nested f way of doing this, we would then need to go in and start typing in our if

statement.

Now, with the F.

S function, we don't have to do that.

We can literally go straight into logical test number two.

So we don't have to type in F.

We can simply say f g four is equal to four at four o'clock, then they're going to get a bonus of fifteen

hundred.

We don't need the f statement again.

We can say f g four is equal to three, then they're going to get a bonus of 900 so on and so forth.

I also don't need a false argument here, so I can literally carry on going through.

I'm going to stop there because I think you've got the idea.

I simply need to close my bracket if I do a control answer and then copy down.

Notice that for the ones that I've said this for, so job rating five, four and three, I have values

in there.

This time I have an A because I have an assigned job ratings one and two to anything.

So Excel doesn't know what to do here.

So that is why I have an RN error.

And towards the end of this section, I'm going to show you how you can deal with things like any errors

in your worksheet.

I make those more meaningful as well, but hopefully you get the idea as to how F.

S works is basically the same as doing nested f statements.

Except you don't need to keep typing if into the formula and you don't need to provide a false result

on the end.

Let's take a look at another example of how we can use if s.

Now here we're going to construct a basic if s formula that's going to return the day of the week when

a day number is typed in.

So the way that I see this is that Sunday is day number one.

Monday is day two, Tuesday, day three, so on and so forth.

And I want people to be able to come down here and enter in number four and have it return the day from

this list above here.

So we can do this using if s so we can say equals if!

S So if the value in this cell, which is currently empty at the moment, is equal to one.

Now I am going to hardcoded this in here.

But as I said, you may want to list one to seven out somewhere in your spreadsheet so you can use that

cell reference.

But for the time being, let's just leave it on one.

So if the number in A11 is equal to one, if that's true, I want it to return the results of Sunday

comma straight into logical test.

If the value in A11 is equal to two, it's going to return a result of Monday.

I'm going to carry on going.

So if A11 is equal to three, we're going to get a result of Tuesday.

So let's just carry on going through all of these.

If A11 is equal to four, then we're going to get Wednesday.

A11 is equal to five.

We're going to get Thursday.

If A11 is equal to six, then we're going to get Friday.

And then finally, if A11 is equal to seven, we're going to get Saturday close off the if s and hit

enter now.

Currently, we have an enemy error in here because.

We don't have anything in cell A-11, and this formula consistently refers to that cell looking for

a number.

But now if I add, let's say two.

It's going to give me my result.

Let's add seven and it changes if I add something outside of the criteria that I specified, I'm going

to get an NRA.

Now, my be again, that you want to add in some error checking so that if somebody types in a number

outside of one to seven instead of just getting.

You want it to say to them, please type a number between one and seven.

And we're going to explore how we would do something like that towards the end of this section when

we look at error checking.

But those are a couple of different examples of how you can use if s.

And hopefully that illustrates quite nicely the differences between nested ifs and the effects formula.