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:

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:

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.

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”,

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…