Can you interpolate a non-linear set of values?

[Piffas]

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 se
of 10 values that are not necessarily linear on columns B1 to B11 (fo
example: 15,16,17,19,22,25,28,33,34,35,36). Then, another user enter
a value on another cell, for example: 20. I need to introduce
formula that can tell me what number 20 corresponds to in column A. I
other words, 20 would correspond to approximately 3.3 because 20 fall
between 19 and 22 in column B, which in turn falls between 3 and 4 i
column A. Is there any way I can get that ~3.3 with some kind o
formula

 

[MrShorty]

Excel doesn't have a built in linear interpolation function (some othe
spreadsheets like Quattro Pro do). I have seen complex workshee
functions posted to the newsgroups that will do a linear interpolation
but they are pretty complex functions. If you want to search th
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 t
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 smalle
than the smallest knownx or larger than the largest knownx (whether t
return an error value, or extrapolate based on the end interval o
what). My search algorithm assumes that the knownxs are in ascendin
order. You could use any search algorithm you like to locate th
interval containing newx

 

[Guest]

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.