Alternative versions of SLOPE() and INTERCEPT()

[Getafix1066]

Hi,

This is a follow on from a related thread https://www.mrexcel.com/forum/excel-questions/1047034-line-worst-fit.html

I'm interested in the 'line-of-best-fit' or 'trend line' for a set of scattered points on a graph. I know I can use SLOPE() and INTERCEPT() to get the equation of the line in the form of y=mx+c (where m is the slope, and c is the intercept).

Having spent some time researching this I've discovered that SLOPE() and INTERCEPT() use a technique known as 'least squares linear regression', this is OK but it tends favour one axis over the other.

I've also learnt that statisticians/mathematicians have a more accurate technique called 'total least squares linear regression' which doesn't favour one axis over the other (not as much, anyway!)

My question is this - is there a version of SLOPE() and INTERCEPT() that use this technique? If not is there another way of doing total least squares analysis in excel?

Thanks

 

[Kaper]

I'm not sure if paragilder circling with drift shall be fitted linear equation at all, but you may try formulas given at: http://www.real-statistics.com/regression/total-least-squares/

As far as I'm aware, you shall compute it on your own in workbook, because there are no predefined functions for it. Of course it is not necessary to use as many helper cells as in the sample in a link above. You could combine formulas into just two (especially as you already have defined names with lat and lon data ).