So far, we have looked at how we can solve

some fairly sophisticated problems using and, if, and all.

One problem that we haven't worked a solution for

though is where you have more than two possible outcomes.

For example, looking at this data I would like

to categorize my parents into three possible outcomes.

Category A, if their balance is settled, they have zero.

Category B, if they are in arrears,

so that means they have a positive on their balance,

or category C if they are actually overpaid,

so they have a negative amount on their balance.

Now, the problem here is,

we don't just have two possible outcomes, we have three.

How do we solve the problem?

The IF function as we've seen it so far,

can't help us because the structure of the if is very rigid.

We have a logical tests what to do if true and then what to do if false.

You can't just pop a comma on the end and hope for the best.

So, how do we solve the problem?

Well, the answer's quite simple, another if.

So, what we would do is we would check first,

is our balance equal to zero?

if it is, give them a category A. if it isn't,

well, there are two possibilities.

So, in that last section in the brackets,

we pop another if,

and inside that if,

we check if the balance is greater than zero.

if it is, we give them a category B, otherwise,

we give them a category C. Let's see this in practice.

We're going to start by inserting another column.

So I'm going to click on C,

the nice little shortcut key,

we're going to press Control plus.

if you're using the plus at the top on your keyboard,

make sure you use shift as well.

Then we're just going to pop category into the title.

Now, we're going to start with our if again.

So, equals if, then open our brackets,

the first logical test is balance equal to zero.

if it is, we're going to say that you are going to be Category A. if it is not,

now we've only got one argument left.

But that's okay, we're going to put an if statement in there too.

So, we're going to type out if and open another bracket.

The logical test this time is,

is our balance greater than zero?

if it is it means they are in arrears Then,

they're going to be Category B.

Otherwise, there's only one option left then their category C. Close

your brackets from the second if close your brackets from the first if, and click Enter.

Now, when I double-click copy down,

you'll see all my parents with zero have A's,

my parents with an outstanding balance have B's and this parent who somehow

overpaid is category C. Let's

have a look at another example so you can get the hang of it.

We've also decided to make our sibling discount a little bit more fair.

if there are two students enrolled,

we're still going to give them five percent off,

but if there are three or more enrolled because it's much more expensive,

we're actually going to give them eight percent off.

So, let's start this from the beginning,

we are going to say equals if and open our brackets.

Our first check is,

is our number of students enrolled equal to one?

if it is, there's no discount so we just type a zero.

if it is not,

well there are two possibilities,

they could be two or they could be more.

So, we put another if and again open our brackets.

The logical test this time is,

are the number of students enrolled equal to two?

if they are, we're going to give them the five percent discount.

So, that's the calculated fee times five percent.

Otherwise, it must be more than two.

So, we are going to do the calculated fee times eight percent.

Again, close your brackets from your second if,

close your brackets from your first if, click enter.

Let's just quickly copy that down and we've now recalculated the sibling discount.

You'll see siblings where there's only two,

they're still only getting the five percent discount,

but where there's three or more,

they're getting the eight percent discounts.

So, we've now seen how we can make our if's even more powerful.

You can actually nest your if's up to 64 levels deep.

I hope you never have to write that formula.

In fact, once you find you're getting past five or six levels deep,

there are probably better solutions.

But where you have three or four outcomes,

nesting if's can be extremely useful.

Make sure you get used to this because it's

something that comes up in business a fair amount.