Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. -- HTH... Jim Thomlinson "OBR" wrote: 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? |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 "OBR" wrote in message ... 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? |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 "OBR" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Polynomial Regression with Dates | New Users to Excel | |||
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 | |||
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 |