Polynomial Regression with Dates

[OBR]

I performed a polynomial regression with dates and flow rates in Excel. The
result provides me wit a sixth degree equation in function of dates
(independent variable). For some reason, when I use the equation to predict
future flows the equation produces very large values. I am assuming it is due
to the numerical format of the dates in the cells. Does any one know how to
solve this issue?

 

[Jim Thomlinson]

This is taking me back to my days in university so if the info is not 100%
accurate then my appologies. Regression is excellent for interpolating
results but when you start to extrapolate and try to predict future values
the function starts to break down. Eventually the function will return values
that get either extremely large or extremely small (tends towards infinity).

For example if you plot weather forcasts it will quite accurately
interpolate missing values but if you use ti to make future predictions then
it breaks down more and more the further away you get form your know values.

 

[Mike Middleton]

OBR -

Here are two of the usual problems with sixth-order polynomial curve
fitting:

(1) Overfitting. You may get a good fit to historical data, but it is
unlikely you can use it reliably for prediction.

(2) Numerical precision. With today's date, you're computing 39573^5. The
result has at least 23 significant digits, but Excel can handle only 15.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[MartinW]

Hi OBR,

Yes it is the numerical value of the dates. For instance todays date
6th May 2008 has an underlying value of 39574, that is the value
that Excel will use to make calculations or to create a chart etc.

You need to do a bit of lateral thinking to workaround it, usually
involving dummy series and dummy calculations in the worksheet.
Maybe calling your first date 0 and subsequent dates 1,2,3 etc.
you then use dummy labels or series to make your chart look right.
How you do the workaround depends on how your data is laid
out and what result you are trying to achieve, there are no real set
rules for doing this, it changes every time.

HTH
Martin