Custom VBA Functions with Nested Formulas

[jbiehl]

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!

 

[Peter_SSs]

Why do you want a vba function?

Would you consider one of these worksheet functions instead?

=IFERROR(LOOKUP(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),{0,10,20,30,40,50,60,70,80,90,100},{"0-9","10-19","20-29","30-39","40-49","50-59","60-69","70-79","80-89","90-100",""}),"")

=IFERROR(CHOOSE(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576))/10+1,"0-9","10-19","20-29","30-39","40-49","50-59","60-69","70-79","80-89","90-100"),"")

 

[mikerickson]

This formula will return which band the value in A1 is in.
=CHOOSE(1+FLOOR(A1,10)/10, "0-9","10-19","20-29","30-39","40-49","50-59","60-69","70-79","80-89","90-99")

If the OPs AVERAGEIFS is put in that formula instead of A1 it would calcuate that Average value only once.

Also, the interior IFERRORs can be omitted and let the outer IFERROR catch all those cases.

AVERAGEIF('Pre-Test'!$1:$1,"*"& LEFT(S$1,FIND(" ",S$1)-1)&"*", XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576))

LEFT(S$1,FIND(" ",S$1)-1) returns everything before the first space in S1.
So does =TRIM(LEFT(SUBSTITUTE(S$1, " ", REPT(" ",100)), 100)) , with only one occurance of S1

AVERAGEIF('Pre-Test'!$1:$1,"*"& TRIM(LEFT(SUBSTITUTE(S$1, " ", REPT(" ",100)), 100)) &"*", XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576))

 

[Peter_SSs]

This formula will return which band the value in A1 is in.
=CHOOSE(1+FLOOR(A1,10)/10, "0-9","10-19","20-29","30-39","40-49","50-59","60-69","70-79","80-89","90-99")

If the OPs AVERAGEIFS is put in that formula instead of A1 it would calcuate that Average value only once.

Also, the interior IFERRORs can be omitted and let the outer IFERROR catch all those cases.

Pretty much like my second formula?
BTW, FLOOR isn't required since, for example, CHOOSE(2.9, .... acts the same as CHOOSE(2, ....

LEFT(S$1,FIND(" ",S$1)-1) returns everything before the first space in S1.
So does =TRIM(LEFT(SUBSTITUTE(S$1, " ", REPT(" ",100)), 100)) , with only one occurance of S1

AVERAGEIF('Pre-Test'!$1:$1,"*"& TRIM(LEFT(SUBSTITUTE(S$1, " ", REPT(" ",100)), 100)) &"*", XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576))

Only 1 occurrence of S1, but two extra function calls and slower to calculate so I don't see any advantage in that change

 

[jbiehl]

Why do you want a vba function?

Would you consider one of these worksheet functions instead?

=IFERROR(LOOKUP(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576)),{0,10,20,30,40,50,60,70,80,90,100},{"0-9","10-19","20-29","30-39","40-49","50-59","60-69","70-79","80-89","90-100",""}),"")

=IFERROR(CHOOSE(AVERAGEIF('Pre-Test'!$1:$1,"*"&LEFT(S$1,FIND(" ",S$1)-1)&"*",XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576))/10+1,"0-9","10-19","20-29","30-39","40-49","50-59","60-69","70-79","80-89","90-100"),"")

Thank you so much for this!

 

[jbiehl]

This formula will return which band the value in A1 is in.
=CHOOSE(1+FLOOR(A1,10)/10, "0-9","10-19","20-29","30-39","40-49","50-59","60-69","70-79","80-89","90-99")

If the OPs AVERAGEIFS is put in that formula instead of A1 it would calcuate that Average value only once.

Also, the interior IFERRORs can be omitted and let the outer IFERROR catch all those cases.

AVERAGEIF('Pre-Test'!$1:$1,"*"& LEFT(S$1,FIND(" ",S$1)-1)&"*", XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576))

LEFT(S$1,FIND(" ",S$1)-1) returns everything before the first space in S1.
So does =TRIM(LEFT(SUBSTITUTE(S$1, " ", REPT(" ",100)), 100)) , with only one occurance of S1

AVERAGEIF('Pre-Test'!$1:$1,"*"& TRIM(LEFT(SUBSTITUTE(S$1, " ", REPT(" ",100)), 100)) &"*", XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576))

Thanks so much for your reply!

 

[Peter_SSs]

Thank you so much for this!

You're welcome. Thanks for the follow-up.

 

[jbiehl]

In case anyone is interested, I did end up creating the UDF below to replace "*"&LEFT(S$1,FIND(" ",S$1)-1)&"*", since that is used in almost every cell in that workbook.
XLOOKUP($A2,'Pre-Test'!$A:$A,'Pre-Test'!$1:$1048576) is also used in nearly every cell, but I haven't figured out a UDF for that yet.

Function FirstStrFlex(CellRef As Range) As String

Dim Result As String

Dim DelimPosition As Integer

DelimPosition = InStr(1, CellRef, " ", vbBinaryCompare) - 1

Result = Left(CellRef, DelimPosition)

FirstStrFlex = "*" & Result & "*"

End Function

 

[Peter_SSs]

If you are interested, you could also write your UDF above like this

Function FirstStrFlex(CellRef As Range) As String
  FirstStrFlex = "*" & Split(CellRef.Value)(0) & "*"
End Function

 

[jbiehl]

I am ALWAYS interested in simplifying things! Thanks!