Perpendicular distance of a point from linear regression line?

  • Thread starter Thread starter Winny
  • Start date Start date
W

Winny

Hi all,

First post for me on here.


I have some Excel charts, with least squares fit lines through the
data. On each chart I have a single extra data point (it's own series)
and I want to know the perpendicular distance from the line to this
point.

This is a physical distance in the real world, as my axes are both in
milimeters but to different scales.

Thanks for any help.

--Winny
 
You know slope and intercept of fitted line, and coordinates of point,
so you can calculate dx and dy, the horizontal and vertical distance
from point to line. By similar triangles, the shortest distance h from
line to point is:

h = dx dy / SQRT[(dx)^2 + (dy)^2]

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Hi Jon,

Thanks very much.

I should have been able to work that out for myself really, but the
trig part of my brain has been unused for a couple of decades.

--Winny
 
Hi Jon,

I cannot derive the formula you got but it cannot work for any vertical
or horizontal line.

For an approach from MathWorld see
http://mathworld.wolfram.com/Point-LineDistance2-Dimensional.html

It gives the distance of (x0,y0) from ax+by+c=0 as
abs(a*x0+b*y0+c)/sqrt(a^2+b^2)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

You know slope and intercept of fitted line, and coordinates of point,
so you can calculate dx and dy, the horizontal and vertical distance
from point to line. By similar triangles, the shortest distance h from
line to point is:

h = dx dy / SQRT[(dx)^2 + (dy)^2]

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Hi all,

First post for me on here.


I have some Excel charts, with least squares fit lines through the
data. On each chart I have a single extra data point (it's own series)
and I want to know the perpendicular distance from the line to this
point.

This is a physical distance in the real world, as my axes are both in
milimeters but to different scales.

Thanks for any help.

--Winny
Click to expand...
Click to expand...
 
Back
Top