Oops! Typos!

Result Y-range: OFFSET(B7,0,-3,2,1) - E7:E8
Result X-range: OFFSET(B7,0,0,2,1) - B7:B8
....
Result: internal energy = -20.544 at 1.5 psia


"thermo" wrote:

I was surprised that Excel doesn't have a built in linear interpolation
routine. I was able to piece one together. I needed to interpolate data from
a table to calculate the internal energy at a given pressure. A linear curve
fit would not work because the data was not linear. I also needed to
calculate the change in internal energy as a function of pressure at constant
density and small interpolation errors could have resulted in large errors in
this quantity (du/dP)rho. I wanted to interpolate from the following data:
A B C D E
F
T(R) P(psia) D(lbm/ft3) v(ft3/lbm) u(Btu/lbm) h(Btu/lbm)
25.123 1.1174 4.8009 0.20829 -22.266 -22.223
25.296 1.1799 4.7958 0.20852 -21.948 -21.902
25.469 1.2451 4.7906 0.20874 -21.634 -21.586
25.642 1.313 4.7855 0.20896 -21.324 -21.273
25.815 1.3836 4.7803 0.20919 -21.017 -20.963
25.988 1.4572 4.7752 0.20942 -20.713 -20.656
26.162 1.5337 4.77 0.20964 -20.411 -20.352
26.335 1.6133 4.7648 0.20987 -20.111 -20.049
26.508 1.6959 4.7596 0.2101 -19.813 -19.747
26.681 1.7818 4.7544 0.21033 -19.516 -19.447
26.854 1.8709 4.7492 0.21056 -19.22 -19.147

Searching for the internal energy (u) at a pressure of ($P15 =) 1.5 psia,

=TREND(OFFSET(INDEX('Table'!$A$2:$I$12,MATCH(INT($ P15*10)/10,'Table'!$B$2:$B$12,1),5),0,0,2,1),OFFSET(INDEX( 'Table'!$A$2:$E$12,MATCH(INT($P15*10)/10,'Table'!$B$2:$B$12,1),5),0,-3,2,1),$P15,5)

MATCH returns the relative position of the pressure (P) of the look-up value
I'm seeking within the look-up array.
If match_type = 1, MATCH finds the largest value that is <= lookup_value.
Result: MATCH(INT($P15*10)/10, 'Table'!$B$2:$B$12, 1) - row = 7 (B7)

INDEX returns a reference of the cell at the intersection of row 7 and
column 5
Result: INDEX('Table'!$A$2:$I$12,7,5) - reference = E7

OFFSET returns a reference range that is a given number of rows and columns
from the given reference
Y-range: OFFSET(INDEX('Table'!A4:I12,7,5),0,-3,2,1) (2 rows high, 1
col wide)
X-range: OFFSET(INDEX('Table'!A4:I12,7,5),0,0,2,1) (2 rows high, 1
col wide)
Result Y-range: OFFSET(B7,0,-3,2,1) - E7:E8
Result X-range: OFFSET(B7,0,0,2,1) - B7:B8

TREND does a linear trend matching using the given data points, using the
least squares method.
TREND(E7:E8, B7:B8, 1.5, 1)
Result: internal energy = -20.544 at 1.5 psia

When I used the command above in it's entirety, I had to switch the y-range
and x-range to get the right value (not sure why). Checking the results of
INDEX and OFFSET was not easy because it's not easy to display cell
references on a spreadsheet, at least I couldn't figure out how to do it.

This complicated interpolation script has worked well for me. Surely
Microsoft can do better.

Kevin



"proinwv" wrote:

I am using the lookup function to look up a value in at table. When the
lookup is intermediate of two values in the lookup column, it returns the
lesser value from the second column.

For instance if I look up 130, and my table has values in column 1 of 100
and 200 (in the first and second rows) and corresponding values of 10 and 20
in the second column, then the value returned is 10 rather than an
interpolated value 13.

I cannot find any reference to interpolating in my help file (Excel 2000).

So, is it possible to interpolate?

Thanks!

--
PROINWV