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

Lalikos

New Member
Joined
May 16, 2016
Messages
2
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.
Can anyone help please?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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:
Upvote 0
Hello sir and thanks for your reply.
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top