# VBA code to calculate the distance between point and the regression line

#### Lalikos

##### New Member
Hello guys. I made a function that calculates the distances between the points (of a X,Y table) and the regression line, while you need only the X and Y ranges/tables and of course their values. Here's what it looks like:

=ABS((ABS(SLOPE(J23:J32,I23:I32))*I23-J23+INTERCEPT(J23:J32,I23:I32))/SQRT((SLOPE(J23:J32,I23:I32)^(2)+(1))))

Where J23:J32 & I23:I32 happens to be the Y and X value ranges/tables respectively. And I23 is the X and the J23 is the Y.

So I'm basicaly trying to make a code in VBA for this function so I can use this regularly because of my job but unfortunately, I have no idea. I'd like the function to ask only for the Known X's and Y's (just like the slope function) and the x and y.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### RickXL

##### MrExcel MVP
Hi and welcome to the MrExcel Message Board.

I may not be understanding this correctly but I am not sure why you have a Sqrt(Slope^2 + 1) in there.

Also, I think I would use FORECAST and not SLOPE and INTERCEPT.

For example:
=ABS(FORECAST(I23,\$J\$23:\$J\$32,\$I\$23:\$I\$32)-J23)

If you want to do it that way then in VBA you could use:
Code:
``````Sub Test()

Dim f As Double

f = WorksheetFunction.Forecast(Range("I23"), Range("J23:J32"), Range("I23:I32")) - Range("J23")

MsgBox f

End Sub``````

Last edited:

#### Lalikos

##### New Member
Well basicaly I'm working with two variables, an Input and a Target variable. I create a scatter plot adding a trendline and the R squared, and I try to keep it relatively high (R^2>=0.75). But keeping it high mean that I have to remove some of the outliers from the data tables, and because the points are two-dimensional some of the traditional ways of removing the outliers (such as the Average +/- 3*St.Deviations) doesn't work for me. And what i'm trying to do is to calculate the distance for each point between them and the trendline using the algebric equation |Ax+By+C|/SQRT(A^2+B^2). We already know that B=-1 and also that the A(=SLOPE function) and C(=INTERCEPT function). By calculating the distances I can determine which of the points have the larger distance and so which of the points increase the statistical error. I know that I can erase the point manualy, but I have to justify it for my thesis, and also it works perfectly. Moreover to predict some values that I need, for the moment I use a neural network.

#### RickXL

##### MrExcel MVP
OK, I think I have got it.

You want t calculate the perpendicular distance between the point and the line and not the vertical (or horizonta) distance.
I think this works but there are all kinds of ways of putting it together.
Code:
``````Sub Test()

Dim Ans        As Double
Dim r1         As Range
Dim r2         As Range
Dim slope      As Double
Dim Intercept  As Double
Dim X          As Double
Dim Y          As Double

Set r1 = Range("I23:I32")
Set r2 = Range("J23:J32")

slope = WorksheetFunction.slope(r2, r1)
Intercept = WorksheetFunction.Intercept(r2, r1)
X = Range("I23").Value
Y = Range("J23").Value

Ans = Abs((slope * X - Y + Intercept) / Sqr(slope ^ 2 + 1))
MsgBox Ans

End Sub``````

#### shg

##### MrExcel MVP
Why do you want to calculate the perpendicular distance of the points from the regression line as a measure of determining outliers, rather than vertical distance, which is what the regression minimizes?

Replies
0
Views
333
Replies
9
Views
212
Replies
0
Views
375
Replies
2
Views
170
Replies
5
Views
197