How To Create Conditions In Excel

Make Nested Excel IF Statements With Multiple Conditions Work for You

In Excel, the IF function lets you make logical comparisons between a value and what you expect. An IF statement can have two results.

IF statements in Excel let you set conditions for your formula and tell it what to output when the condition is met or when it's not.

You can take your IF statements to the next level, by nesting them inside each other. Curious how? Read on to find out!

What Are IF Statements in Excel?

An IF statement or an IF function is one of those functions in Excel that has the potential to take your formula to the next level and make more sophisticated tasks possible. With the IF function, you can write a condition or a logical test in your formula.

If the value passes the logical test, the formula will return the first output. If it doesn't, then the formula will return another output. These two ifs will form the IF function. A great thing about the IF function is that you can nest it inside itself, which gives you the ability to create multiple conditions for your formula.

Before we get down to nested IF statements, here's a simple one: If you're already familiar with the IF function, then proceed to the next section. If not, then read our guide on IF statements and how to use them in Excel.

Excel IF Statement Example

To start things off, let's do a basic IF statement in Excel. Suppose you have the grades of some students, and you want Excel to determine if they've passed or, God forbid, failed. The grades are out of 20, and the minimum passing grade is 12.

To achieve this, you need to write an IF statement that does a logical test of whether the student's grade is above the minimum passing grade or not. If it is, then the formula will return Yes, if it is not, then the formula will return No.

Select cell C2.
In the formula bar, enter the formula below:
```
                =IF(B2>12, "Yes", "No")              
```
Remember that after the logical test, the first value is if true and the second is if false.
Press Enter. Excel will now determine if the student has passed or not.
Grab the fill handle and drag it over the rest of the cells from B2 to B7.
Observe as the fate of the students is decided.

Nested IF Statements in Excel

A nested IF statement happens when one of the values in the IF statement is itself another IF statement. As a result, we have a nested IF statement formed with this second IF statement.

You can keep nesting IF statements inside one another until all your conditions have been defined in the formula. It's best to have some sort of order, as you can easily get lost in these nests.

Nested IF Statement Example 1

In this first example, we have a list of cars and their attributes, and a list of car classes. In this scenario, we want to put these cars in classes based on how fast they can accelerate from 0 to 100 km/h. You can see the example class guide in the image below.

Now, back to the main datasheet. The goal here is to write a formula that outputs the car class by reading its acceleration. In essence, the formula will test if the acceleration is less than 3 seconds. If it is, then the car is definitely an S-class car.

However, if it wasn't less than 3, then the formula will test if it's less than 5. This test, coupled with the first test, actually tests to see if the acceleration value is between 3 and 5, which would indicate if the car is A-class or not.

These tests will go on and on and with each IF statement, the previous IF statement will become eliminated. The formula will eventually test the acceleration value for every class, and if the car didn't fit any of these (meaning an acceleration of over 14s) then the formula will output "No Class".

            =IF(B2<3,"S",IF(B2<5,"A",IF(B2<7,"B",IF(B2<9,"C",IF(B2<10,"D",IF(B2<12,"E",IF(B2<14,"F","No Class")))))))

In this instance, the value_if_false is another IF statement. This repeats seven times, until in the final statement, the value if false becomes No Class. Let's put this formula to use.

Select cell C2.

In the formula bar, enter the formula below:

                =IF(B2<3,"S",IF(B2<5,"A",IF(B2<7,"B",IF(B2<9,"C",IF(B2<10,"D",IF(B2<12,"E",IF(B2<14,"F","No Class")))))))

Press Enter. The formula will now determine the class of the first car.
Grab the fill handle and drag it over the rest of the cells (C2 to C8).
Observe as the Excel formula determines the class of each car.

Nested IF Statement Example 2

In this second example, we have a list of articles with the views each of them has received. The writers get paid for every view, however, the pay per view (PPV) amount itself is determined by the number of views.

So, in order to calculate the PPV revenue, first you have to see what PPV group the article falls in and then finally, multiply the PPV group with the number of views to get the PPV revenue.

Writing a formula to check and determine the PPV group is very much like the previous example with the car classes. You're going to need a nested IF statement.

            =IF(B2<100,0.001,IF(B2<1000,0.005,IF(B2<10000,0.01,0.02)))

This formula will test the views and see if the article falls in the first group. If it does, then the PPV group will be 0.001 and if it doesn't, the formula will move on to test if it falls in the other groups. If the article doesn't fit in any of the previous groups, then it must have over 10,000 views, which puts it in the 0.02 PPV group. It's time to use this formula in the datasheet.

Select cell C2.

In the formula bar, enter the formula below:

                =IF(B2<100,0.001,IF(B2<1000,0.005,IF(B2<10000,0.01,0.02)))

Press Enter. The formula will determine the PPV group of that article.
Grab the fill handle and drag it over the rest of the cells (C2 to C6). Now the formula will determine the PPV group for each article.

Now, let's calculate the PPV revenue.

Select cell D2.
In the formula bar, enter the formula below and press Enter:
```
                =B2*C2              
```
This will multiply the PPV group by the number of views.
Grab the fill handle on cell D2 and drag it over the rest of the cells (D2 to D6).
Excel will now calculate the PPV revenue for each article.

Nested IF Statements Made Easy

Nested IF statements can look daunting from an outsider's perspective, but if you know what you're doing, then it's as easy to use as an ordinary IF statement. IF statements are quite useful if you're using Excel for finance. How about some more financial Excel functions?

The Top 7 Financial Functions in Excel

Whether you are an accountant or a finance professional, you should know these Excel formulas.