Polanskiman
Board Regular
 Joined
 Nov 29, 2011
 Messages
 112
 Office Version

 365
 2016
 2011
 2010
 2007
 Platform

 Windows
 MacOS
 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.
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$16SUMIFS($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)&" %)")),""),"")),"")))