×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Returning Value Greater Than or Equal to Another Value
3

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

RE: Returning Value Greater Than or Equal to Another Value

(OP)
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

RE: Returning Value Greater Than or Equal to Another Value

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.

RE: Returning Value Greater Than or Equal to Another Value

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

RE: Returning Value Greater Than or Equal to Another Value

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

RE: Returning Value Greater Than or Equal to Another Value

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

-Brian

RE: Returning Value Greater Than or Equal to Another Value

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

CODE

    A     B       C
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)
The TREND formulae give the interpolation.  Your input x value goes in A8. The vlookup formula in B8 returns the interpolant.

RE: Returning Value Greater Than or Equal to Another Value

Very clever cummings.

RE: Returning Value Greater Than or Equal to Another Value

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

RE: Returning Value Greater Than or Equal to Another Value

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources