Results 1 to 7 of 7
Thread: Trendline (Excel 2000 SR2)

20021103, 17:29 #1
 Join Date
 Jun 2001
 Location
 Lawrence, Kansas, USA
 Posts
 202
 Thanks
 0
 Thanked 0 Times in 0 Posts
Trendline (Excel 2000 SR2)
I use the add trendline on Excel charts to find a starting point for forecasts. I've got two questions about this.
1. If I'm using dates on the x axis, I get different parameters than if I use integers. This makes sense, but I can't figure out what values the Excel uses to calculate the parameters. It doesn't seem to be the date values. See the spreadsheet that I've attached.
2. What function is Excel using for the exponential trends, power functions, etc. The trend function is least squares and doesn't have any option for another fit. Of course, I could transform the data, but it would be nice to have a function that already did it just the charts seem to have it automated.
I'll appreciate any insights and help.

20021103, 22:32 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Trendline (Excel 2000 SR2)
If you use dates you get the serialdate as the "X" value.
eg. 11/4/2002 is 37,562. (it is the num of days past 12/31/1899. Day1 is 1/1/1900.)
This will cahnge the constants depending on how the values 1whatever relate to the serialdates.
Trendline uses a custom routine since it must get rid of blanks and errors to plot a trend.
For functions you can use LINEST, but the equation must be LINEARIZED first. That is it must be in some form:
Y = A0 + A1*X1 + A2 *X2 + . . . + Ak*Xk
Polynomials need no lineariziing:
Y = A0 + A1*X + A2*X^2 + .... + Ak*X^k
If you have an equation, for example):
Y = A*e^Bx
You have to transform to:
ln(y) = ln(A) + Bx
So you getyou can use linest or slope/intercept functions)
Y' = Intercept + Slope * X
Where Y' = ln(Y)
For equations that can NOT be linearized, you can use Solver to minimize the sum of squares once you have an equation. You can also use the slover technique in cases that also could use the LINEST function.
Steve

20021104, 01:21 #3
 Join Date
 Jun 2001
 Location
 Lawrence, Kansas, USA
 Posts
 202
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Trendline (Excel 2000 SR2)
If I use 37621 which is the serial number for Dec 31, 2002 in the equation: y = 0.0002x2  0.3779x + 183.8 which was estimated with dates on the x axis, I get 268851.8053.
If I fit the same trend with integers 111 on the xaxis, instead of dates, I get y = 0.0017x2 + 0.2182x + 0.853. Using 12 for the next step ahead forecast, I get 3.7162. The range of the function is approximately 1  5 over the domain that I'm interested in. The trendlines and charts look exactly the same, just the domain is different.
In the spreadsheet that I attached, if I used the date serial number, I should get the same answer.
Besides LINEST, I could also use TREND and REGRESS that will produce linear estimates. I know how to transform the data to use those least squares estimators, but I was asking if there are some functions that would do the transforms, since there is obviously a macro that's doing it for the charts.

20021104, 03:37 #4
 Join Date
 Jan 2001
 Location
 Adelaide, South Australia, Australia
 Posts
 85
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Trendline (Excel 2000 SR2)
If you use Excel dates, then the x axis values are in days. Your serial numbers are in months. The number of days per month varies, so there isn't a simple way to get from one to the other.
Ian.

20021104, 11:54 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Trendline (Excel 2000 SR2)
1)You can not use 1 equation for the 2 different extrapolations if you use serial dates you MUST use the 0.0002 equation. If you use the "12" you need "0.0017" Your spreadsheet used the SAME equation for both!
2) be wary of significant figures. You need more more than 1 or 2 if you want precise answers! 0.0002 is ONLY 1 sig fig use MORE figures. 0.0017 is ONLY 2. I would use all 15 if possible
3) you can transform directly using the slope function(and the LINEST). You must ensure that all transformed values are valid (no values <=0 for ln!)
=slope(ln(Yvalues),Xvalues)
=exp(intercept(ln(yvalues),Xvalues)
Will directly give the transformation I posted earlier without having a temp column for the transformation.
Steve

20021104, 14:45 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Trendline (Excel 2000 SR2)
I noticed something else in your chart. Your chart is a LINE chart NOT an XY chart. If you are going to use serialdates you need an XY scatter since the dates are NUMBERS.
With a line chart and a time scale, the base units must be set to days to use the serialdates. If you use any of the other, I am not sure how excel is getting the equation.
Your top graph should also use an XY, line charts are categories on the Xaxis and you can sometimes get strange results when you do a trend versus categories depending on how excel decides to translate them into incremental numbers.
Steve

20021104, 15:04 #7
 Join Date
 Jun 2001
 Location
 Lawrence, Kansas, USA
 Posts
 202
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Trendline (Excel 2000 SR2)
Ian,
I'm not sure what you mean that my serial numbers are in months. I didn't know there was any other way to get serial numbers. I thought just the formatting changed, not the number.
Steve, I did notice that I had used the wrong equation on the spreadsheet and I've corrected it. Still the numbers are very different than, I would expect.
You point about significant digits was right on and that's why I was trying to get a better way to come up with those trendlines myself. Thanks for showing the easier way to do the transforms:
=slope(ln(Yvalues),Xvalues)
=exp(intercept(ln(yvalues),Xvalues).
You are right about the X,Y chart. I was assuming Excel would do something like 1, 2, 3 ... for the xaxis on the line chart. Bad assumption. If I specify the values using and XY chart, I'll get more reliable results. I usually go back and verify everything in Minitab after I get a start on a forecast equation.
Thanks.