As per syntax, a basic IF statement handles one logical test, and based on its TRUE and FALSE values, it specifies one value_if_true argument’s value and one value_if_false argument’s value. But there are some cases where there are multiple logical tests and multiple value_if_true arguments’ values, having a separate value in each TRUE case. For that purpose you need to create multiple, or nested, IF statements for each logical test and its value_if_true argument as per the basic syntax of the IF formula.
Using nested IF statements in Excel
Nested means to combine two formulas together, one inside the other and outer formula controls the result of the inner formula, like;
=DAY(TODAY())
In this nested formula example, the TODAY function returns today’s date inside DAY function and outer DAY function returns the day of the month for date returned by TODAY function.
In the same fashion, two or more IF statements can be nested together, one inside the other, but the only difference between other nested formulas and nested IF formulas is that outer IF formula runs first in IF statements.
First look at the syntax of two nested IF statements and a brief explanation.
IF(logical_test1, [value_if_true1], IF(logical_test2, [value_if_true2], [value_if_false]))
Here, in this nested IF statements you can see two IF statements combined together, one insider the other. For a better understanding of nested IF statements’ syntax and pattern, each IF statement is presented in a separate font color. The 2nd IF statement starts followed by the value_if_true argument of 1st IF statement, and so on.
In the last IF statement, you can optionally supply value_if_false argument and close the nested IF formula by the number of brackets equal to the number of IF statements nested in the formula. For example, if you have nested three IF statements in a formula, then it must contain three closing brackets at the end.
The trick is that if outer logical_test argument (first logical test) comes TRUE, then it returns first value_if_true argument’s value, and it ignores the inner IF formulas. If outer IF formula logical_test argument comes FALSE then inner IF formulas are run.
As a general information regarding nested IF statements formula, you can nest or combine 7 IF statements in one formula till MS Excel 2007. But in MS Excel 2010 and later versions, you can nest up to 64 IF statements.
Generally, nested IF statements are used for various scales, like grade structure, commission structure, tax structure and so on… Sometimes too large of a scale structure results in very complicated nested IF statements, so it is very important to use nested IF statement wisely otherwise it gets complicated to understand easily.
In this example, you will learn how to create multiple nested IF statements in a single formula. Suppose you have sales data from a company for various salespersons. You have a sales commission structure based on sales amount as given below, and you need to assign a commission % as per structure using nested IF statements approach.
In the above screen shot you have a commission structure based on sales amounts and for your better understanding, we have also setup conditions or logical tests used in nested IF statements in this example.
Now by using each condition or logical test in each IF statement, you will nest all the five IF statements in a single formula as follows;
=IF(C2<30000,0%,IF(C2<40000,5%,IF(C2<50000,6%,IF(C2<60000,8%,IF(C2>=60000,10%)))))
OR
=IF(C2<$G$3,$H$2,IF(C2<$G$4,$H$3,IF(C2<$G$5,$H$4,IF(C2<$G$6,$H$5,IF(C2>=$G$6,$H$6)))))
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.
Are you still looking for help with the IF function? View our comprehensive round-up of IF function tutorials here.
Leave a Comment