fbpx
Search
Close this search box.

A clever technique to simplify your long, nested IF formulas

Share

Facebook
Twitter
LinkedIn

Recently I optimized a pretty long nested IF formula using a simple but elegant trick. I made it 80% shorter! In this article, let me explain the process and share the formulas.

A technique to make long Excel formulas shorter

The Situation: Discount Calculation

Imagine you have to calculate the appropriate discount using below rules.

Example Discount Rules - for nested IF formula
Picture 1: Rules for Discount Calculation

And you have transaction data like below:

sample data - nested if formula

How would we write the formula in column H?

Option 1: Long, Nested IFs

We can calculate the discount by writing a complex, lengthy and nested IF formula.

Here is one such formula:

				
					=LET(cat, XLOOKUP(F5,products[Name], products[Category]),
        IFS(OR(D5="India", D5="USA",D5="UK"),
            IFS(AND(G5>=200, G5<=999), 
                IF(OR(cat="Bars", cat="bites"),
                        IF(E5="New",Rules!$F$7, Rules!$F$8),
                    0),
                AND(G5>=1000, G5<=1999), 
                    IFS(cat="Bars", 
                            IF(E5="New",Rules!$F$11, Rules!$F$12), 
                        cat="Bites", Rules!$F$14,TRUE, 0),
                G5>=2000, 
                    IF(E5="New", Rules!$F$16,
                        IFS(
                            cat="Bars",Rules!$F$17, 
                            cat="Bites", Rules!$F$18, 
                            TRUE, 0)
                    )
                )
            )
        )
				
			

What this formula is doing?

  1. We start by calculating the “category” of the product using XLOOKUP and storing it in the variable cat
  2. Then we check the rules (refer to picture 1 above)
  3. Once we reach the lowest level of the rule, we get the matching discount from the rules worksheet cells.
  4. If no discount applies, we return 0

What is wrong with this formula?

Long and error prone

Such formulas are too long to write correctly and errors are not easy to catch.

Not easy to update

When the business rules change, the formulas become hard to edit.

Hard to maintain

The formulas become a nightmare to maintain and document.

Option 2: A smart alternative

What if we can rewrite the rules so that we can write shorter formulas?

I suggest rewriting such business rules as a table like this:

Discount rules - rewritten as a table

Once you have such a table, we can then rewrite our formula using SUMIFS & wildcard characters. Like below:

				
					=LET(cat, "*"&XLOOKUP(F5,products[Name],products[Category])&"*",
        SUMIFS( discount[Discount],
                discount[Country],"*"&D5&"*",
                discount[Category],cat, discount[Customer Type],"*"&E5&"*",
                discount[Quantity from],"<="&G5,
                discount[Quantity to],">="&G5))
				
			

How does this formula work?

  1. We start by calculating the “category” of the product using XLOOKUP, pad this with * and store it in the variable cat
  2. Then we use SUMIFS to add up [Discount] column by 
    1. Checking the country (in D5) against [Country] column of discount table
    2. cat against [Category] column
    3. Customer type (E5) with [customer type] column
    4. Quantity (G5) with the to & from ranges
  3. If there are no discounts then the SUMIFS would be 0
  4. Else it would tell us what the discount is.

Advantages of the SUMIFS approach

Easy to write & test

The formula is easier to write and test. Hence fewer errors.

Easy to maintain & update

Whenever the business rules change or new products are added, updating the discount table is all you need to do.

Scalable

Even when you have 100s of rules, this table approach is easy to scale and won't increase the formula size.

Use FILTER to work with text

If you need to get a non-numeric value as the output, we can use FILTER() instead of SUMIFS.

Video Tutorial - Honey, I shrunk the formulas

I made a video about this concept. See it below or click here to watch it on my YouTube channel.

Try it yourself - Here is a sample file

Want to have a play with the data and see which formula is easier to write?

Click here to download the sample file.

Your thoughts on this approach?

What do you think about this approach? Leave a comment. 

Also, do check out below pages for more on other ways to make advanced Excel formulas.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

4 Responses to “A clever technique to simplify your long, nested IF formulas”

  1. James says:

    Love the method, very nice and clean. I just have a feeling that with manually created table for the discounts that errors or duplications will creep in, and it doesn't have discounts change over time. Presumably the real data set had dates in it, so start dates are useful in discount lookup. I would recommend a countifs just to double check you are only getting one result, but it's a minor thing

  2. How can we choose a large number of tables and pages within a single PDF, then repeat that process for the remaining PDFs within the same folder, and finally use power query to extract just those tables and pages?

  3. Tetonne says:

    I love it thanks a lot

  4. Megan says:

    Love this technique, it's very well-organized and tidy. However, I do have a concern that when using a manually created table for discounts, there might be errors or instances of repetition creeping in. Additionally, this method doesn't account for changes in discounts over time. Assuming the original data set included dates, having start dates would be beneficial for looking up the applicable discount.

Leave a Reply