×
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

How do I Iinterpolate from a data table? EXCEL

How do I Iinterpolate from a data table? EXCEL

How do I Iinterpolate from a data table? EXCEL

(OP)
I have a data table which looks like this.

       219    220    221
82    0.3548 0.3540 0.3532
82.1  0.3531 0.3523 0.3515
82.2  0.3514 0.3506 0.3498

Lets say the left column is T, the top row is P, and the rest is D.

If I have calulated a T and P in my spreadsheet, how can I use those values to find the D. The trick is, the P will not always be an exact value. For example, the P may equal 219.4. If this is the case, how can I use that value and this table to interpolate the D.

RE: How do I Iinterpolate from a data table? EXCEL

Do you mean "interpolate" or do you want to "lookup" the value in the table.  If "lookup" you could use a combination of the VLOOKUP() and MATCH()functions.  I'm not sure that I understand exactly what you are after.

RE: How do I Iinterpolate from a data table? EXCEL

If you want to interpolate, I would still use "Lookup" and "Match" functions, it will just be more complicated. Unfortunately, I don't have the expertise to explain these functions well or concisely if you aren't familiar with them. Additionally, I typically just check Eng-Tips at lunch and don't have time to come up with a sample spreadsheet. Maybe if I get bored tonight at home I'll see what I can come up with.

Anyways, to interpolate for 219.4 you should just be able to use lookup to get the values for 219 and 220 (I'll call them 3 and 4 for example) and then take a weighted average:

3 + (4-3)*((219.4-219)/(220-219))

Of course, the values would be references, with 3 and 4 being received from lookup function.

-- MechEng2005

RE: How do I Iinterpolate from a data table? EXCEL

(OP)
Thanks, seeing both ways really helped. MechEng2205 your way is a lot more simple then I was trying, and it works perfect for what I'm doing.

AgentLA

RE: How do I Iinterpolate from a data table? EXCEL

If you get the excelelnt xlxtrfun it has all that stuff built in.

Cheers

Greg Locock

SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: How do I Iinterpolate from a data table? EXCEL

Ok, I enjoy finding solutions for things like this using Excel, so I toyed with it some more. See attached files for .pdf with some explaination and the excel file. This one allows for 2 dimensional interpolation, so in your case if both "T" and "P" were not exact values from the table (It is "A" and "B" in my notation).

Hope people find it useful!

 

RE: How do I Iinterpolate from a data table? EXCEL

Thanks MechEng2005,

I have never used the offset() function before, I believe for referencing a two dimensional matrix this function will require less storage than index().

RE: How do I Iinterpolate from a data table? EXCEL

Hi all,

I am new to this website so I dont know how everything works. I was reading all of your comments and I think you guys might be able to help me.

I have some data tha looks like this and I need to do some linear interpolation in excel so whenever I pick any value for T, I can get the "interpolated" Q, W, E and R. I tried the spreadsheet you guys posted but it is not working for me. Could anyone please help me?

Thank you very much for all of your help


T    q    w    e    r
 1,782      1.33      1.170      290.01     1.02
 1,772      1.33      1.170      290.01     1.02
 1,766      1.34      1.170      290.01     1.02
 1,761      1.34      1.170      290.01     1.02
 1,754      1.34      1.170      290.01     1.02
 1,748      1.35      1.170      290.01     1.02
 1,735      1.35      1.170      290.01     1.02
 1,717      1.36      1.170      290.01     1.02
 1,710      1.37      1.170      290.01     1.02
 1,700      1.50      1.168      284.27     1.02
 1,694      1.50      1.168      283.38     1.02
 1,689      1.51      1.168      282.52     1.02
 1,659      1.53      1.166      277.28     1.02
 1,644      1.55      1.164      274.64     1.02
 1,621      1.57      1.163      270.72     1.02
 1,612      1.58      1.162      269.20     1.02
 1,597      1.60      1.161      266.69     1.02
 1,589      1.61      1.161      265.30     1.02
 1,561      1.64      1.159      260.51     1.02
 1,542      1.66      1.157      257.16     1.02
 1,533      1.67      1.157      255.74     1.02
 1,517      1.69      1.155      252.94     1.02
 1,493      1.71      1.154      248.85     1.02
 1,482      1.73      1.153      246.95     1.02
 1,453      1.76      1.151      241.98     1.02
 1,421      1.81      1.149      236.44     1.02
 1,395      1.84      1.147      231.97     1.02
 1,386      1.85      1.146      230.48     1.02
 1,362      1.89      1.144      226.29     1.02
 1,348      1.91      1.143      223.94     1.02

 

RE: How do I Iinterpolate from a data table? EXCEL

Try XLXTRAFun.

RE: How do I Iinterpolate from a data table? EXCEL

Your data does not appear to be particularly smooth, so perhaps, the first thing to do is a clean regression with the model equations, then do interpolation.  Otherwise, there'll be a tendency to have spastic behavior from the interpolation alone.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

RE: How do I Iinterpolate from a data table? EXCEL

You may also look at the last post of
thread 770-234356 in the same forum
I submitted a rather general spreadsheet for 2 interpolation.
 

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