Line of best fit does not look like a good fit. Why?

Have a look at this Excel graph:

The 'common sense' line-of-best-fit would appear be an almost vertical line straight through the center of the points (edited by hand in red). However the linear trend line as decided by Excel is the diagonal black line shown.

1. Why has Excel produced something that (to the human eye) appears to be wrong?
2. How can I produce a best fit line that looks a little more intuitive (i.e. something like the red line)?

Update 1. An Excel spreadsheet with data and graph is available here: example data, CSV in Pastebin. Are the type1 and type2 regression techniques available as excel functions?

Update 2. The data represent a paraglider climbing in a thermal whilst drifting with the wind. The final objective is to investigate how wind strength and direction varies with height. I'm an engineer, NOT a mathematician or statistician, so the information in these responses has given me a lot more areas for research.

This is an interesting thread and It would be a shame for the data to be lost and someone in future unable to reproduce the examples, so I'm adding it as a comment here (which is the data from the following link).

"lon","lat"
-0.713917,53.9351
-0.712917,53.93505
-0.712617,53.934983
-0.712333,53.9349
-0.7122,53.93475
-0.71215,53.934567
-0.712233,53.9344
-0.712483,53.934233
-0.712817,53.934167
-0.713217,53.934167
-0.713617,53.934267
-0.7141,53.934733
-0.714133,53.935
-0.71395,53.935283
-0.713617,53.9355
-0.713233,53.935617
-0.712767,53.935617
-0.712383,53.9355
-0.712183,53.9353
-0.712367,53.934883
-0.712717,53.934767
-0.713133,53.9348
-0.713583,53.934917
-0.713867,53.93515
-0.714017,53.935433
-0.7139,53.935717
-0.7136,53.935933
-0.71325,53.936067
-0.712833,53.936133
-0.7124,53.936117
-0.712083,53.935983
-0.7119,53.935767
-0.711917,53.935567
-0.7121,53.935383
-0.7124,53.935283
-0.712733,53.93525
-0.713117,53.935267
-0.7135,53.93535
-0.713817,53.935517
-0.71405,53.935733
-0.71415,53.935983
-0.7141,53.93625
-0.7139,53.9365
-0.713567,53.936667
-0.713183,53.936767
-0.712767,53.9368
-0.7124,53.9367
-0.712133,53.93655
-0.712033,53.936333
-0.712167,53.936167
-0.712383,53.936017
-0.712733,53.935917
-0.7132,53.93595
-0.713567,53.936067
-0.713867,53.936267
-0.714067,53.9365
-0.71415,53.936767
-0.714033,53.937033
-0.71375,53.937233
-0.7134,53.9374
-0.712967,53.93745
-0.71255,53.937433
-0.7122,53.937267
-0.712067,53.937033
-0.712117,53.9368
-0.712367,53.936617
-0.712733,53.936533
-0.713133,53.93655
-0.713467,53.93665
-0.71375,53.93685
-0.713933,53.937083
-0.71395,53.937367
-0.713767,53.937633
-0.713433,53.937833
-0.713033,53.937967
-0.712567,53.937967
-0.71215,53.937867
-0.711883,53.93765
-0.711817,53.937433
-0.711983,53.937233
-0.71265,53.937033
-0.713067,53.9371
-0.713683,53.93745
-0.713817,53.937983
-0.713633,53.938233
-0.7133,53.938433
-0.71285,53.938533
-0.71205,53.938333
-0.71185,53.938117
-0.711867,53.937867
-0.712067,53.9377
-0.712417,53.937583
-0.712833,53.937567
-0.713233,53.937667
-0.713567,53.937883
-0.7137,53.938417
-0.713467,53.93865
-0.713117,53.938817
-0.712683,53.938917000000004
-0.71225,53.938867
-0.711917,53.938717
-0.711767,53.938483
-0.711883,53.938267
-0.712133,53.9381
-0.712483,53.938017
-0.713283,53.93815
-0.713567,53.938333
-0.7138,53.938567
-0.713683,53.9391
-0.713417,53.9393
-0.71305,53.939433
-0.7126,53.939483
-0.7122,53.9394
-0.711917,53.93925
-0.711783,53.93905
-0.7118,53.938817
-0.711967,53.938667
-0.712217,53.938533
-0.712567,53.938433
-0.712933,53.93845
-0.7133,53.938567
-0.713583,53.93875
-0.71375,53.939

