Simplify crazy long formula

Polanskiman

Board Regular
Joined
Nov 29, 2011
Messages
112
Office Version
  1. 365
  2. 2016
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile
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)&" %)")),"-"),"-")),"-")))
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 29, 2011
Messages
112
Office Version
  1. 365
  2. 2016
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile
Thank you. Will work on it.
 

Polanskiman

Board Regular
Joined
Nov 29, 2011
Messages
112
Office Version
  1. 365
  2. 2016
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile
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
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows

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.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,629
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Nov 29, 2011
Messages
112
Office Version
  1. 365
  2. 2016
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile
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
Joined
Nov 29, 2011
Messages
112
Office Version
  1. 365
  2. 2016
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,831
Messages
5,627,147
Members
416,223
Latest member
RichardHell

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
Top