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.
The Situation: Discount Calculation
Imagine you have to calculate the appropriate discount using below rules.
And you have transaction data like below:
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?
- We start by calculating the “category” of the product using XLOOKUP and storing it in the variable cat
- Then we check the rules (refer to picture 1 above)
- Once we reach the lowest level of the rule, we get the matching discount from the rules worksheet cells.
- 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:
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?
- We start by calculating the “category” of the product using XLOOKUP, pad this with * and store it in the variable cat
- Then we use SUMIFS to add up [Discount] column by
- Checking the country (in D5) against [Country] column of discount table
- cat against [Category] column
- Customer type (E5) with [customer type] column
- Quantity (G5) with the to & from ranges
- If there are no discounts then the SUMIFS would be 0
- 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?
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.
11 Responses to “Use Alt+Enter to get multiple lines in a cell [spreadcheats]”
@Chandoo:
One more useful trick.......
In a column you have no. of data in rows and need to copy in the next row from the previous row, no need to go for the previous rows but entering Alt + down arrow, you will get the list of data, (in asending order), entered in the previous rows...
This is another great tip. I use this all the time to make sense of some *very* long formulas. As soon as the formula is debugged I remove the break.
Great tip Chandoo!
I use this feature often and it has even gotten the, "how did you do that" response.
Thanks!
@Ketan: Alt+down arrow is an awesome tip. I never knew it and now I am using it everyday.
@Jorge, Tony: Agree... 🙂
[...] Day 1: Insert Line Breaks in a Cell [...]
how can we merge a two sheet.
excellent idea. Chandoo you are genious
Hi chandoo,
I have used ctrl+enter to break the cell. But I did not get the result.
Please tell me how can i break the cell in multiple lines.
Hi, Ranveer,
Its not Ctrl+enter to break the cell, use Alt+Enter to make it happen.
hi Chandoo....
how we can use Alt+Enter in multiple rows at the same time please reply hurry i have lot of work and have no time and i m stuck in this. 🙁
Alt+J worked once 🙁
So I found another more reliable way:
=SUBSTITUTE(A2,CHAR(13),"")
Where A2 is the cell that contains the line breaks which the code for it is CHAR(13). It will replace it with whatever inside the ""