I think I have finally got enough reverse engineered that I can comment (anything you can do or say to make it easier to reverse engineer helps us help you). I'm still not sure exactly what kind of programming approach you want to take. It still looks to me like a multi-D interpolation problem. 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). Assuming that we can simply apply the 1D interpolation algorithm in your existing UDF multiple times (first in the x direction and then the y direction or vice-versa), then I would expect the overall procedure to look like this (again, I'm not sure exactly how you want to program this, but it can be done either in VBA or in the spreadsheet. if it matters, my preference has usually been to leave my 1D interpolation procedure alone and do the multi-D parts in the spreadsheet):
1) Need to identify and divide the existing "flat" tables into blocks that represent constant x1 values. In the spreadsheet, this could be as "easy" as rearranging the data (change how you input the tabular data) tables into something that makes it easier to access individual blocks. In VBA, this would mean breaking up the input tables into smaller arrays. Filtering tools or INDEX()/OFFSET() + MATCH() combinations (whether in the spreadsheet or in VBA) might be useful here.
2) Perform the 1D interpolation in one direction (it looked easier to me to first interpolate z (ExtA in Table 1, ExtPressB in Table 2) as a function of y (LDo in Table 1, ExtPressA in Table2) at all unique values of x (Dot in Table 1, ExtPressTemp in Table 2)). In the spreadsheet, this would be in a helper block of cells, in VBA, this would be a new array(s).
3) Perform the 1D interpolation in the second direction using the result of step 2.
Since we already have a procedure that can steps 2 and 3, the main programming step is how to break up the tables into the necessary subtables and whether or not you want to use spreadsheet tools or VBA tools to do that. Once we have done that, it is 2 applications of our existing 1D interpolation procedure. How would you like to proceed on this?
Bookmarks