# Simplify crazy long formula

#### Polanskiman

##### Board Regular
Hello,

I built this formula from scratch making explicit every condition to make sure I was not missing anything. It's working as expected, but can't help to think that it could be simplified from a logical standpoint considering some sections of the formula are basically repeating and could probably be regrouped. I know I can create names in order to shorten parts of the formula but my question is more on the logics than on the overall length. Here is the formula. Any help would be appreciated. Thank you.

Excel Formula:
``````=IF(OR(\$K\$15=0,\$K\$16=0,\$K\$17=0,AND(L31="yes",N31>M31),AND(L31="yes",N31>\$K\$16*\$O\$28%),IF(SUMIFS(\$N\$31:\$N\$39,\$L\$31:\$L\$39,"yes",\$M\$31:\$M\$39,">0")>=\$K\$16,N31,0)>=(\$K\$16-SUMIFS(\$N\$31:\$N\$39,\$L\$31:\$L\$39,"yes",\$M\$31:\$M\$39,">0"))),"-",

IF(IsPPM,
IF(L31="yes",
IF(OR(J31="",O31=""),"-",IF((R31/M31*1000)/INDEX(SS!H29:H47,MATCH(DIYTraceCalculator!G31,SS!\$B\$29:\$B\$47,0))<1,IF(L31="no",IF(S31="-","-","Add "&ROUND(S31,2)&" gr of '"&H31&"' to your Micro container."),
IF(R31="-","-","Add "&ROUND(R31,2)&" gr of '"&H31&"' to your "&M31&" ml stock container, then add "&N31&" ml of that solution to your Micro container.")),"Solubility exceeded by ("&ROUND((R31/M31*1000)/INDEX(SS!H29:H47,MATCH(DIYTraceCalculator!G31,SS!\$B\$29:\$B\$47,0))%,2)&" %)")),
IF(L31="no",
IF(L31="no",IF(J31="","-",IF((S31/\$K\$16*1000)/INDEX(SS!H29:H47,MATCH(DIYTraceCalculator!G31,SS!\$B\$29:\$B\$47,0))<1,IF(L31="no",IF(S31="-","-","Add "&ROUND(S31,2)&" gr of '"&H31&"' to your Micro container."),
IF(R31="-","-","Add "&ROUND(R31,2)&" gr of '"&H31&"' to your "&M31&" ml stock container, then add "&N31&" ml of that solution to your Micro container.")),"Solubility exceeded by ("&ROUND((S31/\$K\$16*1000)/INDEX(SS!H29:H47,MATCH(DIYTraceCalculator!G31,SS!\$B\$29:\$B\$47,0))%,2)&" %)")),"-"),"-")),

IF(IsGrams,
IF(L31="yes",
IF(OR(I31="",O31=""),"-",IF((I31/M31*1000)/INDEX(SS!H29:H47,MATCH(DIYTraceCalculator!G31,SS!\$B\$29:\$B\$47,0))<1,IF(L31="no",IF(S31="-","-","Add "&ROUND(S31,2)&" gr of '"&H31&"' to your Micro container."),
IF(R31="-","-","Add "&ROUND(R31,2)&" gr of '"&H31&"' to your "&M31&" ml stock container, then add "&N31&" ml of that solution to your Micro container.")),"Solubility exceeded by ("&ROUND((I31/M31*1000)/INDEX(SS!H29:H47,MATCH(DIYTraceCalculator!G31,SS!\$B\$29:\$B\$47,0))%,2)&" %)")),
IF(L31="no",
IF(L31="no",IF(I31="","-",IF((I31/\$K\$16*1000)/INDEX(SS!H29:H47,MATCH(DIYTraceCalculator!G31,SS!\$B\$29:\$B\$47,0))<1,IF(L31="no",IF(S31="-","-","Add "&ROUND(S31,2)&" gr of '"&H31&"' to your Micro container."),
IF(R31="-","-","Add "&ROUND(R31,2)&" gr of '"&H31&"' to your "&M31&" ml stock container, then add "&N31&" ml of that solution to your Micro container.")),"Solubility exceeded by ("&ROUND((I31/\$K\$16*1000)/INDEX(SS!H29:H47,MATCH(DIYTraceCalculator!G31,SS!\$B\$29:\$B\$47,0))%,2)&" %)")),"-"),"-")),"-")))``````

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### jasonb75

