Function similar to TREND that interpolates lin. between data poin

[Erik Thorsteinsson]

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).

 

[JMB]

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))

 

[JMB]

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