# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  linear interpolation function in excel

## tskoglund

Given a series of x and y data, how can I interpolate to find y given a value
of x based only on a line between the two adjacent points in the data series?
This would be like the TREND() function, only I don't want regression of the
entire data series, just the (x,y) data points immediately above and below
the input x value.

This seems such fundamental and essential function that I've been
exasperated for years that Excel doesn't have it or directions how to find
it.  I realize that Excel calculates these individual lines every time it
connects the dots in a graph, but not having a function to achieve the same
result on a worksheet is puzzling.  Am I missing something?

----------


## Gary''s Student

I have good news.

The FORECAST() function, which is usually used to extrapolate outside a
range of known points also interpolates for a point between two known points.
Checkout help for the function syntax.

(this is an un-advertised feature of the function)
--
Gary''s Student


"tskoglund" wrote:

> Given a series of x and y data, how can I interpolate to find y given a value
> of x based only on a line between the two adjacent points in the data series?
>  This would be like the TREND() function, only I don't want regression of the
> entire data series, just the (x,y) data points immediately above and below
> the input x value.
>
> This seems such fundamental and essential function that I've been
> exasperated for years that Excel doesn't have it or directions how to find
> it.  I realize that Excel calculates these individual lines every time it
> connects the dots in a graph, but not having a function to achieve the same
> result on a worksheet is puzzling.  Am I missing something?

----------


## tskoglund

Gary"s Student, you do not have good news , but maybe you don't understand
the question being asked.  Like TREND, FORECAST uses all the data points in
the array and finds a best fit considering the entire array of data points.
Suppose A1:A4 = 1,2,2,1 (known y values) and B1:B4 = 1,2,3,4 (known x
values).  FORECAST(2.5,A1:A4,B1:B4) returns a y value of 1.5.  The function
everybody and their cousin wants - INTERPOLATE(2.5,A1:A4,B1:B4) - would
return a y value of 2.

Put these numbers in the spreadsheet and try it.  Hopefully this makes sense
to you so you'll spreading wrong information about interpolation on this
board.  I really wish you were right, though.

"Gary''s Student" wrote:

> I have good news.
>
> The FORECAST() function, which is usually used to extrapolate outside a
> range of known points also interpolates for a point between two known points.
>  Checkout help for the function syntax.
>
> (this is an un-advertised feature of the function)
> --
> Gary''s Student
>
>
> "tskoglund" wrote:
>
> > Given a series of x and y data, how can I interpolate to find y given a value
> > of x based only on a line between the two adjacent points in the data series?
> >  This would be like the TREND() function, only I don't want regression of the
> > entire data series, just the (x,y) data points immediately above and below
> > the input x value.
> >
> > This seems such fundamental and essential function that I've been
> > exasperated for years that Excel doesn't have it or directions how to find
> > it.  I realize that Excel calculates these individual lines every time it
> > connects the dots in a graph, but not having a function to achieve the same
> > result on a worksheet is puzzling.  Am I missing something?

----------


## Bernie Deitrick

Google groups is your friend.  Copy the code below into a code module, and
use as your describe.

HTH,
Bernie
MS Excel MVP

Function Interpolate(Xnow As Double, _
XRates As Range, YRates As Range) As Double
Application.Volatile
Dim hi As Long
Dim lo As Long
Count = XRates.Count
If XRates.Count <> YRates.Count Then
Interpolate = _
"Ranges need to be the same size"
Exit Function
End If

For hi = 1 To Count
If XRates(hi) > Xnow Then Exit For
Next
If hi > Count Then
Interpolate = YRates(Count)
Exit Function
End If
If hi = 1 Then
Interpolate = YRates(hi)
Exit Function
End If
lo = hi - 1
Interpolate = YRates(lo) + (Xnow - XRates(lo)) / _
(XRates(hi) - XRates(lo)) * _
(YRates(hi) - YRates(lo))
End Function



"tskoglund" <tskoglund@discussions.microsoft.com> wrote in message
news:DC33C422-3A45-4D3A-875F-5833350E3EC4@microsoft.com...
> Gary"s Student, you do not have good news , but maybe you don't understand
> the question being asked.  Like TREND, FORECAST uses all the data points
> in
> the array and finds a best fit considering the entire array of data
> points.
> Suppose A1:A4 = 1,2,2,1 (known y values) and B1:B4 = 1,2,3,4 (known x
> values).  FORECAST(2.5,A1:A4,B1:B4) returns a y value of 1.5.  The
> function
> everybody and their cousin wants - INTERPOLATE(2.5,A1:A4,B1:B4) - would
> return a y value of 2.
>
> Put these numbers in the spreadsheet and try it.  Hopefully this makes
> sense
> to you so you'll spreading wrong information about interpolation on this
> board.  I really wish you were right, though.
>
> "Gary''s Student" wrote:
>
>> I have good news.
>>
>> The FORECAST() function, which is usually used to extrapolate outside a
>> range of known points also interpolates for a point between two known
>> points.
>>  Checkout help for the function syntax.
>>
>> (this is an un-advertised feature of the function)
>> --
>> Gary''s Student
>>
>>
>> "tskoglund" wrote:
>>
>> > Given a series of x and y data, how can I interpolate to find y given a
>> > value
>> > of x based only on a line between the two adjacent points in the data
>> > series?
>> >  This would be like the TREND() function, only I don't want regression
>> > of the
>> > entire data series, just the (x,y) data points immediately above and
>> > below
>> > the input x value.
>> >
>> > This seems such fundamental and essential function that I've been
>> > exasperated for years that Excel doesn't have it or directions how to
>> > find
>> > it.  I realize that Excel calculates these individual lines every time
>> > it
>> > connects the dots in a graph, but not having a function to achieve the
>> > same
>> > result on a worksheet is puzzling.  Am I missing something?

----------


## Ron Rosenfeld

On Fri, 9 Sep 2005 12:42:29 -0700, tskoglund
<tskoglund@discussions.microsoft.com> wrote:

>Given a series of x and y data, how can I interpolate to find y given a value
>of x based only on a line between the two adjacent points in the data series?
> This would be like the TREND() function, only I don't want regression of the
>entire data series, just the (x,y) data points immediately above and below
>the input x value.
>
>This seems such fundamental and essential function that I've been
>exasperated for years that Excel doesn't have it or directions how to find
>it.  I realize that Excel calculates these individual lines every time it
>connects the dots in a graph, but not having a function to achieve the same
>result on a worksheet is puzzling.  Am I missing something?


If X is your range of X's, Y your range of Y's, and NewX is the new value,
then:

=TREND(OFFSET(Y,MATCH(NewX,X),0,-2),OFFSET(X,MATCH(NewX,X),0,-2),NewX)

This formula assumes your X's are in ascending order.

This formula will give an #N/A error if NewX is less than the minimum X.

This formula will give a #VALUE! error if NewX is equal to or greater than the
maximum X.

These errors are in accord with your specifications that the New X be "between"
two adjacent points, but I would eliminate the error when it matches the
maximum X with this addition to the above formula:

=IF(NewX=MAX(X),INDEX(Y,MATCH(NewX,X)),
TREND(OFFSET(Y,MATCH(NewX,X),0,-2),
OFFSET(X,MATCH(NewX,X),0,-2),NewX))


--ron

----------

