Two-way table lookup with interpolation

Hi, I'm plotting charts and want to avoid making massive excel tables to do a lookup. I've got two input parameters, one for A2:A28 and one for B1:M1. I've played around with LINEST but can't get it to work. I've attached the database. As an example, let's say my input for A2:A28 is 7.8 and my input for B1:M1 is 695, i need the output to say 10931.73075. Please give me some knowledge, I've got a few dozen more charts I'm going to have to implement this to and it will help me a ton.

Thanks!

Hi -

I took a quick look at your data. I just graphed one row and a couple of columns and it is not exactly linear data. So, using LINEST function will have quite a bit of error since you are using a line to approximate data with a curve in it. UNLESS, you take the logarithm of your data and variables to force it to be linear, and then you could create an equation using LINEST and take the inverse Log of the result to get the correct answer. I have done this before with about 5 or 6 small data sets. It's a fair amount of work. As onerous as it sounds, it may be simpler to do lookups rather than develop equations. Otherwise you are looking at writing an equation for each row and each column of data and probably still interpolating. There may be some multi-variate regression that could build one equation for the whole data set, but that's beyond my knowledge and ability.

(As an aside, to use the LINEST equation, you need to nest it inside the INDEX equation like this: =INDEX(LINEST($B$5:$M$5,$B$1:$M$1),1) The 1 at the end of the INDEX equation returns the slope of the line. Change that to 2 and INDEX function returns the Y Intercept. B5:M5 is row 5 of your data, and B1:M1 is row 1, which is the independent variable)

Back to the problem at hand, doing a straight line interpolation between your raw data points to find an intermediate result will have some error in it due to the curved nature of your data. I'm not sure how much error you can tolerate.

Sorry I don't have much more to offer.

Thank you for replying. I've found a workbook with the macro I need, but exporting the formula isn't possible. I can use the series function and manually interpolate everything, it's just very time consuming. What about a formula to find an exact match for x and then interpolate y? I'm using Dagra to plot all the lines and I'm able to extrapolate x at any variable, it's y that's messing everything up.