< Go Back

Nested IF Statements in Excel

As per syntax, 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 IF statements for each logical test and its value_if_true argument as per basic syntax of IF formula.

 

Nested means to combine two formulas together, one inside the other and outer formula controls the result of inner formula, like;

=DAY(TODAY())

In this nested formula example, 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 same fashion, two or more IF statements can be nested together, one inside the other, but 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 better understanding of nested IF statements’ syntax and pattern each IF statement is presented in a separate font color. 2nd IF statements 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 number of brackets equal to number of IF statements nested in formula. For example, if you have nested three IF statements in 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… Sometime too big 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 single formula. Suppose you have sales data 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 amount 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 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)))))

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar