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

Interpolating using the Lookup Function in Excel

1.

1. Sort your table in ascending order based on the values in the first column.

2. Determine the two values in the first column that bracket the value you want to interpolate.
3. Use the function to find the position of the lower bracket value in the first column of the table. For example, if the lower bracket value is 100 and it is in cell A2, the formula would be: 4. Use the function to retrieve the corresponding values in the second column for the lower and upper bracket values. For example, if the values in the second column are in cells B2 and B3, the formula would be: 5. Use the following formula to interpolate between the two bracket values: This formula uses linear interpolation to calculate the value for 130 based on the values for 100 and 200 in the first column. You can replace 130 with a cell reference if you want to interpolate for different values.

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

On Dec 14, 1:09 am, 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

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

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

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

On Dec 14, 1:56 am, proinwv wrote:
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

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" 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

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

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

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" wrote in message
...
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

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?

Try this amended formula...

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

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

Regards,

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



"proinwv" wrote in message
...
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?

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

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" wrote:

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

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.

Nice work, Lori

Best Regards,

Ron




"Lori" wrote in message
...
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" wrote:

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

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



"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

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

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.