Double Interpolation of Tabular Data

[djack77494]

In Excel, I have a table of data giving fluid densities (rho) with temperatures (T) and pressures (P) as parameters. In other words, my table looks like this:

DENSITY vs. T,P
P1 T1 Rho1,1
T2 Rho1,2
T3 Rho1,3
P2 T1 Rho2,1
T2 Rho2,2
T3 Rho2,3
etc.

My problem is that I want to estimate the densities at in between values of temperature and pressure and to use the estimated values in subsequent calculations. I could probably do it if I had a single interpolation, but it's looking extremely awkward. Anyone have a solution to this? (I'd prefer a non-VBA solution but would gladly accept anything that works.) Thanx.

 

[MintJulep]

Assuming that everything is linear...

If you were to graph your table you would have pressure on the X axis, Density on the Y axis and have three curves for temperature.

Imagine you want the density at P1.5, T1.5

You would enter your graph at P=1.5 on the X axis, go up to the T=1 curve and put a big black dot there. Then you would go up to the T=2 curve and put a dot there. Then you would draw a line between the two dots.

Next you would say, ok, T=1.5 is half way along that line.

So do your interpolation like you would if you were looking at the graph.

 

[Onda]

Here a simple excel file with a UDF macro for linear interpolation.
the excel file make a first linear interpolation on pressure and a second linear interpolation on temperature.
Should be better developed for upper bound, because now when p=pmax or t=tmax there is an error.
On C2 and C3 you should input the desired Pressure and temperature.
On A25:C37 there is the table.


Hope this help!

Onda

 

[DavidOC]

Interpolation in excel is a pain. It can be done using various lookup functions. The only way I know how to do it is to use index and match functions to find the data around the point you are interested in and then do the interpolation calculation.

For steam tables you can download a free excel add-in from

http://www.cheresources.com/iapwsif97.shtml.

Also look here

http://www.techwareeng.com/products.htm

for steam tables and an excel interpolation functions (I have not used these).

 

[GregLocock]

2d interpolation is handled beautifully by the free add-in xlxtrfun

Cheers

Greg Locock

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

 

[SteveGregory]

You could introduce a new column that would give a calculated value between the other two columns to take care of one variable. Then use lookup values between the rows to calculate the interpolated value.

 

[IDS]

The file IP.xls can be downloaded from:

http://newtonexcelbach.wordpress.com/2008/08/10/intersections-interpolations-and-rotations/

It includes one and two way interpolation functions (linear and logarithmic) as well as a number of functions for finding intersection points, and rotating and translating coordinates.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[dik]

See my example in thread770-222940

Dik

 

[djack77494]

"2d interpolation is handled beautifully by the free add-in xlxtrfun" - GregLocock

Greg, can you provide a link or otherwise tell us how to get to this add-in. Thanks.

 

[GregLocock]

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

Cheers

Greg Locock

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

 

[asixth]

I had the same problem the other day when I was calculating drag co-efficients on bridge superstructues. I am sure that any of the above methods work but this is how I went about the problem:

I wrote myself a VBA code

Function lin_interp(x, x1, y1, x2, y2)
lin_interp = (x - x1) / (x2 - x1) * (y2 - y1) + y1
End Function

This will give you a one-way linear interpolation if you like. So now you can apply this equation as many times as necessary to interpolate your in-between values.

For example, you know P1, P2, T1, T2, rho1, rho2 and you want to calculate rho at P1.5 and T1.5. You first run your linear interpolation to find the intermediate rho value at P1.5 and T1:

lin_interp(P1.5,P1,rho1(at T1),P2,rho2(at T1))
# This will calculate rho at P1.5 and T1.

Then you will need to calculate the intermediate rho value at P1.5 and T2:

lin_interp(P1.5,P1,rho1(at T2),P2,rho2(at T2))
# This will calulate rho at P1.5 and T2.

Now it is simply finding rho at P1.5 and T1.5.

lin_interp(T1.5,T1,rho(P1.5,T1),T2,rho(P1.5,T2)).

I haven't had a chance to look at the other solutions that have been posted but it sounds like they are easier than what I have posted.

 

[MechEng2005]

See my example in the thread that dik has posted a link to as well! <wink>

-- MechEng2005

 

[rb1957]

but isn't the relationship PV/T = k, ie P/T = kp.
so k = P1/(T1p1) and p2 = P1/(kT2)

 

[rb1957]

oops ... should have read the OP "fluid", so the gas law obviously doesn't apply ... dang

 

[siretb]

For quick and dirty 2D interpolation I like the Shepard approach.

One does not need a regular mesh.

Each reference point has a weight inversely proportional to its distance to the point where the interpolation is desired.
Mathematically It's not very smart, and the method has serious drawbacks. Practically it works fine for most engineering work. Do not extrapolate.

PLease find an Excel spreadsheet.

 

[briand2]

I suppose you could play around with this...

http://homepage.ntlworld.com/brian.doherty/Interpolation_v2.xls

Brian