Probably THE most used excel formula
Perhaps the most useful formula in the excel universe.
This formula gives you the first building block of decision making.
What’s the formula used for?
IF is a logical formula.
It means that you can ask the formula to make a logical test
(is it smaller, bigger, equal, true or false etc…)
Then based on the result of this logical question, the formula will result either TRUE or FALSE.
The second part of the formula is the output bit, where you can tell the formula what to do in case the test comes back TRUE or FALSE.
Where would we use the formula?
Pretty much everywhere where we would need to make a decision based on Value or some kind of properties.
We have to remember that with the help of other formulas we can test for all sort of properties, conditions.
I always try to say it out loud the logical part whenever I make an IF statement.
“If this is something… , then do that. If not then do this…”
What does the formula need?
The formula need a couple of things:
- =IF(ISEVEN($A2),”EVEN”,”ODD”) – Some kind of a logical test (Here we testing if the number in A2 is even or not)
- =IF(ISEVEN($A2),“EVEN”,”ODD”) -The output in case the test is TRUE
- =IF(ISEVEN($A2),”EVEN”,“ODD”) -The output in case the test is FALSE
Here is an example of the formula where we will test to see if the numbers are even or odd.
We will do this with the help of the ISEVEN formula inside the IF formula.
HINT: As soon as you start to type your formula, the formula bar will give you a small hint, which will make your life very easy.
The bold part changes depending on which part of your formula you click in in side of the formula bar.
Here we can clearly see the same structure that we discussed above
What sort of test I can do with IF?
Here is a few example of what you can test for.
All the formulas below are written to test the content of the A1 cell.
This formula will test to see if A1 is more than 10
=IF(A1>10,"More than 10", "Less than 10")
This formula will test to see if A1 is more or equal than 10
=IF(A1>=10,"More than 10", "Less than 10")
This formula will test if the content of cell A1 is a number
=IF(ISNUMBER(A1),"A1 is a number","A1 is NOT a number")
Can I test more than one condition at a time? – AND/OR
You can use the AND or the OR formula to test for more condition
Once you add the AND or the OR formula inside your IF statement the singular logical test will become multiples of logical tests.
Below I will add an AND inside the excel IF statement so now the formula will test for the following:
IF the number is even AND also larger than 2 then give me back TRUE otherwise give me back FALSE
The syntax for the OR formula is exactly the same.
Nested IF? What is it?
You probably heard this term before.
Nested if is when you put another IF statement inside of the first one.
We can make a simple test saying:
IF number is bigger than 2 then… But instead of the TRUE or FALSE output we can say for any of the outcomes
That if the number is bigger than 2 then check if the number is EVEN or ODD.
The formula for this would look like this:
=IF($A2>2,IF(ISEVEN(A2),"Even and bigger than 2","Bigger than 2 but NOT even"),"Not bigger than 2")