Good Evening,
GOLD STAR to anyone who can help with this conundrum!
I am trying to create a custom function for the formula below. I can't find directions on how to create a custom function with multiple nested formulas.
Even though this is long, there are only three input values that will change, so I was hoping that the function could only require those inputs.
My dream is to have a formula that looks something like this: LookupBand(LookupWorksheet, Criteria, LookupValue)
The example formula below is in cell S2
Input descriptions:
-LookupWorksheet: In this example, the LookupWorksheet is ‘Pre-Test’!. The worksheet called will be different each time, but cells referenced will always be the same ($A$A and $1:$10485576). It is used as the reference array and the return array in the XLOOKUP formula.
-Criteria: In this example, the Criteria is S$1. The criteria will always be the header of the cell in which the formula is written. It is the "text" in the LEFT formula and "within text" in FIND formula.
-LookupValue: This is the lookup value in each XLOOKUP. Honestly, if it weren’t for the SPILL error, I would have made the lookup value $A$A because the value is always in column A and the row of the formula. This the formula is in row 2, so the LookupValue in my dream function is $A2
Current Formula:
=IFERROR(IFS(IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<10,"0-9",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<20,"10-19",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<30,"20-29",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<40,"30-39",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<50,"40-49",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<60,"50-59",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<70,"60-69",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<80,"70-79",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<90,"80-89",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<100,"90-100"),"")
The following image is the same as the content above... just color-coded.
Thanks so much for reading... and hopefully helping!
GOLD STAR to anyone who can help with this conundrum!
I am trying to create a custom function for the formula below. I can't find directions on how to create a custom function with multiple nested formulas.
Even though this is long, there are only three input values that will change, so I was hoping that the function could only require those inputs.
My dream is to have a formula that looks something like this: LookupBand(LookupWorksheet, Criteria, LookupValue)
The example formula below is in cell S2
Input descriptions:
-LookupWorksheet: In this example, the LookupWorksheet is ‘Pre-Test’!. The worksheet called will be different each time, but cells referenced will always be the same ($A$A and $1:$10485576). It is used as the reference array and the return array in the XLOOKUP formula.
-Criteria: In this example, the Criteria is S$1. The criteria will always be the header of the cell in which the formula is written. It is the "text" in the LEFT formula and "within text" in FIND formula.
-LookupValue: This is the lookup value in each XLOOKUP. Honestly, if it weren’t for the SPILL error, I would have made the lookup value $A$A because the value is always in column A and the row of the formula. This the formula is in row 2, so the LookupValue in my dream function is $A2
Current Formula:
=IFERROR(IFS(IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<10,"0-9",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<20,"10-19",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<30,"20-29",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<40,"30-39",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<50,"40-49",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<60,"50-59",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<70,"60-69",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<80,"70-79",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<90,"80-89",IFERROR(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),"")<100,"90-100"),"")
The following image is the same as the content above... just color-coded.
Thanks so much for reading... and hopefully helping!
