Smartin,
Thank you so much for your insights!
As I am still learning about polynomial trending and its value, they are
helpful.
So, let me see if I understand what you are saying. Let's say, for instance,
we are talking about fluctuations in gas prices at the pump over the last x
number of years, and we see how they've gone up and down and up and down and
now are waaaaayyyy down (hallelujah! I paid $2.19 yesterday, a price I never
thought I'd see again in this lifetime), but we cannot predict future prices
based on trending of the data alone without knowing something about the
underlying context and 'reasons' for the fluctuations?
So, how is trending used then for predictive purposes? Does it merely state
probabilities of future performance based on previous fluctuations? If so,
what parameter reveals this probability (is it the R-value?)? So, using our
example above, could we plot a trend line that gives a probability, based on
the data, of gas prices going up or down, for instance?
Thank you, anyone, for enlightening me on this subject. Are there any
statisticians in our midst on this board?
Orion
"smartin" wrote:
Orion,
I hope others will comment on this as I am not a statistician, nor am I
intimately familiar with the inner workings of Excel's so-called
"trendlines". Regardless, here are my thoughts.
If you look at it, a polynomial "trendline" really can't trend anything
in your data. Neither can any other sort of Excel "trendline". This is
readily apparent by applying a poly "trendline" to your data.
At order 6, the polynomial does well to interpolate your existing data,
but if you set the "trendline" to "forecast" some number of units
forward, the result is not credible in the least. It should be obvious
what is going on he Excel fits a curve to your points, but it doesn't
(and can't) recognize a pattern to predict future (or past) results.
So, to your second question, experiment a little, and you will see that
picking an order that is one more than #peaks + #valleys (including
endpoints) makes a nice fit. However, do not rely on this to do any kind
of trending.
To do sensible trending, you need to know how to inspect your data in
the appropriate contexts, and apply reasonable assumptions for the future.
Orion wrote:
Hi Bernard,
Thank you so much for answering this question. I see now that what you are
saying is true. I have both 03 and 07 versions.
A couple of questions:
How do you determine what order no. to select for a polynomial trend line?
The Excel Help text says choose an order that is one greater than the no. of
peaks and valleys. I'm not quite sure what constitutes a peak and a valley.
If the data points are 70, 75, 85, 75, 83, 92, 78, 73. Is that 2 peaks and 2
valleys? So the order no. to select is 5? Or?
What is your opinion about the reliability of polynomial trending for
forecasting?
Thank you so much for your response. Best wishes.
"Bernard Liengme" wrote:
Do you mean the value in the window when you use Add Trendline and select
Polynomial.
In my versions of XL2003 and XL2007 this is 2. I seem to recall it was
always 2 in versions from 1997. I have never seen 4 as the 'default'
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
"Orion" wrote in message
...
I'm trying to discover the default order no. for polynomial trending in
Excel
97 and 2000 and if possible, why they chose order no. 4 as the default in
Excel 2003? Can anyone help?