Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    interpolate a value from tables (Excel 2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: interpolate a value from tables (Excel 2000)

    Does the attached do what you want?

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: interpolate a value from tables (Excel 2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •