Interpolation and Extrapolation of data without using Trend line.

[pankajsonii]

Hello all,

I have attached a sheet below, which contains a table of values ( time v/s reduced water level) from this table I want to extract the data or extrapolate the data which should be logical and should match with the trend of the taken values.I have tried trend line in this table but trend line does not always work because sometimes the trend goes like a sin curve over the whole period of time and there is no such trend line equation which I can use to write a sin or cos equation to logically represent these values over a whole range of time abscissa.

[mcmahobt]

Since you will ultimately be deciding what is "logical", this sounds like a problem the Solver may be useful for. See here: http://www.excelforum.com/excel-gene...trendline.html

[MrShorty]

When the desired trendline is not available in the chart trendline feature, then you need to build your own trendline in the spreadsheet. I envision a process something like this:

1) Select the desired function. This is not really an Excel specific question. Another complication. If I follow your example spreadsheet, the two examples you give are so different, that I am not sure they will both be represented by the same function (unless you want to get mathematically intense and do something like Fourier transforms).

2a) If the function is "linear" (in a linear algebra sense, so it is much more than straight lines), then you can use a linear regression algorithm via Excel's LINEST() function. These same linear regression algorithms are used by the chart trendline, so the LINEST() function should give the same result as the chart trendline if you choose the same function type. Using the LINEST() function in the spreadsheet can be much more flexible than the chart trendlines.
2b) If the function is non-linear, then you will need to use non-linear regression techniques. Excel does not have any of these pre-programmed in, so you will need to program Excel to perform the non-linear regression. This usually involves entering reasonable first guesses for the parameters of your chosen function, computing estimated y values for each point, computing an "objective function" (SUMXMY2() or other "goodness of fit" measure), then using Excel's Solver utility to optimize the objective function by changing the function's parameters. For example, you can use a general sine function with amplitude, period, and phase shift as the parameters (can I assume you remember these from trigonometry?) then regress a best fit sine curve by adjusting these three parameters using Solver.
3) Evaluate the regression to see if it is adequate for what you are doing.
4) Perform interpolation and extrapolation as desired.