If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




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 
Ads 
#2




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




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+(C1F1)/(F2F1)*(E2E1) 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




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+(C1F1)/(F2F1)*(E2E1) > > 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 > > > 
Thread Tools  
Display Modes  


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 05:27 PM 
vlookup data hidden within worksheet  Excel Worksheet Functions  0  January 26th 05 01:09 PM  
Vlookup info being used without vlookup table attached?  Excel Worksheet Functions  0  January 25th 05 11:43 AM 