0:13

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 actually overpaid, so

Â they have a negative amount on their balance.

Â Now the problem here is we don't just have two possible out comes, we have three.

Â 0:51

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 test, 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 answers quite simple, another IF.

Â So what we would do is we would check first, is our balance equal to 0?

Â 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, is our balance less than 0?

Â If it is, we give them a category of 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 Ctrl+.

Â If you're using the + at the top on your keyboard, make sure you shift as well, and

Â then we're just going to pop Category into the title.

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

Â So = if, then open our brackets, the first logical test is balance = to 0.

Â If it is, we're going to say, that you're 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 they're category C.

Â Close your brackets from the second if,

Â close your brackets from the first if, and click answer.

Â And now, when I double-click copy down, you'll see all my parents with 0 have A's.

Â My parents with an outstanding balance have B's and

Â this parent who somehow overpaid is category C.

Â 3:16

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 5% off.

Â But if there are three or more enrolled,

Â because it's much more expensive, we're actually going to give them 80% off.

Â So let's start this from the beginning.

Â We're going to say =if, and open our brackets.

Â Our first check is, is our number of students enrolled = to 1?

Â If it is, there's no discount, so we just type a 0.

Â 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 = to 2?

Â If they are, we're going to give them the 5% discount.

Â So that's the calculated fee times 5%.

Â Otherwise, it must be more than 2.

Â So we're going to do the calculated fee times 8%.

Â Again, close your brackets from your second if,

Â close your brackets from your first if, click enter.

Â And let's just simply copy that down, and

Â we've now recalculated the sibling discount.

Â You'll see siblings where there's only 2 that still only getting the 5% discount.

Â But where there's 3 or more, they're getting the 8% discount.

Â So we've now seen how we can make our ifs even more powerful.

Â You can actually nest your ifs up to 64 levels deep.

Â I hope you never have to write that formula.

Â And in fact, once you find, you're getting past 5 or

Â 6 levels deep, there are probably better solutions.

Â But where you have 3 or 4 outcomes, nesting ifs can be extremely useful.

Â Make sure you get used to this,

Â because it's something that comes up in business a fair amount.

Â [SOUND]

Â