## 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