• To be honest, I doubt that this is a question of Deming regression vs. OLS. Looking at the very small absolute variability in $x$ and $y$, I'd rather think this may be a numerical issue in Excel. Can you edit your question to include the data? – Stephan Kolassa Mar 11 '18 at 12:59
• This phenomenon was one of the major statistical discoveries of the 19th century (if not of all time). It is termed regression toward the mean. Indeed, it is the very reason why this statistical procedure is called "regression"! – whuber Mar 11 '18 at 14:40
• I should add that the main reason the fits look so different is that the plot so greatly exaggerates the scale in the y-axis. By drawing it where both scales are proportional to the marginal standard deviations, you might arrive at entirely different conclusions about which fit is more "common sense." – whuber Mar 11 '18 at 16:28
• @StephanKolassa - Note how large the error would be if you used the red line and predicted $y$ for $x=-0.714$; the red line can't be a best least squares fit. Excel certainly has its problems, but I think this isn't one of them. – jbowman Mar 11 '18 at 16:29
• This has been addressed numerous times on site already (e.g. see here). When you understand what a regression line is, it's easy to see why it's the black line. Ask yourself two questions: 1: what's the average value of y when x is about 0.712? 2. What do the red and the black lines predict it should be? ... [I have held off closing as duplicate for now as there are specific issues with your data that it would be worth refocusing your question on] – Glen_b Mar 12 '18 at 3:58

Is there a dependent variable?

The trend line in Excel is from the regression of the dependent variable "lat" on independent variable "lon." What you call a "common sense line" can be obtained when you don't designate dependent variable, and treat both the latitude and longitude equally. The latter can be obtained by applying PCA. In particular, it's one of the eigen vectors of the covariance matrix of these variables. You can think of it as a line minimizing the shortest distance from any given $$(x_i,y_i)$$ point to a line itself, i.e. you draw a perpendicular to a line, and minimize the sum of those for each observation.

Here's how you could do it in R:

