# Line of worst fit !

#### Getafix1066

##### Board Regular
Line of best fit that isn’t.

Have a look at this graph. It shows the latitude and longitude of a paraglider circling whilst drifting over the ground….

(if I’ve clicked the correct buttons, a good quality image of the graph called ‘LineOfworstFit.png’ should be here http://ge.tt/1cadawo2 )

The image clearly shows that a line-of-best-fit (aka a trend line) should be almost vertical (north-south), but the trend line added by excel is at a 45 degree angle (as shown)

After much head scratching I figured out why – it’s because the scales on the each axis aren’t equal, basically one unit of latitude on the vertical scale (Y) represents a different distance to one unit of longitude on the horizontal scale (X)

My question is - how to create a line-of-best-fit that matches the drift of the aircraft? The graph itself isn't complex, and the trend line function is performing correctly, but the result doesn't match the 'common sense' result expected.

(a simple spreadsheet containing the data and the graph is available at the above link)

Thanks

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### thisoldman

##### Well-known Member
I think this is what you want.

You want to calculate your own trend line and add it as a second series. The x values will be the latitude and the y values will be the longitude.

In I5: =SLOPE(B6:B128, C6:C128)
In I6: =INTERCEPT(B6:B128, C6:C128)

F6 and F7 are manually entered; the latitude points were chosen to lie just above and below the collected values.

In E6: =\$I\$5*F6+\$I\$6
In E7: =\$I\$5*F7+\$I\$6

Chart the original data as a scatter plot.
Copy E5:F7.
Select the chart.
From the Home tab, select the dropdown menu below the large Paste icon then select "Paste Special...".
The selections on the popup menu should be:

Format as you would wish:

My workbook: https://www.dropbox.com/s/pyx1tdyvnaohlgm/aircraft_drift.xlsx?dl=0

#### Getafix1066

##### Board Regular
Thanks thisoldman,

The line you've plotted is exactly what I wanted - except that I need to do it automatically! The method proposed relies on the manual selection of the two points at the end of the orange line (unless I've mis-understood).

I've done some more reading since my original post. The excel trend-line relies on something called the least-squares method, which (apparently) won't provide good fits for vertical data points. What I'm still trying to find out is how to do what you've shown automatically.

Thank you

#### thisoldman

##### Well-known Member
If you switch latitude and longitude axes, the Excel chart trendline will work as you want. But you will create confusion for the viewer as latitude and longitude are not as they would appear on a map. See the second tab in my uploaded workbook. The slope and intercept functions do use a least-squares method, the same as clicking the option for a chart.

To automate the chart, use a scatterplot with two series. Where I manually selected the two points for the end points of the trend line, use the max and min of the latitude.

F6: =MIN(C6:C128)
F7: =MAX(C6:C128)

See the new third tab in my uploaded workbook.
If you want to do this automatically for a varying count of positional data, say up to 500 points, we can use a dynamic range to calculate the minimum and maximum latitudes.

F6: =MIN(C6:INDEX(C6:C506, COUNT(C6:C506)))
F7: =MAx(C6:INDEX(C6:C506, COUNT(C6:C506)))

I show this dynamic range formula on the third tab of the worksheet also.

Last edited:

#### Getafix1066

##### Board Regular

Thanks thisoldman,

I've re-read your post, and (if I understand correctly) excel will return different values of SLOPE() and INTERCEPT() depending on which axis is treated as 'x' and which as 'y' - so switching the two axis around you get a better fit.

But there still remains the question of how to do this automatically? - if the wind is blowing from east to west then the fixes will be spread from left to right, and the first method for finding the trend line is probably best, but in other cases (north/south) then the 2nd method is better

#### thisoldman

##### Well-known Member
If you are OK with a separate series for the trend line, we can automate which trend line is displayed using formulas.

I do use named formulas to create dynamic ranges for the collected latitude and longitude data.

