Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function similar to TREND that interpolates lin. between data poin
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(Sh eet3!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). |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function similar to TREND that interpolates lin. between data
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(Sh eet3!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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro question | Excel Worksheet Functions | |||
constraints for the trend function | Excel Discussion (Misc queries) | |||
Create a Function similar to multiple IFs | Excel Discussion (Misc queries) | |||
Math Experts - Function to compare where salary is compared to others have 800 rows of data | Excel Worksheet Functions | |||
Can you use the validate function in a data form in Excel? | Excel Worksheet Functions |