Shorten Formula that Includes IFS, AVERAGEIF,LEFT, FIND, and XLOOKUP

[jbiehl]

Hello all!
I'm have to put this formula into several thousand cells. Obviously with it being so long, I'm worried that it could get edited and I would never find the error.
Does anyone have any ideas on how to shorten it?

=IFERROR(IFS(AVERAGEIF(MOY!$1:$1,"*"&LEFT(ACO$1,FIND(" ",ACO$1)-1)&"*",XLOOKUP($A11,MOY!$A:$A,MOY!$1:$1048576))>=1,1,AVERAGEIF(MOY!$1:$1,"*"&LEFT(ACO$1,FIND(" ",ACO$1)-1)&"*",XLOOKUP($A11,MOY!$A:$A,MOY!$1:$1048576))>=0, 0.79, AVERAGEIF(MOY!$1:$1,"*"&LEFT(ACO$1,FIND(" ",ACO$1)-1)&"*",XLOOKUP($A11,MOY!$A:$A,MOY!$1:$1048576))<0, 0.59),"")

 

[Fluff]

Do you have the new LET function yet?

 

[jbiehl]

I do not have that.

 

[Fluff]

Ok, in that case how about

=IFERROR(CHOOSE(SIGN(AVERAGEIF(MOY!$1:$1,"*"&LEFT(ACO$1,FIND(" ",ACO$1)-1)&"*",XLOOKUP($A11,MOY!$A:$A,MOY!$1:$1048576)))+2,0.59,0.79,1),"")

 

[jbiehl]

Ok, in that case how about

=IFERROR(CHOOSE(SIGN(AVERAGEIF(MOY!$1:$1,"*"&LEFT(ACO$1,FIND(" ",ACO$1)-1)&"*",XLOOKUP($A11,MOY!$A:$A,MOY!$1:$1048576)))+2,0.59,0.79,1),"
[/QUOTE]
That worked perfectly!
Thanks so much!

 

[jbiehl]

Ok, in that case how about

=IFERROR(CHOOSE(SIGN(AVERAGEIF(MOY!$1:$1,"*"&LEFT(ACO$1,FIND(" ",ACO$1)-1)&"*",XLOOKUP($A11,MOY!$A:$A,MOY!$1:$1048576)))+2,0.59,0.79,1),"")

That worked perfectly!
Thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.