Returning Value Greater Than or Equal to Another Value 3

[Stringmaker]

Hi All,

I have a dilemma of sorts on my hands. I have a series of points the compose a piecewise function. Say I have the following points data as a column vectors

X Y
1.1 10
2.3 20
9.8 30
12.4 40
15.2 50

What I need to do is interpolate linearly inbetween these values for other given values. Say for instance I have a value, Xi=5.4. I want to interpolated between X=2.3 and X=9.8 to determine Yi(5.4). My data is in acscending order as shown above and I have been successful in getting the lower value by using the lookup function, for instance LOOKUP(5.4,A1:A5,A1:A5) would return 2.3 in my case. But I am having trouble obtaining the upper value of 9.8 since my data is ascending. Can anyone help me with this? Ultimately I'd like to if possible accomplish the entire interpolation in once cell. Is this possible? I'm stuck now.

Thanks in advance!
-Brian

 

[Stringmaker]

Sorry I just realized I have a couple spelling errors...in the second sentence 'the' should be that* and at the very end 'once cell' should be one cell*. I've been staring at excel too long.

Thanks
-Brian

 

[GregLocock]

there's the proper way using index and match, another way I suspect using offset, and then there's my way

Creat a joggled x column called x1

x1 X Y
-1 1.1 10
1.1 2.3 20
2.3 9.8 30
9.8 12.4 40
12.4 15.2 50

Now when you do a lookup for (say) 10.5 based on x1 you'll get say 40, and from x you'll get 30. you'll also need 9.8 and 12.4 obviously



Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[MintJulep]

=INDEX(A1:B5,MATCH(VLOOKUP(2.4,A1:B5,1,TRUE),A1:A5)+1,2)

 

[yogia]

Hi stringmaker:

Try the following formula with your posted data ...

=INDEX(A1:A5,MATCH(1,INDEX(1/(A1:A5>=5.4),0),0))

this will give the result 9.4

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[Stringmaker]

Thanks for the replies! Mint Julep I ended up using the solution you provided me with.

-Brian

 

[cummings54]

instead of looking up the points, look up the formula that represents the line segment joining the points as follows:

[red]    A     B       C[/red]
[red]1[/red]  1.1	10	=TREND(B1:B2,A1:A2,$A$8,TRUE)
[red]2[/red]  2.3	20	=TREND(B2:B3,A2:A3,$A$8,TRUE)
[red]3[/red]  9.8	30	=TREND(B3:B4,A3:A4,$A$8,TRUE)
[red]4[/red] 12.4	40	=TREND(B4:B5,A4:A5,$A$8,TRUE)
[red]5[/red] 15.2	50	50
[red]6[/red]
[red]7[/red]
[red]8[/red]  2.4 =VLOOKUP(A8,A1:C5,3)

The TREND formulae give the interpolation. Your input x value goes in A8. The vlookup formula in B8 returns the interpolant.

 

[MintJulep]

Very clever cummings.

 

[yogia]

Hi stringmaker:

Sorry, there is a typo in the formula in my post of 23-Feb-07 11:12.

The formula I had intended to post is ...

=INDEX(A1:A5,MATCH(1,INDEX(1/(A1:A5>2.3),0),0))




Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[GregLocock]

cummings, you have a twisted mind, that is great. Now, if we change C2 to

=TREND(B1:B3,A1:A3,$A$8,TRUE)

Then I'm even happier

Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.