##### Well-known Member
Sometimes it is easier to work backwards, start with the results then work out the criteria that need to be met for that result rather than staring with criteria first.

In your formula I notice that the section starting "Add "&ROUND(R31,2)&" gr of '" is repeated several times. If this is identical each time then look at all possible combinations of criteria that would equal that same result. Once you have done this for each section then look at the result with the simplest set of criteria and start your formula with that.

Moving on to the next result by using the second simplest set of criteria. Where possible use process of elimination to reduce the number of logical tests needed (if you have tested for criteria <=0 then anything else must be >0 so a second test for >0 is not required, it can be correctly assumed).

Using the section starting "Add "&ROUND(R31,2)&" gr of '" again, if the value to be rounded varies depending on some of the criteria then move the logical test inside of the round function. For example "Add "&ROUND(IF(L31="Yes",R31,S31),2)&" gr of '". Quite often such a simple change can reduce the length of a formula significantly.

Having said all of that,
I built this formula from scratch making explicit every condition to make sure I was not missing anything.
If somebody changed the formula to a shorter version, do you think that you would understand how it was working if you needed to alter some of the criteria?

• Polanskiman

#### Polanskiman

##### Board Regular
Thank you. Will work on it.

#### Polanskiman

##### Board Regular
If somebody changed the formula to a shorter version, do you think that you would understand how it was working if you needed to alter some of the criteria?
Well that would depend how compressed the formula would become. Obviously the way it is at present it's easy to understand because all conditions are made explicit so it's just a matter of reading line by line and there is no mind bending to figure out what the conditions are.

I guess the ultimate question is, would performance be dramatically improved if the formula is compressed/simplified? If not then I guess leaving it like that would also be fine. That was initially why I wanted the formula to be simplified.

#### jasonb75

##### Well-known Member

ADVERTISEMENT

I guess the ultimate question is, would performance be dramatically improved if the formula is compressed/simplified?
Compressing and simplifying are not always the same thing, often a shorter formula can be less efficient.
Unless the formula is being filled to a large number of cells, I doubt that you will notice any performance improvements.

I'll try breaking it down and see what I can come up with at some point, probably at the weekend when I can disengage my brain from reality.

• Polanskiman

#### Dave Patton

##### Well-known Member
A quick thought that may be useful.
Excel's new function LET; see

With LET, you can name parts of a formula that are repeated several times.

#### Polanskiman

##### Board Regular
A quick thought that may be useful.
Excel's new function LET; see

With LET, you can name parts of a formula that are repeated several times.
Unfortunately I can't use recent functions like LET as the file needs to be backward compatible with Excel 2010. That would have been a nice thing though.

#### Polanskiman

##### Board Regular
Compressing and simplifying are not always the same thing, often a shorter formula can be less efficient.
Unless the formula is being filled to a large number of cells, I doubt that you will notice any performance improvements.

I'll try breaking it down and see what I can come up with at some point, probably at the weekend when I can disengage my brain from reality.
The formula slightly evolved. It now looks like this:

