The if function allows you to give a condition to Excel. Based on this condition, many other things can be done. The default output for this function is just true or false. The key here is that you can define an action if the output is true; or another action if the output is false. This ‘action’ can be a simple data output or another calculation or even another function. This lets you create whole flows of logical actions and reactions. Let’s learn with some examples:

IF

The IF function is made up of 3 parts – a condition, true value and a false value. In plain English, the IF function basically tells excel to return an answer based on a condition. It is an “If This – Then This – Otherwise That” statement.

=IF(logical_test, [value_if_true], [value_if_false])

So, the logical test here is the condition that Excel has to evaluate. If the answer to the question is true, Excel will do whatever you put in [value if true]. If the answer is false, excel will do [value if false].

Let’s look at a simple example:

 

Name

Age

Adult?

Imaran

24

Abhithi

28

Imtiaz

8

Devyani

19

Jasamit

27

Kunti

30

Kalash

19

Mangala

15

Mayank

29

Narayani

16

Nischith

18

Ojaswini

30

 

Here, we need to find out if each person has crossed the age of 18 or not. Let’s ask Excel using the IF function, so it can fetch correct answers for each row.

Here the IF function would be:

IF(age>=18,”Adult”,”Not Adult”)

IF(age<18,”Not adult”,”Adult”)

Both these options essentially mean the same. Just that the condition is reversed, hence the [value if true] and [value if false] are reversed too.

As soon as we copy the formula down to all cells, the answers come up:

 

IF function

IF function

Now, this is just a single condition. But if more conditions are needed to be evaluated to arrive at an answer; then you simply put another IF function in either [the value if true] or in [value if false].

This is demonstrated in the example below where the 2nd IF condition is placed inside the [value if false] of the 1st condition. This is called Nesting of Functions, where you put one function inside the other. Hence this is called, NESTED IF; basically, IF under IF.

We need to identify which category each person falls under, based on the values in the table on the right.

Nested IF

Nested IF

Here, we need to ask multiple questions, one by one:

=IF(question 1, [value if true],question 2, [value if true], … question ‘n’, [value if true], [value if false]…

=IF(age<=12,”Kid”,

IF(age<=19,”Teen”,

IF(age<=21,”Adolescent”,

“Adult”)))

 

Nested IF

Nested IF

Now, this is how nested if works – it’s a condition inside a condition, inside a condition. If the first condition is true, then the following conditions will be ignored. If the first condition is false, then the next condition is waiting in the [value if false]. Like this, you can ask a series of conditions to get to the correct answer.

The only problem is; as you might already have faced it; these formulas get tougher to read as they get longer and longer. There is a work-around for this – using the IFS function.

IFS

The IFS function is built so it just goes on checking for the first true result and only then calculate. This makes it much faster to implement. Here is how it works:

=IFS(logical_test_1, value_if_true_1,logical_test_2,value_if_true_2…

Using only IF, we had made this formula:

=IF(B2<=12,”Kid”,IF(B2<=19,”Teen”,IF(B2<=21,”Adolescent”,”Adult”)))

But with the IFS function, we don’t need to put multiple IF functions and also not worry about the ( inside a ( inside a ( and closing them all ))) at the end. Here only 1 ( opens and 1 ) closes.

=IFS(B2<=12,”Kid”,B2<=19,”Teen”,B2<=21,”Adolescent”,B2>21,”Adult”)

IFS solution

IFS solution

So, this is easier for us to read and faster for Excel to calculate.

In cases where a value does not match and evaluate to true for any of the conditions, the IFS function will output a #N/A error. Therefore, in cases where a value if false is to be specified, it can be done using the IFERROR function:

=IFERROR(value, value_if_error)