Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 7th 06, 02:57 AM posted to microsoft.public.excel.worksheet.functions
Dave_AD
 
Posts: n/a
Default Interpolation with VLOOKUP

I'm trying to construct a workbook with the ability to interpolate between 2
values.
If we consider two columns of data, for example:

A B
100 97
110 120
135 135
157 166

What function or nested functions will return an "A" value if I input a "B"
value of 147 ?
--
Dave_DD

  #2   Report Post  
Old February 7th 06, 03:36 AM posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Interpolation with VLOOKUP

Hi!

Depends on what result you're after:

If you think the result shoud be 135:

D1 = 147

=INDEX(A1:A4,MATCH(D1,B1:B4))

If you think the result should be 157:

The table would need to be sorted in descending order

=INDEX(A1:A4,MATCH(D1,B1:B4,-1))

Biff

"Dave_AD" wrote in message
...
I'm trying to construct a workbook with the ability to interpolate between
2
values.
If we consider two columns of data, for example:

A B
100 97
110 120
135 135
157 166

What function or nested functions will return an "A" value if I input a
"B"
value of 147 ?
--
Dave_DD



  #3   Report Post  
Old February 7th 06, 11:05 AM posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default Interpolation with VLOOKUP

Easiest with a few helper cells. If you don't like that, you can always
compact them to one formula, but I think this way it is more clear what
happens.

I assume your table (without headers) in A1:B4 and the item to look for in
C1

In D1:
=MATCH(C1,A1:A4)
In E1:
=INDEX(A1:A4,D1)
In E2:
=INDEX(A1:A4,D1+1)
In F1:
=INDEX(B1:B4,D1)
In F2:
=INDEX(B1:B4,D1+1)

Your formula:
=E1+(C1-F1)/(F2-F1)*(E2-E1)

Gives 143.5161

Is that what you were looking for?

--
Kind regards,

Niek Otten


"Dave_AD" wrote in message
...
I'm trying to construct a workbook with the ability to interpolate between
2
values.
If we consider two columns of data, for example:

A B
100 97
110 120
135 135
157 166

What function or nested functions will return an "A" value if I input a
"B"
value of 147 ?
--
Dave_DD



  #4   Report Post  
Old February 9th 06, 12:24 AM posted to microsoft.public.excel.worksheet.functions
Dave_AD
 
Posts: n/a
Default Interpolation with VLOOKUP

Thanks for the help. It works like a charm.
--
Dave_DD


"Niek Otten" wrote:

Easiest with a few helper cells. If you don't like that, you can always
compact them to one formula, but I think this way it is more clear what
happens.

I assume your table (without headers) in A1:B4 and the item to look for in
C1

In D1:
=MATCH(C1,A1:A4)
In E1:
=INDEX(A1:A4,D1)
In E2:
=INDEX(A1:A4,D1+1)
In F1:
=INDEX(B1:B4,D1)
In F2:
=INDEX(B1:B4,D1+1)

Your formula:
=E1+(C1-F1)/(F2-F1)*(E2-E1)

Gives 143.5161

Is that what you were looking for?

--
Kind regards,

Niek Otten


"Dave_AD" wrote in message
...
I'm trying to construct a workbook with the ability to interpolate between
2
values.
If we consider two columns of data, for example:

A B
100 97
110 120
135 135
157 166

What function or nested functions will return an "A" value if I input a
"B"
value of 147 ?
--
Dave_DD






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP Limitations chris_manning Excel Worksheet Functions 2 August 9th 05 06:23 PM
Have Vlookup return a Value of 0 instead of #N/A Mr Mike Excel Worksheet Functions 4 May 25th 05 04:51 PM
help with interpolation and limit of interpolation uriel78 Excel Discussion (Misc queries) 0 February 17th 05 04:27 PM
vlookup data hidden within worksheet Excel Worksheet Functions 0 January 26th 05 12:09 PM
Vlookup info being used without vlookup table attached? Excel Worksheet Functions 0 January 25th 05 10:43 AM


All times are GMT +1. The time now is 01:49 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017