UDF for interpolation

to hopefully build on this thread: https://www.excelforum.com/excel-for...ml#post5679345

I'm looking to create a User Designed Function that will figure out 'A' and 'B' for the attached spreadsheet, and if possible for 'ta', which is a goal seek. There is a interpolation module in the sheet (not working), but I'd like the function to be able to look up the table and then find 'LowSigma', 'LowSigma2', and others that are based on consistent locations of the table.

for example: when looking up 'A', it would be great to be able to put in @InterpVol("Table1",O12,O13) and it finds the rest.


Sample1 of the attached files is the original way I was doing it so you can see the math.

MrShorty: I want to replace the formula in 'O14' & 'O15' where I would just call a UDF. something like @InterpVol("Table1",O14,O15,"ExtA") for 'O14' and the UDF would do the rest. Then a separate UDF for 'O15', something like @FindB("Table2",....) for it's variables.

I'm not sure how to manipulate the UDF in the workbook I attached to do what I what. Table1 & Table2 are actually in another workbook (it will be open).

I notice that the interpolation grid represented by the two tables is a bit irregular (the x and y columns are not consistently spaced), which, I know can impact the choice of the interpolation algorithm (though I do not have the expertise to know exactly how in all cases).

Yes, and Table 2 actually has a lot more 'ExtPressTables' types (CS-1, CS-2, HA-1, etc...) where some of the 'ExtPressTemp' fields start at less than 300 (i.e. 100, or 200), but the way it's set up in the 'Sample1' workbook gives me what I want (comparing it to samples I'm working off)

I'd like do do it in VBA

Essentially Code to do 'A', which is currently:

=LET(d,O12,l,O13,do,DOT,ld,LDo,dlh,XLOOKUP(d,do,do,,{-1,1}),
elh,BYCOL(dlh,LAMBDA(ll,TREND(XLOOKUP(l,ld/(do=ll),ExtA,,{-1;1}),XLOOKUP(l,ld/(do=ll),ld,,{-1;1}),l))),
TREND(elh,dlh,d))

and code to do 'B', which is currently:

=LET(t,F3,c,F6,ex,O14,eta,ExtPressTables,ete,ExtPressTemp,ea,ExtPressA,eb,ExtPressB,tlh,XLOOKUP(t,ete/(eta=c),ete,,{-1,1}),
eblh,BYCOL(tlh,LAMBDA(tt,LET(x,ea/(eta=c)/(ete=tt),TREND(XLOOKUP(ex,x,eb,,{-1;1}),XLOOKUP(ex,x,ea,,{-1;1}),O14)))),
TREND(eblh,tlh,t))

Ideally, instead of using named column ranges, I'd like to be able to just reference just the table, the variables and what I'm looking for because I want to be able to use it on different table, depending on if it uses 1, 2 or 3 columns to find variables in.

I would have something like: @Int2D(Table1,O12,O13,ExtA) where the 'Int2D' is in place of the current function InterpVol, and is using two columns to find and interpolate the 'ExtA' column of Table1. And @Int3D(Table2,F3,F6,O14,ExtPressB) to find the 'B' value from Table2

If it was a formula, this is how it would be based off of the above potential UDF callout @Int2D. Notice that I don't callout for ExtPressA, ExtPressB, or ExtPressTemp, I just grab the column numbers from the table.

=LET(d,O12,l,O13,do,INDEX(Table1,,1),ld,INDEX(Table1,,2),dlh,XLOOKUP(d,do,do,,{-1,1}),
elh,BYCOL(dlh,LAMBDA(ll,TREND(XLOOKUP(l,ld/(do=ll),INDEX(Table1,,MATCH("ExtA",INDEX(Table1,1,0),0)),,{-1;1}),XLOOKUP(l,ld/(do=ll),ld,,{-1;1}),l))),
TREND(elh,dlh,d))

I realize I suck at explaining things, good thing I'm not a teacher, but I hope it is clearer.

MrShorty: I appreciate the input. While I had my fingers crossed it was something someone could figure out, I did take the formula and put it into my sheets 'Format' macro. So when I have 'LookupA(Dot,LDo), or whatever variables within the brackets, it finds the variables on my sheet and puts the cell locations into the formula.


Row 'G': LookupA(Dot,LDo)
Row 'N': LookupA(43,1.86)
Row 'O': 0.0026227

Row 'G' I type in as a formula and rows 'N' and 'O' are generated when I 'Format'
where:
- LookupA calls out the macro name
- Dot is the first variable i search for on my sheet, which cell location gets assigned to DOT
- LDo second variable I search for, gets cell location assigned to LDO.
then I put the formula into Row 'O', and row 'N' gets the numerical values added.

Range("O" & i).Formula2 = "=LET(d," & DOT & ",l," & LDO & ",do,INDEX(Library.xlam!IIDTableG,,1),ld," _
                                        & "INDEX(Library.xlam!IIDTableG,,2),dlh,XLOOKUP(d,do,do,,{-1,1}),elh," _
                                        & "BYCOL(dlh,LAMBDA(ll,TREND(XLOOKUP(l,ld/(do=ll),INDEX(Library.xlam!IIDTableG,," _
                                        & "MATCH(" & QTS & "A" & QTS & ",INDEX(Library.xlam!IIDTableG,1,0),0)),,{-1;1})," _
                                        & "XLOOKUP(l,ld/(do=ll),ld,,{-1;1}),l))),TREND(elh,dlh,d))"
                                     
            Range("O" & i).NumberFormat = "0.0000000"
            Range("N" & i).Formula2 = "=" & QTS & "LookupA(" & QTS & AndSymbl & "Round(" & DOT & ", 3)" _
                                            & AndSymbl & QTS & "," & QTS & AndSymbl & "Round(" & LDO & ", 3)" _
                                            & AndSymbl & QTS & ")" & QTS

Not as fancy, but does the trick. I just had to make a few of them to handle the different number of variables.