interpolate a value from tables (Excel 2000)

Topic

New Reply

hI LOUNGERS

i have a question regarding three tables I have and getting a value from them is the value does not exist.

my tables are related to temperature gradients.

I have a static temp at 32

Reply | Quote

Replies

Does the attached do what you want?

Reply | Quote

There is no excel function that will do exactly what you need. I couple of years ago I needed to interpolate on some complex (actually – unknown, but not known to be simple) curves, and I wrote a UDF to do it. The UDF takes arguments of the range of known X and Y values for the curve, the X value for which an interpolation is required, and an optional argument to determine how to deal with values of X outsdie the range of the known X values. I hope the treatment is evident from the UDF module. Note that you may need some error checking – for instance, if the calculated TG is 3.6 that could either represent an unacceptable value, and the sheet should throw up an error result, or (as it is set up now) it just “assumes” that it can calculate based on extending the values in the yellow, orange, and blue data columns linearly.

The attached file works by determining the nearest depth above and below the measured depth. It interpolates between the values in the yellow, orange and blue columns for each of these depths based on the calculated TG (see cellsI7:I8). In each case the “Known X” values are the TG’s identified at the top of the column, the “Known Y” values are supplied by an offset function that reads down into the table an appropriate number of rows, and the “X” value of the estimate is the calculated TG. As noted, if the TG is outside the values of 2.4 to 3.5 it will extrapolate linearly from the nearest data points. This returns a value for the depths above and below the measured depth for the calculated TG. The calculation then interpolates between these two values for the two depths based on the measured depth in cell I10.

Reply | Quote