A Nested IF statement is where you place an IF statement within an IF statement. Nested IF statements can be very complicated to produce, let alone to read, as you can have 7 nested IF statements in a formula. Getting grips of writing such formula takes time and practice.
In this video you will learn how to prepare a nested IF statement in Excel
Example
We need to construct an IF statement that will pull in the discount rate on the order based on the number of units produced. If there is between 5000 and 10000 units then the discount is 5% and if it is greater than or equal to 10,000 units then the discount will be 7%.
Step 1:
Select the cell in which you want your formula and enter
=IF(
Step 2:
Identify the first logical test.
In this case the units in cell B6 must be greater or equal to 5000 and less than 10000 to get a discount of 5%. We therefore wrap the logical tests in AND. Each logical test is separated with a comma and then closed with a bracket. A comma is then placed at the end to move to the next step
=IF(and(B6>=B2, B6<B3),
Step 3:
Enter the value if true and a comma
=IF(and(B6>=B2, B6<B3),A2
Step 4
Enter the value if false.
If the units do not fit into this bracket but they are greater or equal to 10,000 then we have a second option, which is 7%. However in this case there is a third alternative too, and that is if the unit are below 5000. Then there is 0%. The value if false is therefore a second IF statement to see if the units are greater or equal to 10,000
=IF(and(B6>=B2, B6<B3),A2,IF(B6>=B3,
Step 5:
Enter the value if true and a comma.
In the second IF statement the value if true will A3(7%)
=IF(and(B6>=B2, B6<B3),A2,IF(B6>=B3,A3,
Step 6:
Enter the value if true and a comma.
In the second IF statement the value if true will 0. We must now close the second if statement with a bracket and also the first IF statement with a bracket.
=IF(and(B6>=B2, B6<B3),A2,IF(B6>=B3,A3,0))
My video is at DLive