How to use the IF function in Excel

How to use the IF function in Excel

today’s video is about excels if
function how can we use it alone how do
we know if we need more than one if and
how can we combine it with other logical
functions such as and or-or functions

whenever you need a formula that’s based
on a condition such as you need to mark

or flag entities separately if their
values above 200 in revenue and if can
do a good job here now the way you know
if you need a nested-if that means you
need to use another if inside an if is

when you use bots in your sentences so
let’s say we need to mark entities if
their value is above 200 but if their
value is below 50 we need to follow up

on them let’s take a look at some
examples in this example I have a list
of apps and I have the revenue that’s
associated with each app now I’ve been
asked to solve for this for the first
case I need to mark apps with the word
good if the revenue is above 15,000 so
we’re gonna start off with if the first

argument is our logical test what is our
logical test in this case is this value
right that’s our revenue and if this is

greater than 15,000 so I can either type
it in like this or if I have these
values in other cells I can reference
those cells and obviously you’re going
to be more flexible if you go with the
cell referencing route because it could
be that I change the threshold next
month to 20,000 and it’s much more
transparent and easier for me to manage

if I just have to go to one cell and
change that number instead of finding
the formula changing it in that formula
and then dragging it down and making
sure that it applies to everything else
I’m not going to type it in here I’m

actually gonna reference this cell but
when you do the cell referencing route
you have to keep
the fixing in mind right and that I can
do in one go with the f4 key that’s
basically my logical test check is this
value greater than this value if it is
then we go to our next argument what
should it do now here I want to type in

good and again I can type it in like
this but you have to be careful because
if you’re typing text in a formula you
have to put it in quotation marks in
this case I also want to do a Saab

reference so I’m gonna reference this
cell and again I’m gonna fix it okay so
that’s what it should do if this does
happen and if it doesn’t happen then I

wanted to do nothing and nothing in
Excel means you can put in a double
quotation mark close the bracket press
ENTER now we’re going to send this
formula down and just double check this
is about 15,000 and these are above
15,000 okay so it looks good now let’s

go to the next one it’s not just about
15,000 but we want to mark the entities
as good if the revenue is greater than
15,000 and less than 20,000 here’s the
situations like this call for the and
function inside the e function and
allows us to do a logical test for more

than one thing and in this case we have
two things to check for so we’re still
gonna start off with our if but right
here before we start typing in our
logical test we are gonna put the and
function first and type in all our tests
that should occur and should be true
inside the ant function here so what’s

one of these logical tests it’s the same
one we had before so is this revenue
value greater than this value and I’m
going to fix it
the next argument is your second logical
test and that’s the game is this value
now we want to say is it less than this
value and I’m going to fix it as well
now don’t forget to close the bracket

for the logical tests of our and
condition here then the next argument is
what should it do if both of these occur
so if revenue is in between 15,000 and
20,000 well we said we want good in this
case I’m gonna type it in otherwise we
want nothing close the bracket press
Enter
let’s see if it filters it out correctly
so these are between this is between and
that’s between these are too high
next challenge if revenue is greater

than 15,000 and 20,000 so this is
similar to what we did before but now we
have an additional condition if revenue
is greater than or equal to 20,000 we
want them marked as exceptional and rest

is value by value I mean just a value
that’s in the cell so basically we’re
gonna have a mixed column some text and
some numbers in here how do we deal with
these conditions well the first part is
exactly like if we did before so I’m
actually gonna copy this just press

escape to leave and paste it in here
let’s just bring in my cell references
up here hit because if both of these

conditions occurs then I want good
otherwise do I want nothing now no
because I’m not done with a formula I
need to test for another condition so if

this doesn’t happen I still now need to
go and test is this app an exceptional
app in terms of revenue so exactly in
the value if false argument that’s where
you need to put your second if condition
what is our logical test this number now

is it greater than or equal to
this number that I’m gonna fix this then
what do we want we want it to write
exceptional otherwise otherwise means if

none of these occurred until now what
should it do we said we want the values
I’m just going to do a cell reference
here and now I have two ifs so I need
two brackets let’s just send this down
so now we have the good we have the

exceptional and for everything else we
have the number here so that’s how you

can use nested ifs in your formulas and
you’re not restricted to two ifs you can
obviously put another if here if the
value is below another threshold then do

this otherwise put the value or put
nothing it’s just that the more nested
ifs you have the more difficult is going
to get to understand the formula one
thing to keep in mind is that Excel does
leave the formula the moment it comes
across a true condition so the moment

this is true it puts back good and it
leaves the formula it doesn’t go and
evaluate all your other if conditions so
that’s something to keep in mind when
you’re writing these more complex
formulas now let’s look at another case

