Nested IF statements are the thorn in the side of many Excel users.  They can become a long complex formula that is difficult to both read and write.

However, if you have been using Excel 365 one of the new functions available is IFS.  Now, this function is also available in Excel 2019 Standalone as a core function.

The IFS function can be used to replace this complex nested IF statements in many cases.  Its syntax is simple

=IFS(Logical test 1, Value if true 1, Logical test 2, Value if true 2…..)

Using IFS we can test up to 127 conditions.  Word of caution thou, by using larges formulas they become difficult to read, understand, and troubleshoot, so don’t be afraid to break your formula down into smaller chunks where you can.

In this article, we are going to take a look at both the nested IF statement and IFS.  The example is based on this small set of data.  An organization offers a discount of 4% on sales where the units are 6000 or more and a 6% discount on sales where the units sold are 10,000 or more.

We wish to calculate the discount for each sales order.  We have been given 3 orders, each with a different unit quantity sold.

How to solve this using a nested IF statement

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

The syntax for an IF statement is

=IF(logical test, Value if true, Value if False)

In this case, we need a function that says

If the units are greater than 10,000 give me 6%, if it is greater than 6,000 give me 4%, otherwise give me 0%

Our logical test is that if the units are greater than 1000, our value if true is 6% and our value if false is a second IF statement.

The logical test in our second IF statement would be if the units are greater than 6000 and the value if true is 4% and the value if false is 0)

In Excel language that formula would be

=IF(B6>10000,6%,IF(B6>6000,4%,0))

Note the order we have place the logical tests, from higher >10000, to lower >6000.  The reason for this is that if a first condition is met, the value for true will be returned.

Lets say we were to re-write the formula to read:

=IF(B6>6000,4%,IF(B6>10000,6%,0))

What do you think will happen?

Well, when it first tests to see if 4000 units are greater than 6000, it will get a false and so move on to the next statement, and here it will test to see if the 4000 units are greater than 10000, in this case returning a false and the value if false.  This works fine

Looking next at the 6500 units first is this greater than 6000, the answer is true so 4% is returned. Perfect, this too works find

However, when we test 15000 units, the first test is to see if it is greater than 6000 and this is true, so 4% will be returned and it will not move on to the next test.  Epic fail. Units of 10000 or more should have a 6% discount.

This is why it is important to place your logical tests in the right order.  You could overcome this by making an even more complex formula adding ADD and OR statement within the nested IF, but why complicate things more!

How to solve this using the new IFS Function

The syntax for IFS is

=IFS(Logical test 1, Value if true 1, Logical test 2, Value if true 2…..)

Note, there is no value if false.

Like with the nested if Statement you must place your logical tests in the correct order, from higher to lower.

Using this syntax we could now simply says

If the units is greater than 10000 give me 6%, if the units are greater than 6000 give me 4%

and as there is no FALSE statement in IFS, we can finish by saying

if the units are less than 6000 give me 0

In Excel language that equates to

=IFS(B6>10000,6%,B6>6000,4%,B6<=6000,0)

Where the first logical test is B6>10000, B6 in this case having 4000 units, a true value is not returned so it moves to the second logical lets.  This is B6>6000 and again a true value is not found.  We have then added a third logical test to account for the ‘false’ that would be found in a normal IF statement and this is B6<=6000 and as this is found to be true, 0 is returned.

If we write the formula with the logical tests in the wrong order, for example

=IFS(B6>6000,4%,B6>10000,6%,B6<=6000,0)

What do you think is going to happen?

As you can see from the image above, we end up with the same problem as we did with nested IF statements.  As the value for the first found true result is returned, and the fist logical test is >6000, items with units greater than this, even if they fall into the next logical tests are found to be true and so the next logical test is not carried out and the wrong result can be seen for units of 15000 or more.

 

In return for this Tip -No More Complex Nested IF Statement with the use of IFS– I ask that you share this post with your friends and colleagues

Don’t forget to subscribe to our newsletter for regular updates on Excel, Excel Power Tool and Power BI

This blog is powered by blockchain using Steempress.io as the integration tool. Having a Hive account means you can collect rewards on any comments you learn on our blog posts  You can use this link to register for a Hive account.