Polynomial Regression with Dates

[OBR]

I had Excel develop a 6th order polynomial regression with Dates (M/D/Y) as
the independent variable (x) and Hydraulic Flow as the dependant variable
(y). Unfortunately, when I try to use the equation to generate Hydraulic Flow
values the equation outputs extremely large numbers that make no sense.
I imagine that the problem has to do with the Date format. Does any one have
a solution for this issue?
Regards,
OBR

 

[pdberger]

I wonder whether the problem is your use of the date -- not the format.
Excel stores dates as an integer, starting with 1/1/1900. So April 14, 2008
is stored as 39552. (The modulus to the right of the decimal point is
essentially the % of the day elapsed...)

Perhaps if you set your first day as a 'day 0', with subsequent days
numbered accordingly?

HTH

 

[Jerry W. Lewis]

That would certainly make for an easier numerical problem.

Fitting a 6th order polynomial is usually a very difficult numerical
problem. When you couple that with x's of the magnitude of current dates, it
is not possible to get a condition number smaller than 10^50 for X'X, which
means that it is not possible for LINEST prior to Excel 2003 to do it, and I
have serious doubts about the Excel 2007 "improvements" to the formerly
excellent chart trendline.

Centering the dates on zero would help, but the numerical problem could
still be unmanageable if the dates do not cover a long enough time span.

If the equation is coming from the chart trendline, have you reformatted the
equation to display coefficients in scientific notation with 14 decimal
places?

Jerry