vendredi 29 octobre 2021

Is anyone able to clean up this massive nested if statement?

I am looking to see if anyone is able to condense this code block and make it cleaner. There are multiple sheet links: 'Main Page' refers to the main page where you would go to see the steps for the worksheet and select what group of data you are working on out of three options. 'Main Page'!$BA$10 The rest of the code is basically just criteria and it looks at all possible combinations.


    =IFERROR(
    IF(('Main Page'!$BA$10=1),"",
    
    IF(AND(I472>=0,(M472+O472=0),I472<>"",'Main Page'!$BA$10=6),"",
    IF(AND(I472<0,I472<>"",'Main Page'!$BA$10=6),"Move "&TEXT(I472*-1,"#")&" unit(s) from FRZ_NET to FG",
    IF(AND(I472>0,I472<=O472,I472<>"",'Main Page'!$BA$10=6),"Move "&TEXT(IF(I472<=O472,I472,O472),"#")&" unit(s) from FG to FRZ_NET",
    IF(AND(I472>0,O472<=(1-1),M472>0,I472<>"",'Main Page'!$BA$10=6),"Move "&TEXT(IF(I472<=M472,I472,M472),"#")&" unit(s) from BLOCK_BIN to FRZ_NET",
    IF(AND(I472>0,I472<=O472,O472>0,M472=(1-1),I472<>"",'Main Page'!$BA$10=6),"Move "&TEXT(IF(I472<=O472,I472,O472),"#")&" unit(s) from FG to FRZ_NET",
    IF(AND(I472>0,I472>O472,O472>0,M472<=(1-1),I472<>"",'Main Page'!$BA$10=6),"Move "&TEXT(IF(I472>O472,O472,I472),"#")&" unit(s) from FG to FRZ_NET",
    IF(AND(I472>0,I472<=O472,O472>0,M472>0,I472<>"",'Main Page'!$BA$10=6),"Move "&TEXT(IF(I472<=O472,I472,O472),"#")&" unit(s) from FG & "&TEXT(IF(AND(I472>M472,M472>0),IF(I472-O472>=M472,M472,I472-O472)),"#")&" unit(s) from BLOCK_BIN to FRZ_NET",
    IF(AND(I472>0,I472>O472,O472>0,M472>0,I472<>"",'Main Page'!$BA$10=6),"Move "&TEXT(IF(I472>O472,O472,I472),"#")&" unit(s) from FG & "&TEXT(IF(AND(I472>O472,M472>0),IF(I472-O472>M472,M472,I472-O472)),"#")&" unit(s) from BLOCK_BIN to FRZ_NET",
    
    IF(AND(I472>=0,(M472+O472=0),I472<>"",'Main Page'!$BA$10=7),"",
    IF(AND(I472<0,I472<>"",'Main Page'!$BA$10=7),"Move "&TEXT(I472*-1,"#")&" unit(s) from FRZ_NONNET to FG",
    IF(AND(I472>0,I472<=O472,I472<>"",'Main Page'!$BA$10=7),"Move "&TEXT(IF(I472<=O472,I472,O472),"#")&" unit(s) from FG to FRZ_NONNET",IF(AND(I472>0,O472<=(1-1),M472>0,I472<>"",'Main Page'!$BA$10=7),"Move "&TEXT(IF(I472<=M472,I472,M472),"#")&" unit(s) from BLOCK_BIN to FRZ_NONNET",
    IF(AND(I472>0,I472<=O472,O472>0,M472=(1-1),I472<>"",'Main Page'!$BA$10=7),"Move "&TEXT(IF(I472<=O472,I472,O472),"#")&" unit(s) from FG to FRZ_NONNET",
    IF(AND(I472>0,I472>O472,O472>0,M472<=(1-1),I472<>"",'Main Page'!$BA$10=7),"Move "&TEXT(IF(I472>O472,O472,I472),"#")&" unit(s) from FG to FRZ_NONNET",
    IF(AND(I472>0,I472<=O472,O472>0,M472>0,I472<>"",'Main Page'!$BA$10=7),"Move "&TEXT(IF(I472<=O472,I472,O472),"#")&" unit(s) from FG & "&TEXT(IF(AND(I472>M472,M472>0),IF(I472-O472>=M472,M472,I472-O472)),"#")&" unit(s) from BLOCK_BIN to FRZ_NONNET",
    IF(AND(I472>0,I472>O472,O472>0,M472>0,I472<>"",'Main Page'!$BA$10=7),"Move "&TEXT(IF(I472>O472,O472,I472),"#")&" unit(s) from FG & "&TEXT(IF(AND(I472>O472,M472>0),IF(I472-O472>M472,M472,I472-O472)),"#")&" unit(s) from BLOCK_BIN to FRZ_NONNET",
    
    IF(AND(I472>0,M472<=(1-1),N472<=(1-1),I472<>"",'Main Page'!BA$10=2),"",
    IF(AND(I472>0,M472<=(1-1),N472<=(1-1),O472<=(1-1),I472<>"",'Main Page'!BA$10=2),"",
    IF(AND(I472<=(1-1),M472<=(1-1),N472<=(1-1),O472<=(1-1),I472<>"",'Main Page'!BA$10=2),"",
    IF(AND(I472>M472,M472>0,N472<=(1-1),I472<>"",'Main Page'!$BA$10=2),"Move "&TEXT(M472,"#")&" unit(s) from BLOCK_BIN to FG",
    IF(AND(I472>N472,N472>0,M472<=(1-1),I472<>"",'Main Page'!$BA$10=2),"Move "&TEXT(N472,"#")&" unit(s) from SCRAP to FG",
    IF(AND(I472>0,I472<=M472,I472<>"",'Main Page'!$BA$10=2),"Move "&TEXT(IF(I472<=M472,I472,M472),"#")&" unit(s) from BLOCK_BIN to FG",
    IF(AND(I472>0,I472<=N472,I472<>"",'Main Page'!$BA$10=2),"Move "&TEXT(IF(I472<=N472,I472,N472),"#")&" unit(s) from SCRAP to FG",
    IF(AND(I472>0,I472<=M472+N472,I472<>"",'Main Page'!$BA$10=2),"Move "&TEXT(IF(I472<M472,I472,M472),"#")&" unit(s) from BLOCK_BIN & "&TEXT(IF(AND(I472>M472,N472>0),IF(I472-M472=N472,N472,I472-M472)),"#")&" unit(s) from SCRAP to FG",
    IF(AND(I472>0,I472>M472+N472,I472<>"",'Main Page'!$BA$10=2),"Move "&TEXT(IF(I472>M472,M472,I472),"#")&" unit(s) from BLOCK_BIN & "&TEXT(IF(I472>N472,N472,I472-M472-N472),"#")&" unit(s) from SCRAP to FG",
    "")))))))))))))))))))))))))),"")

It renders something like this:

th {
  font-weight: bold;
}

td {
  text-align: center;
}
<table>
 <tr>
  <th>H472</td>
  <th>L472</td>
  <th>M472</td>
  <th>N472</td>
 </tr>
 <tr>
  <td>200</td>
  <td>Move 100 unit(s) from BLOCK_BIN & 99 unit(s) from SCRAP to FG</td>
  <td>100</td>
  <td>99</td>
 </tr>
</table>

Aucun commentaire:

Enregistrer un commentaire