![]() ![]() =LAMBDA(searchx,searchy,rangeX,rangeY,valuerange,LET(valuerange,INDEX(valuerange,1,0),L_InterpolateX(searchx,rangeX,OFFSET(valuerange,MATCH(XLOOKUP(searchy,rangeY,rangeY,-1),rangeY,0)-1,0))+IF(XLOOKUP(searchy,rangeY,rangeY,-1)=searchy,0,(searchy-XLOOKUP(searchy,rangeY,rangeY,-1))/(XLOOKUP(searchy,rangeY,rangeY,1)-XLOOKUP(searchy,rangeY,rangeY,-1))*(L_InterpolateX(searchx,rangeX,OFFSET(valuerange,MATCH(XLOOKUP(searchy,rangeY,rangeY,1),rangeY,0)-1,0))-L_InterpolateX(searchx,rangeX,OFFSET(valuerange,MATCH(XLOOKUP(searchy,rangeY,rangeY,-1),rangeY,0)-1,0)))))) =LAMBDA(searchX,searchY,rangeX,rangeY,rangeValue,L_InterpolateX(searchX,rangeX,OFFSET(rangeV,MATCH(XLOOKUP(searchY,rangeY,rangeY,-1),rangeY,0)-1,0))+IF(XLOOKUP(searchY,rangeY,rangeY,-1)=searchY,0,(searchY-XLOOKUP(searchY,rangeY,rangeY,-1))/(XLOOKUP(searchY,rangeY,rangeY,1)-XLOOKUP(searchY,rangeY,rangeY,-1))*(L_InterpolateX(searchX,rangeX,OFFSET(rangeV,MATCH(XLOOKUP(searchY,rangeY,rangeY,1),rangeY,0)-1,0))-L_InterpolateX(searchX,rangeX,OFFSET(rangeV,MATCH(XLOOKUP(searchY,rangeY,rangeY,-1),rangeY,0)-1,0))))) '2 intermediate results after interpolation on Xīased on L_InterpolateX, so you need to copy in both functions. PY2 = WorksheetFunction.Match(Y2, YRange, 0) PY1 = WorksheetFunction.Match(Y1, YRange, 0) ![]() PX2 = WorksheetFunction.Match(X2, XRange, 0) PX1 = WorksheetFunction.Match(X1, XRange, 0) Y2 = WorksheetFunction.XLookup(Y, YRange, YRange,, 1) Y1 = WorksheetFunction.XLookup(Y, YRange, YRange,, -1) X2 = WorksheetFunction.XLookup(X, XRange, XRange,, 1) X1 = WorksheetFunction.XLookup(X, XRange, XRange,, -1) 'Determine data table points around searchvalues If (X WorksheetFunction.Max(XRange)) Or (Y WorksheetFunction.Max(YRange)) Thenĭim X1, X2, Y1, Y2, PX1, PX2, PY1, PY2, V1, V2, V3, V4, FX, FY, C12, C34 'if X or Y fall outside the table, the function result is an error value Public Function InterpolateXY(ByRef X As Range, ByRef Y As Range, ByRef XRange As Range, ByRef YRange As Range, ByRef ValueTable As Range) As Double In MS Excel, press Alt+F11 to open the Visual Basic Editor (Windows). Bilinear interpolation - VBA code to be put in a module ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2023
Categories |