hi guys today we're going to do a nested

if function meaning if function within

an if function okay and I've set up an

example that I've come across before

that I think is a really good one for

starting out understanding when you

would need to use such a function such a

nested function and also how to do it

and it's also not too complicated so as

to turn you off to pursuing further

levels of complication okay so here we

have a bunch of flights these are just

the flight numbers I made up these are

took the total number of seats on each

one of these flights and there's either

a 210 seats 350 seats 150 or 190 so I

just went off the presumption that

there's four types of aircraft and these

are the four capacities okay here are

the seats sold so as we know sometimes

airlines oversell seats and they have

all different kinds of ways of dealing

with things like that but here we're

going on the assumption that they can

over sell a flight and obviously they

can under sell a flight like in this

case they sold less seats than the

number of seats on the plane and also

they can fully booked a flight which is

a case like this where there is 210

seats and there was also 210 seats sold

okay and basically what we want in this

column is the status of the flight so we

and here I just put a little note on the

side here and these are the three

statuses

our stat I that will consider the flight

is full the flight is overbooked meaning

we sold more seats than there is there's

space available the space available will

be like the first example here there is

210 seats and we only sold 195 ok so

there's three possibilities for each one

of these flights and as we know because

you know we're if you if you're new to

it functions maybe you should go and

watch my earlier a function tutorial in

on my channel as a primer for this but

as we know an if function has three

parts one is the test and the second

third part are return or the values

return if the test is true and if the

test is false so it could give you back

two things right either or whereas here

we have three possibilities so here

you're going to use an nested-if

functions okay

so let's get started since we're all

pretty familiar with it functions we

know we start with equals if and then

the first argument is the logical test

so arbitrarily I'm going to choose to

make the first test whether the flight

is full so treating each row

independently let's deal with Row 1 does

how can we test whether the flight is

full basically that means if cell b3

equals cell c3 right because if they're

equal then the flight is full

so that's the first test , what do we

want Excel to return if the flight is

full or in other words if this test this

first test is true well obviously we

want it to return quote in quotes flight

oh right comma what do we want Excel to

return if b3 does not equal c3 or in

other words if this tests fails and what

we want it to return if this test fails

is either of two things either it's

overbooked or their space available so

we can't just write overbooked here

because there's also the possibility

that their space available so this is

where a nested if function will come in

so basically what's going on here is if

this test is true then we get within

excel will return flight full and we're

done

comma if this test is false we will

continue with another if function

another test so we type if here so it'll

only get this far to the second if if

this test fails because if it's true we

end with flight full and Excel doesn't

continue any further on this function

okay so now if this test fails now

what's another thing we want to check

our test obviously let's just take it in

the next order we want to test if it's

overbooked and how do we test if this

flight is overbooked we can see if the

total seats is less than

the seat salt because if the total seats

is less than that seats sold then we've

overbooked the flight right and if this

is true of course this will be over both

right and then if we think about if

there's only one other possibility and

that is that space is available and

space is available will occur when total

seats is greater than the seats sold but

we don't have to test that because by

default that's the only thing left in

other words if this test fails and

they're not full then Excel will go to

this test and if this test fails and

it's not overbooked and there's only one

other thing it can be and there we can

just type space available in quotes and

we'd close the parentheses on the

nested-if function and then close the

parentheses on the larger if function

that's containing is nested if function

okay and remember the open parenthesis

have to equal the closed parentheses and

this becomes a something that gets quite

annoying when you have five or six if

functions nested within each other but

over here it's clear there's two open

and two closed okay so let's just review

this function again before we hit enter

so this is what's going on first the if

function is testing whether B 3 equals C

3 if they equal each other then the

flight is full and we're done

Excel will stop right there and chop it

off right there

but if it's both be three doesn't equal

C three then Excel will continue and it

will go into another if function and

this time it will test if B 3 is less

than C 3 and that would mean that the

flight is overbooked and it'll return

over overbooked and it would finish

there if that was the case but if this

fails and this fails then there's only

one thing left and that is a final

status which is space available now we

hit enter and in this first case before

we hit enter I always like to take a

little guess of what it's going to be

and obviously in this case there's going

to be space available so let's see if we

return is the right thing and voila it

does and just like all functions if it's

done correctly we can drag it out let's

drag this all the way down and go

through a couple rows to see that it

works properly ok the second row there's

210 seats and we sold more seats than we

had so it's overbooked makes sense third

flight there's 350 seats and we sold 312

so there's space available fourth there

is 150 seats and we sold too many so we

overbooked its flight 140 here we sold

200 there were 210 seats and we sold

exactly 210 so the flight is full and so

on all the way on down to flight 347

there's a hundred and fifty seats

available and we only sold 112 so

there's space available ok so I hope

this was a good introduction to

nested-if functions here we have two it

functions one within the other and I

hope it was a good illustration of when

this would be useful why you would use

it

and also the logic of building it I

would take my time to build the function

slowly and do it right once to check

that it's correct and then the time that

it saves by being able to copy it all

the way on down all your rows will pay

off if you do the first function with

care okay

subscribe to my channel check out my

other excel tutorial videos comment let

me know if there's any other topics you

want me to cover I'd be happy to

until next time practice practice

practice and have a great day bye