Excel Formula:
``````=IF(OR(\$K\$15=0,\$K\$16=0,\$K\$17=0,AND(\$L31="yes",\$N31>\$M31),AND(\$L31="yes",\$N31>\$K\$16*\$O\$28%),IF(SUMIFS(\$N\$31:\$N\$39,\$L\$31:\$L\$39,"yes",\$M\$31:\$M\$39,">0")>=\$K\$16,N31,0)>=(\$K\$16-SUMIFS(\$N\$31:\$N\$39,\$L\$31:\$L\$39,"yes",\$M\$31:\$M\$39,">0"))),"-",
IF(IsPPM,
IF(\$L31="yes",
IF(OR(\$J31="",\$O31=""),"-",IF((\$R31/\$M31*1000)/INDEX(SS!\$H\$29:\$H\$47,MATCH(DIYTraceCalculator!G31,SS!\$B\$29:\$B\$47,0))<1,IF(\$R31="-","-","Add "&ROUND(\$R31,2)&" gr of '"&\$H31&"' to your "&\$M31&" ml stock container, then add "&\$N31&" ml of that solution to your "&DIYTraceAdjustedMicroVolume&" ml Micro container."),"Trace Solubility Exceeded ("&ROUND((\$R31/\$M31*1000)/INDEX(SS!\$H\$29:\$H\$47,MATCH(DIYTraceCalculator!G31,SS!\$B\$29:\$B\$47,0))%,1)&" %) - Increase ml taken from stock solution to micro container.")),
IF(\$L31="no",
IF(\$J31="","-",IF((\$S31/\$K\$16*1000)/INDEX(SS!\$H\$29:\$H\$47,MATCH(DIYTraceCalculator!G31,SS!\$B\$29:\$B\$47,0))<1,IF(\$S31="-","-","Add "&ROUND(S31,2)&" gr of '"&\$H31&"' to your Micro container."),"Trace Solubility Exceeded ("&ROUND((\$S31/\$K\$16*1000)/INDEX(SS!\$H\$29:\$H\$47,MATCH(DIYTraceCalculator!G31,SS!\$B\$29:\$B\$47,0))%,1)&" %) - Increase ml taken from stock solution to micro container.")),"-")),
IF(IsGrams,
IF(\$L31="yes",
IF(OR(\$I31="",\$O31=""),"-",IF((\$I31/\$M31*1000)/INDEX(SS!\$H\$29:\$H\$47,MATCH(DIYTraceCalculator!G31,SS!\$B\$29:\$B\$47,0))<1,IF(\$R31="-","-","Add "&ROUND(R31,2)&" gr of '"&\$H31&"' to your "&\$M31&" ml stock container, then add "&\$N31&" ml of that solution to your "&DIYTraceAdjustedMicroVolume&" ml Micro container."),"Trace Solubility Exceeded ("&ROUND((\$I31/\$M31*1000)/INDEX(SS!\$H\$29:\$H\$47,MATCH(DIYTraceCalculator!G31,SS!\$B\$29:\$B\$47,0))%,1)&" %) - Increase ml taken from stock solution to micro container.")),
IF(\$L31="no",
IF(\$I31="","-",IF((\$I31/\$K\$16*1000)/INDEX(SS!\$H\$29:\$H\$47,MATCH(DIYTraceCalculator!G31,SS!\$B\$29:\$B\$47,0))<1,IF(\$S31="-","-","Add "&ROUND(\$S31,2)&" gr of '"&\$H31&"' to your Micro container."),"Trace Solubility Exceeded ("&ROUND((\$I31/\$K\$16*1000)/INDEX(SS!\$H\$29:\$H\$47,MATCH(DIYTraceCalculator!G31,SS!\$B\$29:\$B\$47,0))%,1)&" %) - Increase ml taken from stock solution to micro container.")),"-")),"-")))``````

I think I might just leave it like this although it might not be the most efficient. That formula is only replicated across 6 cells only and at least I can understand it easily. But if it can be improved without making it too hard to understand then why not.

Replies
3
Views
96
Replies
3
Views
486
Replies
2
Views
152
Replies
0
Views
138
Replies
1
Views
105

Threads
1,127,318
Messages
5,623,976
Members
416,003
Latest member
indyman

### Share this page ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?    ### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back