Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
help with interpolation and limit of interpolation | Excel Discussion (Misc queries) | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |