Interpolate in table lookup

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

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?