Can you interpolate a non-linear set of values?

I am going crazy with the following problem. Can anyone help?

I have a range from 0 to 10 on columns A1 to A11. A user enters a set of 10 values that are not necessarily linear on columns B1 to B11 (for example: 15,16,17,19,22,25,28,33,34,35,36). Then, another user enters a value on another cell, for example: 20. I need to introduce a formula that can tell me what number 20 corresponds to in column A. In other words, 20 would correspond to approximately 3.3 because 20 falls between 19 and 22 in column B, which in turn falls between 3 and 4 in column A. Is there any way I can get that ~3.3 with some kind of formula?

Excel doesn't have a built in linear interpolation function (some other spreadsheets like Quattro Pro do). I have seen complex worksheet functions posted to the newsgroups that will do a linear interpolation, but they are pretty complex functions. If you want to search the newsgroups for one, feel free, but I can't build one.
One of the first things I did when I converted from QP to Excel was to write my own Interpolation function in VBA for use in my spreadsheets. Not very complicated, looks something like:

Function interpolation(knownys,knownxs,newx)
i=0
do
i=i+1
loop until knownxs(i)>newx
interpolation=(knownys(i)-knownys(i-1))/(knownxs(i)-knownxs(i-1))*(newx-knownxs(i-1))+knownys(i-1)
end function

Of course, you need to decide how to handle cases where newx is smaller than the smallest knownx or larger than the largest knownx (whether to return an error value, or extrapolate based on the end interval or what). My search algorithm assumes that the knownxs are in ascending order. You could use any search algorithm you like to locate the interval containing newx.

First use MATCH() to find the pair of values around the given value. Then
use FORECAST() to get a linear interpolation between 19 and 22.
--
Gary''s Student


"MrShorty" wrote:

>
> Excel doesn't have a built in linear interpolation function (some other
> spreadsheets like Quattro Pro do). I have seen complex worksheet
> functions posted to the newsgroups that will do a linear interpolation,
> but they are pretty complex functions. If you want to search the
> newsgroups for one, feel free, but I can't build one.
> One of the first things I did when I converted from QP to Excel was to
> write my own Interpolation function in VBA for use in my spreadsheets.
> Not very complicated, looks something like:
>
> Function interpolation(knownys,knownxs,newx)
> i=0
> do
> i=i+1
> loop until knownxs(i)>newx
> interpolation=(knownys(i)-knownys(i-1))/(knownxs(i)-knownxs(i-1))*(newx-knownxs(i-1))+knownys(i-1)
> end function
>
> Of course, you need to decide how to handle cases where newx is smaller
> than the smallest knownx or larger than the largest knownx (whether to
> return an error value, or extrapolate based on the end interval or
> what). My search algorithm assumes that the knownxs are in ascending
> order. You could use any search algorithm you like to locate the
> interval containing newx.
>
>
> --
> MrShorty
> ------------------------------------------------------------------------
> MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
> View this thread: http://www.excelforum.com/showthread...hreadid=486358
>
>