Custom VBA Functions with Nested Formulas

jbiehl

Board Regular
Joined
Jul 30, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows
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.
1598566828020.png


Thanks so much for reading... and hopefully helping!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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"),"")
 
Solution

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,906
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 30, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jul 30, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows
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!
 

jbiehl

Board Regular
Joined
Jul 30, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
If you are interested, you could also write your UDF above like this

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

jbiehl

Board Regular
Joined
Jul 30, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows
I am ALWAYS interested in simplifying things! Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,621
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top