Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inconsistant Trendline (2002 SP3)

    Hi y'all,
    the attached workbook contains 2 charts that use the same sets of data, but are simply have the axes swapped.

    Since the data is the same, the trendlines for each set of data should (IMHO) be the same.

    Why then are they different?

    Any ideas???

    TIA
    Regards
    Paul

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

    Re: Inconsistant Trendline (2002 SP3)

    The equation for a trend line is determined by minimizing the sum of the squares of yc - yt where yc is the y coordinate of a point on the chart and yt is the y coordinate of the point on the trendline with the same x. This definition is asymmetrical with respect to x and y. If you swap the x and y coordinates, the old trend line becomes the line for which the sum of the squares of xc - xt is minimized. This will in most cases be different from the line for which the sum of the squares of yc - yt is minimized.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inconsistant Trendline (2002 SP3)

    Ahhhh, thanks for that Hans.

    I had asumed that the trend line was the "line of best fit" for the points on the scatter graph.

    Is it possible to display such a beast, which would then be independent of the orientation of the graph axes?

    TIA
    Regards
    Paul

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

    Re: Inconsistant Trendline (2002 SP3)

    In most situations, the x coordinates are considered as given (fixed), and the y coordinates are considered as variable, so the calculation for the trendline only tries to minimize the vertical distances.

    It would be possible to calculate a trendline that minimizes the perpendicular distances from the data points to the line, but please keep in mind that this is not the "official" definition generally used in statistics. I'll see if I can find a ready-made solution or come up with one myself.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inconsistant Trendline (2002 SP3)

    The thing that gets me here is that, whilst they all agree at the mean values, if I look at Chart3 it tells me that for a female of height 164 I should expect to see a shoe size of 7.35, however, looking at the same data on Chart1 it would predict a shoe size of about 6.15.

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

    Re: Inconsistant Trendline (2002 SP3)

    No! Because of the definition of a trendline, those in Chart3 can only be used to predict the height of a person from his or her shoe size, NOT to predict someone's shoe size from his/her height. For Chart1, it is the reverse.

  7. #7
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inconsistant Trendline (2002 SP3)

    <hr>those in Chart3 can only be used to predict the height of a person from his or her shoe size, NOT to predict someone's shoe size from his/her height<hr>

    But that is where I have the problem. If I predict that for a particular shoe size, someone will be of a particular height, I would feel that I have defined a relationship between the 2 values.

    Suppose I predict that someone with a shoe size of 7.35 should be of height 164. So I find a person with a shoe size of 7.35, find that their height is 164 and then say, "because your height is 164, I predict that your shoe size is 6.15".

    It just doesn't work????

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Inconsistant Trendline (2002 SP3)

    Paul,
    If you look at your actual data, you will note the following:
    the average shoe size for a 164cm tall woman is 7.
    the average height for a woman with size 7 shoes, is 169.
    Because the groupings are different for the same data depending on which are your known values (in this case either height or shoe size), fitting a line to them will (and should) alter according to which way you look at it.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Inconsistant Trendline (2002 SP3)

    This is not the place to provide a course in statistics, but there is an essential difference between dependent and independent variables - their role is asymmetric. In the "traditional" trendline, the x values are treated as given - it makes no sense to predict them, because they are known.

    It is possible to create a trendline by minimizing the perpendicular distance from the data points to the trend line. This will be symmetric with respect to the x and y values.
    See Least Squares Fitting--Perpendicular Offsets -- From MathWorld for the mathematics behind it.
    I have attached your workbook with these trendlines.

  10. #10
    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: Inconsistant Trendline (2002 SP3)

    I don't see what doesn't work.

    If you have X = shoe size and Y = Ht (for the males)
    A shoe size of 7.35 predicts that the man should be 164.00

  11. #11
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inconsistant Trendline (2002 SP3)

    Wow, thanks for that Hans.

Posting Permissions

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