> para <- read.csv("para.csv")
> plot(para)
>
> # run PCA
> pZ=prcomp(para,rank.=1)
> # look at 1st PC
> pZ$$rotation PC1 lon 0.09504313 lat 0.99547316 > > colMeans(para) # PCA was centered lon lat -0.7129371 53.9368720 > # recover the data from 1st PC > pc1=t(pZ$$rotation %*% t(pZx) ) > # center and show > lines(pc1 + t(t(rep(1,123))) %*% c)  The trend line that you got from Excel is as a common sense as the eigen vector from PCA when you understand that in the Excel regression the variables are not equal. Here you're minimizing a vertical distance from $$y_i$$ to $$y(x_i)$$, where y-axis is latitude and x-axis is a longitude. Whether you want to treat the variables equally or not depends on the objective. It's not the inherent quality of the data. You have to pick the right statistical tool to analyze the data, in this case choose between the regression and PCA. An answer to a question that wasn't asked So, why in your case a (regression) trend line in Excel doesn't seem to be a suitable tool for your case? The reason is that the trend line is an answer to a question that wasn't asked. Here's why. Excel regression is trying to estimate the parameters of a line $$lat=a+b \times lon$$. So, the first problem is the latitude is not even a function of a longitude, strictly speaking (see the note at the end of the post), and it's not even the main issue. The real trouble is that you're not even interested in paraglider's location, you're interested in the wind. Imagine that there was no wind. A paraglider would be making the same circle over and over. What would be the trend line? Obviously, it would be flat horizontal line, its slope would be zero, yet it doesn't mean that the wind is blowing in horizontal direction! Here's a simulated plot for when there's a strong wind along y-axis, while a paraglider is making perfect circles. You can see how linear regression $$y\sim x$$ produces nonsensical result, a horizontal trend line. Actually, it's even slightly negative, but not significant. The wind direction is shown with a red line: R code for the simulation: t=1:123 a=1 #1 b=0 #1/10 y=10*sin(t)+a*t x=10*cos(t)+b*t plot(x,y,xlim=c(-60,60)) xp=-60:60 lines(b*t,a*t,col='red') model=lm(y~x) lines(xp,xp*model$$coefficients[2]+model$$coefficients[1])  So, the direction of the wind clearly is not aligned with the trend line at all. They're linked, of course, but in a nontrivial way. Hence, my statement that the Excel trend line is an answer to some question, but not the one you asked. Why PCA? As you noted there are at least two components of the motion of a paraglider: the drift with a wind and circular motion controlled by a paraglider. This is clearly seen when you connect the dots on your plot: On one hand, the circular motion is really a nuisance to you: you're interested in the wind. Though on the other hand, you don't observe the wind speed, you only observe the paraglider. So, your objective is to infer the unobservable wind from observable paraglider's location reading. This is exactly the situation where tools such as factor analysis and PCA can be useful. The aim of PCA is to isolate a few factors that determine the multiple outputs by analyzing the correlations in outputs. It's effective when the output is linked to factors linearly, which happens to be the case in your data: wind drift simply adds to the coordinates of the circular motion, that's why PCA is working here. PCA setup So, we established that PCA should have a chance here, but how will we actually set it up? Let's start with adding a third variable, time. We're going to assign time 1 to 123 to each 123 observation, assuming the constant sampling frequency. Here's how the 3D plot looks like of the data, revealing its spiral structure: The next plot shows the imaginary center of rotation of a paraglider as brown circles. You can see how it drifts on lat-lon plane with the wind, while paraglider shown with a blue dot is circling around it. The time is on vertical axis. I connected the center of rotation to a corresponding location of a paraglider showing only the first two circles. The corresponding R code: library(plotly) para <- read.csv("para.csv") n=24 parat=1:123 # add time parameter