if revenue is greater than or equal to
20,000 or it’s less than or equal to
15,000 then we want to type in flak so
basically anything in between we’re

going to leave alone how do we write
this I’m going to start with the if now
another logical test that we can use is
the or function and or checks for if
either of these conditions apply so the

logical test one is this one greater
than or equal to this number we’re going
to fix it what is logical test two is
this number less than or equal to this

number and we’re going to fix it and
don’t forget to close your or condition
before you leave so there’s something in
some of this forget it continue going
and then I realize oh I forgot to close

that condition so next one what should
it do if either of these occur well we
just want to type in the word flag there

otherwise we’re going to leave them
alone and put nothing okay say flagged
the first one but see if that’s correct
is that less than yes because it’s not
in between these flag these these and
these okay so that looks good now let’s

take a look at another case where we’re
going to use bigger formulas inside our
logical test argument we have budget
values and we want to show the
percentage difference basically show the
deviation from actual to budget if that

deviation is a bigger deviation that’s
plus or minus 10% whenever you come
across cases where you have formulas
inside your if function it’s easier to
start with the core formula first the
core formula in this case is my

deviation so I’m just going to calculate
that actual divided by budget minus one
let’s just drag this down and see what
we get the aim is not to put anything
for these ones that are between plus or
minus 10% so only putting the bigger
deviations in here which actually is
these four numbers okay so how do we do

that let’s start off with our if that’s
a part of my logical test right I want
to evaluate the answer of this formula
what do I need to put in here how do I
handle that
I need the or function right and the or

always comes before so right after the
if I’m going to type in the or my
logical test one is to check the result
of this formula and see
is it bigger than 10% okay that’s the

first logical test the second one is
take a look at the same formula and see
is it less than minus 10% yeah we’re
gonna close the bracket for the or
condition what should it do if it’s true
well it should give me back the

deviation so I’m gonna paste that
formula in there otherwise it should
leave it alone and do nothing so I’m

gonna close the bracket and I should
just get these four numbers okay and in
the last example here I just wanted to
show you that you can also use symbols
as your result so let’s say for the
positive deviations I wanted an up arrow

and for the negative deviations for the
deviation in this case I wanted a down
arrow first step is to bring your
symbols in your excel sheet so I’m gonna
do that by going to insert symbols
symbols I use most often are under
aerial geometric shapes you can see them
actually here just find the ones that
you like and click on them press insert

so that’s the up one and I want the down
one I’m gonna press insert right there
and then close I can use them as text
inside formulas by putting them in
quotation marks but I actually want to
do cell references to them so if I
decide to change the symbols something

else for another type of report all I
have to do is replace that symbol in the
cell
so I’m gonna put them in two separate
cells
so let’s control X cut this one out and
put it right here can I use the same
formula for here and just replace this
with a symbol I can’t write Y because
I’m using two different symbols so I
need to split them up if I was using the
same symbol I can but I’m not in this
case
let’s just write this one from scratch
we know our logical test by now so
that’s this divided by this minus one
let’s do the positive one first so if

this is greater than 10% then we want
the symbol which is this one and press
f4 to fix it otherwise what do I need
right here can I just put the other
symbol no if I wanted everything else
that wasn’t above 10% to show this
symbol then yes but I don’t I just want

the ones that are below minus 10% to
show the other symbol so I do need an F
here and my logical test is the same so
I’m gonna copy this and paste it in here
is less than now it’s minus 10% then
this symbol let’s fix it otherwise
nothing close close because I have two
if conditions and let’s see what we get
okay so that looks good now what you can
obviously do to make this simpler is if

you calculate this deviation in a
separate column and then just reference
that cell that way
you don’t have to calculate it inside
your formula all the time but I just
wanted to show you that it is common to
have formulas and much bigger and more
complex formulas inside your if function
and depending on the outcome of that

formula it decides which way to go okay
so in this example we saw different uses
of the a function we took a look at a
simple version the version together with
and’ and or’ conditions we also take a

look at nested ifs and how to use
slightly bigger formulas inside your if
function and even how you can use
symbols in your formulas now one
question that can come up is could you

color the up arrows in a different color
than your down arrows and yes you can
you can do it in different ways you can
either use conditional formatting or you
can also use cuss
formatting and I have different videos
than these so I’m gonna share the links
to those videos in the descriptions
if you liked this video don’t forget to

give it a thumbs up and for more of
these videos don’t forget to subscribe
to this channel so that you can get
updates when new videos come out

 

Leave a comment

Your email address will not be published. Required fields are marked *