Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Polynomial Regression with Dates
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Polynomial Regression with Dates
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 "OBR" wrote: 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Polynomial Regression with Dates
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 "pdberger" wrote: 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 "OBR" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The multiple regression tool - linear or non-linear regression? | Excel Discussion (Misc queries) | |||
how to generate a polynomial regression graph with 90% CI? | Charts and Charting in Excel | |||
polynomial equation | Excel Worksheet Functions | |||
how to get coefficients for Polynomial regression as for rgp() | Excel Worksheet Functions | |||
Polynomial regression - how does Excel do it? (under "Trendline") | Charts and Charting in Excel |