How to nest if then statements in excel
Excel Nested IF function
Timecodes:
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
Related queries:
nested if statement excel
nested if then statements in excel vba
how do i create multiple if then statements in excel
multiple if then statements in excel with text
multiple if then statements in excel vba
how to nest multiple if and statements in excel
