Rating the straightness of a line

I have a data set that defines a set of points on a 2-dimen开发者_如何学运维sional Cartesian plane. Theoretically, those points should form a line, but that line may be perfectly horizontal, perfectly vertical, and anything in between.

I would like to design an algorithm that rates the 'straightness' of that line.

For example, the following data sets would be perfectly straight:

 Y = 2/3x + 4
 X  |  Y
---------
-3  |  2
 0  |  4
 3  |  6

 Y = 4
 X  |  Y
---------
 1  |  4
 2  |  4
 3  |  4

 X = -1
 X  |  Y
---------
-1  |  7
-1  |  8
-1  |  9

While this one would not:

 X  |  Y
---------
-3  |  2
 0  |  5
 3  |  6

I think it would work to minimize the sum of the squares of the distances of each point from to a line (usually called a regression line), then determine the average distance of each point to the line. Thus, a perfectly straight line would have an average distance of 0.

Because the data can represent a line that is vertical, as I understand it, the usual least-squares regression line won't work for this data set. A perpendicular least-squares regression line might work, but I've had little luck finding an implementation of one.

I am working in Excel 2010 VBA, but I should be able to translate any reasonable algorithm.

Thanks, PaulH

---

The reason things like RSQ and LinEst won't work for this is because I need a universal measurement that includes vertical lines. As a line's slope approaches infinity (vertical), their RSQ approaches 0 even if the line is perfectly straight or nearly so.

-PaulH

---

Sounds like you are looking for R², the coefficient of determinism.

Basically, you take the residual sum of squares, divide by the sum of squares and subtract from 1.

---

Use a Linear Regression. The "straightness" of the line is the R^2 value.

A value of 0 for the R^2 value implies it is perfectly straight. Increasing values imply increasing error in the regression, and thus the line is less and less "straight"

---

Could you try to catch the case of the vertical line before moving the least squares regression? If all x-values are the same, then the line is perfectly straight, no need to calculate an r^2 value.

---

Rough idea: 1. translate all coordinates to absolute values 2. calculate tan of current x/y 3. calculate tan of difference in x/y between current x/y and next x/y 4. difference in tan can give running deviation

---

Yes, use ordinary least squares method. Just use the Slope and Intercept functions in a worksheet. I expect there is a simple way to call these from the VBA codebehind.

Here's the VBA info. for R-Squared: http://www.pcreview.co.uk/forums/thread-1009945.php