How do I Iinterpolate from a data table? EXCEL

[AgentLA]

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.

 

[Clyde38]

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.

 

[dik]

Attached spreadsheet may help... modify as necessary.

Dik

 

[MechEng2005]

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

 

[MechEng2005]

Nevermind, here is a basic example to get you started...

http://files.engineering.com/getfil...f-4d1d-8623-34d4e4c87e15&file=Interpolate.xls

I hope that works... First time I've tried uploading

-- MechEng2005

 

[AgentLA]

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

 

[GregLocock]

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

Cheers

Greg Locock

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

 

[asixth]

Here's my attempt

 

[AgentLA]

Thanks for the help again.
This is the final product I came up with and it works. It's a really good time saver. Feel free to use it.

AgentLA

 

[MechEng2005]

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!

 

[MechEng2005]

And the Excel file is:

 

[asixth]

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

 

[Mala]

A similar problem was dealt with long ago in:

http://eng-tips.com/viewthread.cfm?qid=4962

Mala Singh
'Dare to Imagine'

 

[BobRar]

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

 

[RWF7437]

Try XLXTRAFun.

 

[RWF7437]

http://www.xlxtrfun.com/XlXtrFun/XlXtrFun.htm

 

[IRstuff]

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

 

[siretb]

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.