# run PCA
pZ3=prcomp(para)
c3=colMeans(para) # PCA was centered
# look at PCs in columns
pZ3$rotation # get the imaginary center of rotation pc31=t(pZ3$$rotation[,1] %*% t(pZ3$$x[,1]) ) eye = pc31 + t(t(rep(1,123))) %*% c3 eyedata = data.frame(eye) p = plot_ly(x=para[1:n,1],y=para[1:n,2],z=para[1:n,3],mode="lines+markers",type="scatter3d") %>% layout(showlegend=FALSE,scene=list(xaxis = list(title = 'lat'),yaxis = list(title = 'lon'),zaxis = list(title = 't'))) %>% add_trace(x=eyedata[1:n,1],y=eyedata[1:n,2],z=eyedata[1:n,3],mode="markers",type="scatter3d") for( i in 1:n){ p = add_trace(p,x=c(eyedata[i,1],para[i,1]),y=c(eyedata[i,2],para[i,2]),z=c(eyedata[i,3],para[i,3]),color="black",mode="lines",type="scatter3d") } subplot(p)  The drift of the center of paraglider's rotation is caused mainly by the wind, and the path and speed of the drift is correlated with the direction and the speed of the wind, unobservable variables of interest. This is how the drift looks like when projected to lat-lon plane: PCA Regression So, earlier we established that regular linear regression doesn't seem to work very well here. We also figured why: because it doesn't reflect the underlying process, because paraglider's motion is highly nonlinear. It's a combination of circular motion and a linear drift. We also discussed that in this situation factor analysis might be helpful. Here's an outline of one possible approach to modeling this data: PCA regression. But fist I'll show you the PCA regression fitted curve: This has been obtained as follows. Run PCA on the data set which has extra column t=1:123, as discussed earlier. You get three principal components. The first one is simply t. The second corresponds to the lon column, and the third to lat column. I fit the latter two principal components to a variable of the form $$a\sin(\omega t+\varphi)$$, where $$\omega,\varphi$$ are extracted from spectral analysis of the components. They happen to have the same frequency but different phases, which is not surprising given the circular motion. That's it. To get the fitted values you recover the data from fitted components by plugging the transpose of the PCA rotation matrix into the predicted principal components. My R code above shows parts of the procedure, and the rest you can figure out easily. Conclusion It's interesting to see how powerful is PCA and other simple tools when it comes to physical phenomena where the underlying processes are stable, and the inputs translate into outputs via linear (or linearized) relationships. So in our case the circular motion is very nonlinear but we easily linearized it by using sine/cosine functions on a time t parameter. My plots were produced with just a few lines of R code as you saw. The regression model should reflect the underlying process, then only you can expect that its parameters are meaningful. If this is a paraglider drifting in the wind, then a simple scatter plot like in the original question will hide the time structure of the process. Also Excel regression was a cross sectional analysis, for which the linear regression works best, while your data is a time series process, where the observations are ordered in time. Time series analysis must be applied here, and it was done in PCA regression. Notes on a function Since a paraglider is making circles, there will be multiple latitudes corresponding to a single longitude. In mathematics a function $$y=f(x)$$ maps a value $$x$$ to a single value $$y$$. It's many-to-one relationship, meaning that multiple $$x$$ may correspond to $$y$$, but not multiple $$y$$ correspond to a single $$x$$. That is why $$lat=f(lon)$$ is not a function, strictly speaking. • Good catch on the spiral structure! Two side comments: It is easier to work with$a\ \text{sin}\ \omega{t} + b\ \text{cos}\ \omega{t}\$ as an alternative parameterisation. There are plenty of cases in which we choose to average over a periodicity that isn't interesting or relevant to the main problem. – Nick Cox Mar 12 '18 at 9:06
• Whether you want to treat them equally or not depends on the objective. It's not the inherent quality of the data. -- Great point and +1. – Richard Hardy Mar 12 '18 at 10:39
• @NickCox, that's right, it would have been less work too – Aksakal Mar 13 '18 at 15:57
• Might be worth pointing out that PCA is the generalization of major axis regression to the situation where you have >2 variables. But that since in this case there is only 2 variables the standard name for the technique would be major axis regression (also sometimes called orthogonal regression or Type II regression). – Tom Wenseleers Mar 27 '18 at 12:40
• "This has been obtained as follows. Run PCA on the data set", it would have been interesting to have the R code for this too, there are also a typo ("But fist I'll show you") which is too small for me to edit. I learnt from this post though thanks +1 – baxx Nov 14 '20 at 14:31

The answer probably has to do with how you are mentally judging the distance to the regression line. Standard (Type 1) regression minimizes the squared error, where error is calculated based on vertical distance to the line.

Type 2 regression may be more analogous to your judgement of the best line. In it, the squared error minimized is the perpendicular distance to the line. There are a number of consequences to this difference. One important one is that if you swap X- and Y-axes in your plot and refit the line, you will get a different relationship between the variables for Type 1 regression. For Type 2 regression, the relationship remains the same.

My impression is that there is a fair amount of debate about where to use Type 1 vs Type 2 regression, and so I suggest reading carefully about the differences before deciding which to apply. Type 1 regression is frequently recommended in cases where one axis is either controlled experimentally, or at least measured with far less error than the other. If these conditions are not met, Type 1 regression will bias slopes towards 0 and therefore Type 2 regression is recommended. However, with sufficient noise in both axes, type 2 regression apparently tends to bias them towards 1. Warton et al. (2006) and Smith (2009) are good sources for understanding the debate.

Also note that there are several subtly different methods that fall within the the broad category of Type 2 regression (Major Axis, Reduced Major Axis, and Standard Major Axis regression), and that terminology about the specific methods is inconsistent.

Warton, D. I., I. J. Wright, D. S. Falster, and M. Westoby. 2006. Bivariate line-fitting methods for allometry. Biol. Rev. 81: 259–291. doi:10.1017/S1464793106007007

