Interpolating data from a table

[Chris Hempsall]

Hi everybody,

I have a table, "properties of superheated vapour" in the following
format:

Temp(K) Pressure (bar)
13.5 14.0 14.5 15

350 351.53 350.92 350.31 349.69

352 353.17 352.58 351.98 351.37

354 354.82 354.23 353.64 353.05

The real table is much bigger than this but hopefully this gives the
general idea.

The figures in the table are enthalpy. So if I want the figure for
enthalpy at 352K and 14 bar I can read off the table a value of 352.58
kJ/kg.

What I want to do is to have two cells separate from the main table
where I input other values. So for instance

Temp = 352.62
Pressure = 14.224
Enthalpy = ??? (would like excel to interpolate and provide the
enthalpy here)

The enthalpy at these conditions is somewhere around the intersection
of the four nearest values. If the increase in values is linear does
anybody have any idea if there is a worksheet function that would
accomplish this. Alternatively can anybody help me out with a formula
to do what I want.

Many thanks in advance,


Chris

 

[Tom Ogilvy]

From a previous post by J.E. McGimpsey: See bottom of article for a
formula:

From: "J.E. McGimpsey" <[email protected]>
Mail-Copies-To: nobody
Subject: Re: Excel Function to Interpolate a List
References: <[email protected]>
User-Agent: MT-NewsWatcher/3.3b1 (PPC Mac OS X)
Date: Thu, 18 Sep 2003 10:57:14 -0600
Message-ID: <[email protected]>
Newsgroups: microsoft.public.excel.programming
NNTP-Posting-Host: 65.102.75.40
Lines: 1


One way:

First - note that you're not really interpolating based on the range
of data - you're simply finding the linear interpolation between the
two closest values. The result you're seeking, 11, certainly
wouldn't fall on a smooth trendline given your data.


Public Function FindX(xRange As Range, yRange As Range, _
y As Range, Optional bAscend As Boolean = True) As Double
Dim maxX As Double
Dim maxY As Double
Dim minX As Double
Dim minY As Double
Dim matchPoint As Long
Dim matchType As Long

If bAscend Then
matchType = 1
Else
matchType = -1
End If
matchPoint = Application.Match(y, yRange, matchType)
If yRange(matchPoint) = y Then
FindX = xRange(matchPoint)
Else
maxX = xRange(matchPoint - bAscend).Value
minX = xRange(matchPoint - (Not bAscend)).Value
maxY = yRange(matchPoint - bAscend).Value
minY = yRange(matchPoint - (Not bAscend)).Value
FindX = (maxY - y) / (maxY - minY) * (maxX - minX) + minX
End If
End Function

Call as

= FindX(B37:B45,C37:C45,E27,FALSE)

Note that I made the default for y values in ascending order. The y
values must be sorted.

Note that you can accomplish the same thing with worksheet formulae:

=(INDEX(Ys,MATCH(Y,Ys,-1))-X)/(INDEX(Ys,MATCH(Y,Ys,-1))-(INDEX(Ys,MAT
CH(Y,Ys,-1)+1)))*(INDEX(Xs,MATCH(Y,Ys,-1))-INDEX(Xs,MATCH(Y,Ys,-1)+1)
)+INDEX(Xs,MATCH(Y,Ys,-1)+1)

where Xs, Ys, and Y are your x-range, y-range and unknown y.