Returning Value Greater Than or Equal to Another Value
Returning Value Greater Than or Equal to Another Value
(OP)
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
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





RE: Returning Value Greater Than or Equal to Another Value
Thanks :)
-Brian
RE: Returning Value Greater Than or Equal to Another Value
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.
RE: Returning Value Greater Than or Equal to Another Value
RE: Returning Value Greater Than or Equal to Another Value
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
RE: Returning Value Greater Than or Equal to Another Value
-Brian
RE: Returning Value Greater Than or Equal to Another Value
CODE
1 1.1 10 =TREND(B1:B2,A1:A2,$A$8,TRUE)
2 2.3 20 =TREND(B2:B3,A2:A3,$A$8,TRUE)
3 9.8 30 =TREND(B3:B4,A3:A4,$A$8,TRUE)
4 12.4 40 =TREND(B4:B5,A4:A5,$A$8,TRUE)
5 15.2 50 50
6
7
8 2.4 =VLOOKUP(A8,A1:C5,3)
RE: Returning Value Greater Than or Equal to Another Value
RE: Returning Value Greater Than or Equal to Another Value
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
RE: Returning Value Greater Than or Equal to Another Value
=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.