vlookup & Extrapolation

[alawi]

I have the following columns

Va2 177
V3 216.37
V3/rootT3 11.984
Q3 ?


V3/rootT3=11.984 which is not in the table
I need to retrieve the Q3 from a long table looks as following


V / Root.T Q
11.888 0.03450
11.924 0.03456
11.960 0.03463
11.996 0.03469
12.032 0.03475



As you can see 11.984 dose not exists
I need to extrapolate between any tow columns in V / Root.T and return the
answer Q to Q3 cell


How can I do that? Please

 

[Mike H]

Hi,

I have no idea what Va2 or V3 has got to do with your question.

Try these for looking up where no exact match exists.

To find a lower value

=VLOOKUP(D1,A2:B6,2,TRUE)


To find the higher value

=MIN(IF(A2:A6>=D1,B2:B6))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


Mike

 

[Shane Devenshire]

Hi,

Linear extrapolation?

I don't have time to finish this right now but here is a start. If your
known X's are in C1 and C2 and the new X is in C3 and the known Y's are in D1
and D2

=((C3-C1)/(C2-C1))*(D2-D1)+D1

=TREND(D12,C1:C2,C3)

You can combine this in an IF with VLOOKUP and possibly MATCH.

 

[Bernd P]

Hello,

TREND is outright dangerous, if you apply it on all your points, for
example:
Enter into A1:B4
1 -100000
2 4
3 5
4 -100000
Then =TREND(B1:B4,A1:A4,2.5) would result in -49997.75 and not 4.5
Or its very complex to use because you need to find the two boundary
points in your data set which you want to feed into TREND.

I suggest to use my UDF Interp:
http://www.sulprobil.com/html/interpolate.html

Regards,
Bernd