Results 1 to 11 of 11

20060220, 12:15 #1
 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

20060220, 12:26 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20060220, 12:30 #3
 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

20060220, 12:40 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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 readymade solution or come up with one myself.

20060220, 12:57 #5
 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.

20060220, 13:27 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20060220, 13:42 #7
 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????

20060220, 14:37 #8
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,353
 Thanks
 4
 Thanked 229 Times in 210 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

20060220, 14:45 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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 FittingPerpendicular Offsets  From MathWorld for the mathematics behind it.
I have attached your workbook with these trendlines.

20060220, 15:45 #10
 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

20060220, 21:40 #11
 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.