Interpolate in table lookup

[proinwv]

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!

 

[Pete_UK]

Presumably you just want a straight linear interpolation between the
found item and the next value? If you use MATCH instead of VLOOKUP you
will get the (relative) row where the found item is, so adding 1 to
this will get you the next row. You can use INDEX to retrieve the two
values, and from those you can work out your interpolation factor.
INDEX can also return the values from the adjacent columns, allowing
you to factor those as appropriate.

Hope this helps.

Pete

 

[proinwv]

I believe I understand your method. I was hoping that I could find a function
that would do much of that manipulation for myself.

Hmmmmm

 

[Herbert Seidenberg]

Assume your data is fairly linear
and looks like this:
Xs Ys
246.2 40.7
54.6 1.9
146.3 19.0
102.5 8.8
296.6 49.2
205.0 30.6
Then the interpolated value for 120
can be found with this formula:
=FORECAST(120,Ys,Xs) = 13.9

 

[Pete_UK]

Well, you didn't post any details (like the formula you have, how your
data is laid out, the cells that you use etc), so I could only give
you the general approach.

Pete

 

[Lori]

for a point-to-point fit try:

=percentile(B:B,percentrank(A:A,130,20))

in the above example this gives y=12.9 for x=120. (Note: This assumes a
direct relationship i.e. both columns increase/decrease together).

 

[proinwv]

All,

My data is in two columns of X and Y but is not linear. (See below)

I tried the suggestion for FORECAST but it will not return the correct value
becasue of the non-lineararity.

I tried PERCENTILE, but it is not recognized. Possibly because of my version
being older (Excel 2000)?

Is there another method? My data is as shown below. Charting it shows the
non-lineararity to be significant.

X Y
2,500 60
4,200 100
21,000 500
42,000 1,000
84,000 2,000
126,000 3,000
168,000 4,000
210,000 5,000
420,000 10,000
630,000 15,000
840,000 20,000
1,050,000 24,000
1,260,000 28,000
1,470,000 31,000
1,680,000 34,000
1,890,000 37,000
2,100,000 40,000
2,520,000 44,000
2,940,000 48,000
3,360,000 52,000
3,780,000 56,000
4,200,000 60,000
5,040,000 68,000
5,880,000 75,000
6,720,000 82,000
7,560,000 90,000

 

[proinwv]

I forgot to add that it is acceptable to linearily interpolate between points.

 

[Ron Coderre]

With your posted data list in A1:B27

and...
D1: (the "Y" value to find in B2:B27......eg 3500)

This formula (in sections for readability) returns
the interpolated "X" value from A2:A27:

E1: =FORECAST(D1,OFFSET(A2:A27,MATCH(D1,A2:A27,1)+1,,2,1),
OFFSET(B2:B27,MATCH(D1,A2:A27,1)+1,,2,1))

Using that example, the formula returns: 147000

-------------------------------
Or....to interpolate the other column
D1: (the "X" value to find in A2:A27......eg 147000)

This formula (in sections for readability) returns
the interpolated "Y" value from A2:A27:

E1: =FORECAST(D1,OFFSET(B2:B27,MATCH(D1,A2:A27,1)+1,,2,1),
OFFSET(A2:A27,MATCH(D1,A2:A27,1)+1,,2,1))

In that case, the formula returns: 3500

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[proinwv]

Ron, thanks for the information.

I copied and pasted your equation into my spreadsheet, and placed the data
in the indicated columns to avoid any typo errors. I used the second example
as I only am solving for Y, with X being known.

What I find is that for the smaller values of X, the returned values of Y
are quite accurate, but a small error creeps in as X increases. This is
probably not surprising due to the increase in curvature at higher values.

However, I find that I cannot input any value of X greater than 5,879,999.
If I do the value returned is #DIV/0!

This I do not understand and I do need to use all of the table data.

Any thoughts here?

 

[Ron Coderre]

Try this amended formula...

E1:
=FORECAST(D1,OFFSET(B1,MATCH(D1,A2:A27,1),,2,1),OFFSET(A1,MATCH(D1,A2:A27,1),,2,1))

Does that fix the problem?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Herbert Seidenberg]

Here is another method, using graphing and
curve fitting to a 4th order polynomial.
Add Trendline > Type > Polynomial > Order > 4
Option > Display Equation on Chart >
Set Intercept = 0 (if applicable)
Click on the equation > Format Data Labels >
Number > Scientific > Decimal Places > 4
Copy the numbers manually to your data sheet
and name them like this:
k_1 -3.6395E-23
k_2 7.6570E-16
k_3 -5.8384E-09
k_4 2.7983E-02
Next to your X Y data, enter this formula and copy down:
=k_1*X^4+k_2*X^3+k_3*X^2+k_4*X
To find Y for 6,300,000
insert a row in the appropriate space and copy the formula.
Y=78,695

 

[Lori]

PERCENTILE has been a native Excel function since XL97 but you need to try a
value inside the data range for interpolation to make sense. Try:

=PERCENTILE(B:B,PERCENTRANK(A:A,D1,20))

to interpolate for y given x, e.g. D1 = 147000 gives 3500 as in Ron's
example. For a curve fit try:

=TREND(B2:B27,A2:A27^{1,2,3,4},D1^{1,2,3,4},0)

You can interchange A and B in the formulas to interpolate for x given a y
value.

 

[proinwv]

Ron your revised formula works great. It would not calculate the highest
value of X, so I added 1 to it and it works fine. (What's 1 out of 7,560,000
? . Nothing for an engineer anyway.)

I haven't studied your formula yet to truly understand it as I am pleased
that I will be able to use it to get my results.

Herb and Lori,,,

I also thank both of you. I haven't yet tried your solutions, but I will
work with them also, if only to learn something.

ALL
I really appreciate the effort that everyone has put into this post. It is
great to know that there are folks like yourselves out there. Happy holidays
to all.

 

[Ron Coderre]

Nice work, Lori

Best Regards,

Ron

PERCENTILE has been a native Excel function since XL97 but you need to try
a
value inside the data range for interpolation to make sense. Try:

=PERCENTILE(B:B,PERCENTRANK(A:A,D1,20))

to interpolate for y given x, e.g. D1 = 147000 gives 3500 as in Ron's
example. For a curve fit try:

=TREND(B2:B27,A2:A27^{1,2,3,4},D1^{1,2,3,4},0)

You can interchange A and B in the formulas to interpolate for x given a y
value.

 

[thermo]

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(B8,0,0,2,1) -> E7:E8
Result X-range: OFFSET(B8,0,-3,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

 

[thermo]

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

 

[Herbert Seidenberg]

Using the formula in Lori's post:
=TREND(u_Btu_lbm,P_psia^{1,2,3},1.5^{1,2,3},0)
=-20.5435
Notice that a 3rd order polynomial is used.
If you graph your data and then add the right trendline,
you will see that it fits the data well.