Excel IF Statements Part 3 – Nested IF Statements

In this week’s IF statement series, we are covering Nested IF Statements. This convenient formula allows you to return values based on multiple logic tests built into – or nested – in the formula itself. We will explore several uses of nested IF statements in demonstrations below.

Syntax of Nested IF Statements

When you write a nested IF statement, you are essentially saying “If true, do this, if true, do this, if true, do this…if false, then do you this.” You can nest up to seven IF criteria within the formula.

The syntax for nested IF Statements is:

=IF(True-This,IF(True, this,IF(True,this,IF(True,This,IF(False,this))))

Notice: For each new IF statement, you open a new parenthesis, however, the parenthesis closing all come at the end of the formula.

Uses for Nested IF Statements

There are many reasons to use nested IF statements and you will have to determine the best situations to employ them. For the purposes of demonstration, we will use nested IF statements to calculate commission rates based on a sliding scale and salary increases based on years of service.

Calculating Commission on a Sliding Scale

If you remember our Simple IF Statements from last week, we used a single IF condition to return a flat bonus rate or determine No Bonus. But, what if there is a sliding scale? By nesting several IF statements together, you can create a dynamic formula that returns the commission amount based on a sliding scale.

Let’s say you had the following spreadsheet:

You can see the sliding scale in the table to the right. To write this scale into a nested IF statement you would write it as:

=IF(D2<101,”No Bonus”,IF(D2<201,D2*0.03,IF(D2<301,D2*0.03,IF(D2<=500,D2*0.04,IF(D2>501,D2*0.05)))))

Here, what you are saying is: IF the amount in column D is less than 101, return the value No Bonus, IF it’s less than 201, times the amount by 2% (0.02), IF less than 301, times it by 3%, IF less than or equal to 500, times it by 4%, and if it’s greater than 501, times it by 5%.

Here is how that looks in Excel:

As you can see, it automatically calculates the commission payout based on the range of the sale amount.

Calculating Salary Increase Based on Years Employed

Here we have a similar issue. We can see the employee list, their current salary and years employed. We have a table that shows what percentage their salary should increase based on length of employment.

To quickly find the new salary amount, you would write the following IF statement:

=IF(C2<6,B2*1.02,IF(C2<11,B2*1.03,IF(C2<16,B2*1.04,IF(C2<21,B2*1.05))))

Here, we are saying “IF the years in column C are less than 6, then increase the salary in column B by 2% (1.02). IF it’s less than 11 years, increase by 3%, IF less than 16 years, increase by 4% and IF greater than 21 years, increase by 5%. “

Here is how it looks in Excel:

As you can see, there are a variety of ways and reasons to use nested IF statements. We here at Learn Excel Now hope you know feel comfortable applying these functions in your spreadsheets.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now