Smith, R. J. 2009. On the use and misuse of the reduced major axis for line-fitting. Am. J. Phys. Anthropol. 140:476–486. doi:10.1002/ajpa.21090

EDIT:

@amoeba points out that what I am calling Type 2 regression above is also known as orthogonal regression; this may be the more appropriate term. As I said above, the terminology in this area is inconsistent, which warrants extra care.

• Debate about whether to use type 1 or type 2? There is nothing to debate about when you know what your goal (objective function or loss function) is. And if you don't, well, then you should clarify that before proceeding. – Richard Hardy Mar 11 '18 at 14:32
• Type 2 also produces nonsensical results if both axes use different units. – John Dvorak Mar 11 '18 at 17:46
• You are using "Type 1" and "Type 2" as if these were standard terms. Are they? I've never head anybody calling usual regression and orthogonal regression "type 1" and "type 2". – amoeba Mar 12 '18 at 9:23
• @RichardHardy Of course, clarifying one's specific goal is best. But as I said in the answer, my impression is that there is an ongoing debate about their usage in a subset of cases - and those papers I cite (as well as the conflicting recommendations I keep getting from reviewers) seem to bear this out. – mkt - Reinstate Monica Mar 12 '18 at 10:04
• @mkt, thanks for your clarification. Indeed, there might be some debate among people who are not sure what they are after. There, the focus of the debate is which of the two is more relevant for their subject-matter goal. What I wanted to stress is that there is no debate once your goal is well defined, i.e. the subject-matter goal is translated into statistical language (which is unavoidable if one is to employ statistical methods). So I guess we agree, we are just stressing different parts of the argument. – Richard Hardy Mar 12 '18 at 10:21

The question that Excel tries to answer is: "Assuming that y is dependent on x, which line predicts y best". The answer is that because of the huge variations in y, no line could possibly be particularly good, and what Excel displays is the best you can do.

If you take your proposed red line, and continue you it up to x = -0.714 and x = -0.712, you will find that its values are way, way off the chart, and it is at a huge distance from the corresponding y values.

The question that Excel answers is not "which line is closest to the data points", but "which line is best to predict y values from x values", and it does this correctly.

• Exactly. The underlying assumption is "x is given, y is measured / predicted". – Floris Mar 12 '18 at 2:07

I don't want to add anything to the other answers, but I do want to say that you have been led astray by bad terminology, in particular the term "line of best fit" which is used in some statistics courses.

Intuitively, a "line of best fit" would look like your red line. But the line produced by Excel is not a "line of best fit"; it is not even trying to be. It is a line that answers the question: given the value of x, what is my best possible prediction for y? or alternatively, what is the average y value for each x value?

Notice the asymmetry here between x and y; using the name "line of best fit" obscures this. So does Excel's use of "trendline".

It is explained very well at the following link:

https://www.stat.berkeley.edu/~stark/SticiGui/Text/regression.htm

You might want something more like what is called "Type 2" in the answer above, or "SD Line" at the Berkeley stats course page.

Part of the optical issue comes from the different scales - if you use the same scale on both axis, it will look already different.

In other words, you can make most such ‘best fit‘ lines look ‘unintuitive’ by spreading one axis scale out.

• I agree this is the answer - the X range of the values is about 0.02 wide, but the Y range only about 0.005 -- in fact the chart should be about 4 times as wide as it is high, and it would be obvious that the best fit would be horizontal. The phenomenon in the question is purely visual due to the different scales. – RemcoGerlich Mar 12 '18 at 10:01
• @RemcoGerlich We can agree that as shown in the question the aspect ratio of the graph is not helpful. But the suggestion that you need a graph 4 times as wide as high because numerically the ranges are in that ratio is implausible and certainly not a matter of fact. If the units on either axis were changed by a factor of 1000, would you suggest an aspect ratio of 4000 or 0.004? The 4x ratio is likely to be just a side-effect of different units. – Nick Cox Mar 12 '18 at 15:16
• The other answers give the correct answer. This one sadly does not. If we rescale the values, such that we get the same visual image, but with equal axes, it is still a "type 1" vs "type 2" fitting problem. – user174363 Mar 12 '18 at 15:18
• He talks about the "common sense" best fit, what is right "to the human eye". And then the scaling of the axes is the main thing that's relevant. – RemcoGerlich Mar 12 '18 at 15:38
• He has a point, sometimes what looks intuitive is influenced by silly things like scaling, though this is not such a case. Here we have a genuine mismatch between what excel does and what OP wants – Aksakal Mar 18 '18 at 4:49

A few individuals have noted that the problem is visual - the graphical scaling employed produces misleading information. More specifically, the scaling of "lon" is such that it it appears to be a tight spiral which suggests the regression line provides a poor fit (an assessment to which I agree, the red line you draw would provide lower squared errors if the data were shaped in the manner presented).

Below I provide a scatterplot created in Excel with scaling for "lon" altered so it does not produce the tight spiral in your scatterplot. With this change, the regression line now provides a better visual fit and I think helps demonstrate how the scaling in the original scatterplot provided a misleading assessment of fit.

I think regression works well here. I don't think a more complex analysis is needed.

For any interested, I have plotted the data using a mapping tool and show the regression fitted to the data. The red dots are the recorded data and the green is the regression line.

And here are the same data in a scatter plot with regression line; here lat is treated as the dependent and lat scores are reversed to fit with geographic profile.

• Regression doesn’t show the direction of a wind drift – Aksakal Mar 18 '18 at 4:50
• We know only lat and lon, and for the data provided it does show change of one relative to the other. – Bryan Mar 19 '18 at 17:43
• So what does this trend line signify? – Aksakal Mar 19 '18 at 17:45
• Assuming the first point in the data file is the starting location, it appears there is a slight northern increase in route the further east traveled. The data provide no information about wind strength or height, but does provide direction - east by north. – Bryan Mar 19 '18 at 21:23
• Correction, the mapping software I used request lat-lon, but his data are lon-lat, so the wind direction would be slight north by east, i.e. there is a slight eastern movement the further north traveled (or slight western movement the further south traveled). – Bryan Mar 19 '18 at 21:44

Your confuse ordinary least squares (OLS) regression (which minimizes the sum of the squared deviation about the predicted values, (observed-predicted)^2) and major axis regression (which minimizes the sums of squares of the perpendicular distance between each point and the regression line, sometimes this is referred to as Type II regression, orthogonal regression or standardized principal component regression).

If you want to compare the two approaches just in R just check out

data=read.csv("https://pastebin.com/raw/4TsstQYm")
require(lmodel2)
fit = lmodel2(lat ~ lon, data=data)
plot(fit,method="OLS") # ordinary least squares regression


plot(fit,method="MA") # major axis regression


What you find most intuitive (your red line) is just the major axis regression, which visually speaking is indeed the one that looks most logical, as it minimizes the perpendicular distance to your points. OLS regression will only appear to minimize the perpendicular distance to your points if the x and y variable are on the same measurement scale and/or have the same amount of error (you can see this simply based on Pythagoras' theorem). In your case, your y variable has way more spread on it, hence the difference...

The PCA answer is the best because I think that is what you should be doing given your problem description, however the PCA answer might confuse PCA and regression which are totally different things. If you want to extrapolate this particular data set then you need to do regression, and likely want to do Deming regression (which I guess sometimes goes by Type II, never heard of this description). However, if you want to find out what directions are most important (eigenvectors) and have a metric of their relative impact on the data set (eigenvalues) then PCA is the correct approach.

• This is mostly a series of comments on other answers. It would be better to comment directly on each. I don't see that the answer by @Aksakal confuses PCA and regression at all. – Nick Cox Mar 14 '18 at 9:06
• I wanted to comment directly, but wasn't reputable enough. I don't think Aksakal is confusing regression, but thought it's worth pointing out to the OP that PCA and regression are totally different. – Andrew Holmgren Mar 14 '18 at 20:24