# Thread: Inconsistant Trendline (2002 SP3)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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.

9. ## 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. ## 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. ## 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
•