Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    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.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 1-whatever 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

  3. #3
    3 Star Lounger
    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 1-11 on the x-axis, 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.

  4. #4
    Star Lounger
    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.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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 X-axis 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

  7. #7
    3 Star Lounger
    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 x-axis 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •