Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    trendlines (MS Office 2003)

    I don't know what I am doing wrong, and will appreciate somebody correcting me.
    Attached spreadsheet shows results from several experiments listing average value of a given parameter and its standard deviation, for each experiment.
    Attached also plot (yellow line) and the polynomial trendline (power 2) in black.
    Shown also the equation of the trendline. From polynomial coefficients (repeated in cells C32:C34).
    I generated a NEW TABLE taking SAME x values as cells A2:A32, and when plotting them (blue line), I find they don't eve resemble the original values (B2:B32).
    Any help?

    Thanks in advance.

    Daniel Rozenberg.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: trendlines (MS Office 2003)

    The x values aren't distributed evenly, so you should use an xy scatter chart instead of a line chart. The equation will come out quite differently. See attached version.
    Attached Files Attached Files

  3. #3
    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: trendlines (MS Office 2003)

    To expand on what Hans indicated:

    If you use the trendline feature in XL in an XY scatter it bases the trendlines on the Xvalues and Yvalues in the ranges you are plotting

    If you use the trendline feature in XL in a Line chart it bases the trendlines on the Y ranges you have chosen but the Xvalues are the numbers 1 to the number of data points (1-29 in your example) [You can see this by plotting an XY scatter of 1-29 vs your Ys and calculating a trendline]. It will not use the values in your X range at all, since you have told XL 9by choosing a linechart) that they are not numeric, but categorical data.

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    Re: trendlines (MS Office 2003)

    Gentlemen;

    Thank you VERY, VERY MUCH.
    Best regards,

    Daniel Rozenberg.

Posting Permissions

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