Simplify crazy long formula

[Polanskiman]

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.

=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)&" %)")),"-"),"-")),"-")))

 

[jasonb75]

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]

Thank you. Will work on it.

 

[Polanskiman]

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]

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.

 

[Dave Patton]

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

LET function - Microsoft Support

support.microsoft.com

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

 

[Polanskiman]

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

LET function - Microsoft Support

support.microsoft.com

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]

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:

=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.