Function similar to TREND that interpolates lin. between data poin

Dear fellow Excel users out there,

does anyone know if there is a function in Excel similar to TREND that
interpolates linearly between data points instead of returning a linear trend
for the hole array.

Example:

Known x's Known y's
1 2
2 5
3 6
4 8

=TREND(known_y's;known_x's;1.5) delivers 3.35 but I need a function that
delivers 3.5 (because 1.5 is in the middle between 1 and 2, and 3.5 is in the
middle between 2 and 5, i.e. the linear interpolation between the two data
points).

Try using just this part of your table for known x and known y.

known x known y
1 2
2 5

If your table is in A1:B4, and D1 = 1.5, try:

=IF(ISNA(MATCH(D1,Sheet3!A1:A4,0)),TREND(OFFSET(Sheet3!B1,MATCH(D1,Sheet3!A1:A4,1)-1,0,2,1),OFFSET(Sheet3!A1,MATCH(D1,Sheet3!A1:A4,1)-1,0,2,1),D1),VLOOKUP(D1,Sheet3!A1:B4,2,0))

"Erik Thorsteinsson" wrote:

> Dear fellow Excel users out there,
>
> does anyone know if there is a function in Excel similar to TREND that
> interpolates linearly between data points instead of returning a linear trend
> for the hole array.
>
> Example:
>
> Known x's Known y's
> 1 2
> 2 5
> 3 6
> 4 8
>
> =TREND(known_y's;known_x's;1.5) delivers 3.35 but I need a function that
> delivers 3.5 (because 1.5 is in the middle between 1 and 2, and 3.5 is in the
> middle between 2 and 5, i.e. the linear interpolation between the two data
> points).

Note I inadertently left the worksheet reference in (Sheet3). Change or
remove as needed.

"JMB" wrote:

> Try using just this part of your table for known x and known y.
>
> known x known y
> 1 2
> 2 5
>
> If your table is in A1:B4, and D1 = 1.5, try:
>
> =IF(ISNA(MATCH(D1,Sheet3!A1:A4,0)),TREND(OFFSET(Sheet3!B1,MATCH(D1,Sheet3!A1:A4,1)-1,0,2,1),OFFSET(Sheet3!A1,MATCH(D1,Sheet3!A1:A4,1)-1,0,2,1),D1),VLOOKUP(D1,Sheet3!A1:B4,2,0))
>
> "Erik Thorsteinsson" wrote:
>
> > Dear fellow Excel users out there,
> >
> > does anyone know if there is a function in Excel similar to TREND that
> > interpolates linearly between data points instead of returning a linear trend
> > for the hole array.
> >
> > Example:
> >
> > Known x's Known y's
> > 1 2
> > 2 5
> > 3 6
> > 4 8
> >
> > =TREND(known_y's;known_x's;1.5) delivers 3.35 but I need a function that
> > delivers 3.5 (because 1.5 is in the middle between 1 and 2, and 3.5 is in the
> > middle between 2 and 5, i.e. the linear interpolation between the two data
> > points).