SLOPE(<font color="Red">Longitude, Latitude</font>) * MIN(<font color="Red">Latitude</font>) + INTERCEPT(<font color="Red">Longitude, Latitude</font>),
MIN(<font color="Red">Longitude</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F6</th><td style="text-align:left">=IF(<font color="Blue">ABS(<font color="Red">MAX(<font color="Green">Latitude</font>) - MIN(<font color="Green">Latitude</font>)</font>) < ABS(<font color="Red">MAX(<font color="Green">Longitude</font>) - MIN(<font color="Green">Longitude</font>)</font>),
SLOPE(<font color="Red">Latitude, Longitude</font>) * MIN(<font color="Red">Longitude</font>) + INTERCEPT(<font color="Red">Latitude, Longitude</font>),
MIN(<font color="Red">Latitude</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E7</th><td style="text-align:left">=IF(<font color="Blue">ABS(<font color="Red">MAX(<font color="Green">Latitude</font>) - MIN(<font color="Green">Latitude</font>)</font>) > ABS(<font color="Red">MAX(<font color="Green">Longitude</font>) - MIN(<font color="Green">Longitude</font>)</font>), SLOPE(<font color="Red">Longitude, Latitude</font>) * MAX(<font color="Red">Latitude</font>) + INTERCEPT(<font color="Red">Longitude, Latitude</font>), MAX(<font color="Red">Longitude</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F7</th><td style="text-align:left">=IF(<font color="Blue">ABS(<font color="Red">MAX(<font color="Green">Latitude</font>) - MIN(<font color="Green">Latitude</font>)</font>) < ABS(<font color="Red">MAX(<font color="Green">Longitude</font>) - MIN(<font color="Green">Longitude</font>)</font>),
SLOPE(<font color="Red">Latitude, Longitude</font>) * MAX(<font color="Red">Longitude</font>) + INTERCEPT(<font color="Red">Latitude, Longitude</font>),
MAX(<font color="Red">Latitude</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Latitude</th><td style="text-align:left">=Sheet1!\$C\$6:INDEX(<font color="Blue">Sheet1!\$C\$6:Sheet1!\$C\$506,COUNT(<font color="Red">Sheet1!\$C\$6:Sheet1!\$C\$506</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Longitude</th><td style="text-align:left">=Sheet1!\$B\$6:INDEX(<font color="Blue">Sheet1!\$B\$6:Sheet1!\$B\$506,COUNT(<font color="Red">Sheet1!\$B\$6:Sheet1!\$B\$506</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
New workbook: https://www.dropbox.com/s/fshon0pmbpd7xfi/aircraft_drift_v2.xlsx?dl=0

Last edited:

#### Getafix1066

##### Board Regular

Thank you for your help thisoldman.

What I've learnt elsewhere is that the SLOPE() and INTERCEPT() functions use 'least squares' regression which depends on which set of data you specify as the X and Y axis.

Apparently there's something called 'total least squares' regression which is independent of which is the 'x' and 'y' axis, unfortunately there doesn't seem to be a simple excel function to give this.

#### thisoldman

##### Well-known Member
Well, that's good. I felt the linear regression would not accurately show the drift. I avoided showing my misgivings because I assumed a proper solution would require a bunch of trigonometry and array formulas. It turns out, we can use Excel statistical functions to determine the total least squares.

I used the method shown by Charles Zaiontz at "Real Statistics Using Excel", http://www.real-statistics.com/regression/total-least-squares/.

Again I used named formulas for Latitude and Longitude. I also used a bunch of named cells—I needed to follow the formula logic and named formulas make that much easier than using Excel references. To reduce complexity, I did not combine formulas, even though the opportunities to do so may seem obvious.

The solution I came up with is similar to my last post—the trend line is a second series. To make the trend line display stay within the limits of the observed data, so that reasonable axes limits are maintained, I created a trend line takes into account the relative sizes of the latitude and longitude ranges. This means if the longitude range is greater than the latitude range, I use longitude as the independent variable, the x value in the line formula y = mx + b. If the latitude range is greater, latitude is used as the x in y = mx + b.

New workbook at: https://www.dropbox.com/s/bdnluvqptvi612r/aircraft_drift_v3.xlsx?dl=0

#### Getafix1066

##### Board Regular
Thanks @thisoldman - following some of your earlier tips I to had come across real-statistics.com and had been adapting it to use 'total least squares'. I had hoped (as per my other recent post on this forum) that the total least squares technique would be a standard excel feature, but apparently not! Thanks again.

Replies
1
Views
1K
Replies
0
Views
260
Replies
7
Views
971

1,129,983
Messages
5,639,380
Members
417,084
Latest member
elitepaper

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back