Polanskiman
Board Regular
- Joined
- Nov 29, 2011
- Messages
- 119
- 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$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)&" %)")),"-"),"-")),"-")))