Simplify formula

[yomarcos]

Hello everyone,

I have a formula that works for me but I think there should be a way to simplify it.

=if(isblank(if($A8<>0, if($F8<>0, (IF($G8=False,"Ok to Allocate", if(isna(FILTER(TRANSPOSE(indirect($B8)), (indirect("App_"&TO_TEXT(B8)&"_True_False!"&row($A8)&":"&row($A8))=false))), "Contractor", FILTER(TRANSPOSE(indirect($B8)), (indirect("App_"&TO_TEXT(B8)&"_True_False!"&row($A8)&":"&row($A8))=false))))), "Contractor"), "No Project selected")), "Contractor", if($A8<>0, if($F8<>0, (IF($G8=False, "Ok to Allocate", if(isna(FILTER(TRANSPOSE(indirect($B8)), (indirect("App_"&TO_TEXT(B8)&"_True_False!"&row($A8)&":"&row($A8))=false))), "Contractor", FILTER(TRANSPOSE(indirect($B8)), (indirect("App_"&TO_TEXT(B8)&"_True_False!"&row($A8)&":"&row($A8))=false))))), "Contractor"), "No Project selected"))

As you can see, do to different IF functions, I am repeating the following 4 times
FILTER(TRANSPOSE(indirect($B8)), (indirect("App_"&TO_TEXT(B8)&"_True_False!"&row($A8)&":"&row($A8))=false)))

Is there a way to simplify this formula?

Thank you

 

[GraH]

Hi, try the LET() function. You are on 365, so you should have it available.
In this function you can define intermediate results (like steps in a script) and provide these a name. In the next steps you can refer to any of the previous steps by using the name (thus not repeating the whole formula). The final step is the formula that returns the result. You might consider using IFS over IF as well.

 

[yomarcos]

Hi Grah. Thank you very much for your ideas.
I put this thread on "General Discussion & Other Applications" because this is on Google sheets but I forgot to mentioned it on the post. My apologies. I don't have the LET function however, I will try to use IFS.

Thank you!!

 

[GraH]

Hi, @yomarcos, sorry missed that on my smart phone.
The formula could have fooled me though... I'm not familiar with googlesheets, so I don't have a reflex to think it could be not excel.

 

[jorismoerings]

Hi,

If you can't use LET try this:

=IF($A8<>0,IF($F8<>0,(IF($G8=FALSE,"Ok to Allocate",IF(ISNA(FILTER(TRANSPOSE(indirect($B8)), (indirect("App_"&TO_TEXT(B8)&"_True_False!"&row($A8)&":"&row($A8))=false))),"Contractor", FILTER(TRANSPOSE(indirect($B8)), (indirect("App_"&TO_TEXT(B8)&"_True_False!"&row($A8)&":"&row($A8))=false))))),"Contractor"),"No Project selected")

This deletes two times the FILTER function because of the criterias used in the IF statement, it will never reach the FILTER function in that particular IF statement

 

[yomarcos]

Hi,

If you can't use LET try this:

=IF($A8<>0,IF($F8<>0,(IF($G8=FALSE,"Ok to Allocate",IF(ISNA(FILTER(TRANSPOSE(indirect($B8)), (indirect("App_"&TO_TEXT(B8)&"_True_False!"&row($A8)&":"&row($A8))=false))),"Contractor", FILTER(TRANSPOSE(indirect($B8)), (indirect("App_"&TO_TEXT(B8)&"_True_False!"&row($A8)&":"&row($A8))=false))))),"Contractor"),"No Project selected")

This deletes two times the FILTER function because of the criterias used in the IF statement, it will never reach the FILTER function in that particular IF statement

But you removed ISBLANK as well

 

[jorismoerings]

Compare the results between formulas, yours and mine, first and determine if, with the combination of IF functions you're already nested, you still think you need the ISBLANK part is necessary.

And remember TO_TEXT is a Google Sheets function!

 

[yomarcos]

This is what worked best for me:

=if($A2<>0, IF(and($G2=False,$F2<>0),"Ok to Allocate", if(or(isna(FILTER(TRANSPOSE(indirect($B2)), (indirect(TO_TEXT(B2)&"_True_False!"&row($A2)&":"&row($A2))=false))),isblank(FILTER(TRANSPOSE(indirect($B2)), (indirect(TO_TEXT(B2)&"_True_False!"&row($A2)&":"&row($A2))=false)))), "Contractor", transpose(SORTN(transpose(FILTER(TRANSPOSE(indirect($B2)),(indirect(TO_TEXT(B2)&"_True_False!"&row($A2)&":"&row($A2))=false))),5)))),"No Project selected")