No More Complex Nested IF Statement with the use of IFS

Nested IF statements are the thorn is the side of many Excel users. They can become long complex formula that are 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 organisation 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 order, 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 within a formula. 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 is greater than 10,000 give me 6%, if it is greater than 6,000 give me 4%, You can read this full article here http://theexcelclub.com/nested-if-statements-replaced-with-ifs-function

---

Posted from my blog with SteemPress : http://theexcelclub.com/nested-if-statements-replaced-with-ifs-function/

---