Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    VLookup Interpolation Formula?? (Excel 2003)

    I have an array of corneal thicknesses with corresponding IOP (intra ocular pressure ) correction factors. e.g The thicker the cornea then the pressure reading must be corrected lower. However it rounds too far down. for instance on an IOP of 18, 614 thickness yields 14mm when it should be closer to 13 as corneal thickness 615 yields 13.
    Is there a way of making Vlookup interpolate the -4 to -5 correction to say -4.5 or something like that. I am attaching the file.
    Thanks
    Jerome

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

    Re: VLookup Interpolation Formula?? (Excel 2003)

    See attached modified version. I used some auxiliary values (which can be hidden), and changed the number format of the result to display one decimal place.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: VLookup Interpolation Formula?? (Excel 2003)

    Hans
    Really great really elegant, Many thanks
    Jerome

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: VLookup Interpolation Formula?? (Excel 2003)

    Hi Jerome,

    It appears to me as if the IOPs in your table are rounded off to the nearest whole number and that their 'true' values might be +/- a few decimal points out from that. The reason I say this is that the relationship between IOP values for K values appears to be linear except to the extent that the IOP values are rounded off. If so, and if the IOP values for K values 445 and 645 are exact integers, you might get a more accurate result in your calculation with a formula like:
    =A2+(545-B2)*0.07
    No interpolated lookups required.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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