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

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 range of the Known X's and Y's (just like the slope function) and the x and y values.
Can anyone help please?

Hi Lalikos,
Try this:

Please Login or Register  to view this content.

Let me know if you have any issues/questions

Originally Posted by spitfireblue

Hi Lalikos,
Try this:

Please Login or Register  to view this content.

Let me know if you have any issues/questions

Thank you so much man, that worked for me!! And now I have two (and the last i promise) more questions.

1) How can I lock the X & Y range through the code (just like what the "$" symblom does) because I want to use the AutoFill option, and just drag it down for each empty cell, but the range everytime moves one cell down. For example if a table of X & Y's is the (A:1,B:10), in the rest of the cells which they lie underneath, AutoFill will every time change the range (addind one cell) e.g.(A:2,B:11) to (A:3,B:12) to (A:4,B13) and so on..

And..

2) How can I save this function in order to use it again in the future?

Hi,
Not sure I completely understand your first question, but have a look at this attachment and let me know if this is what you are after: Example.xlsm

For your second question, you can set up the function as an addin that opens every time you open Excel. Here is what you need to do:

1. Open a new workbook and copy the function into a new module
2. Save the workbook in C:\Users\(your username)\AppData\Roaming\Microsoft\AddIns as an xlsm (this becomes your backup)
3. Save the workbook again, this time as an .xlam file in the same folder
4. Open Excel and go to Excel Options > Add-Ins > Go and then find your new Add In and tick the box, then select OK

Let me know how you go and if you have any further questions.

Cross=posted at http://www.mrexcel.com/forum/excel-q...ml#post4524525

Originally Posted by spitfireblue

Hi,
Not sure I completely understand your first question, but have a look at this attachment and let me know if this is what you are after: Attachment 461313

Yes that's exactly what I mean! At your attach sir, at the CalcDist function cell(s) (D column) you have placed the $ dollar symbol to refer these values.
I personaly didn't place the $ symbol and used the autocomplete, taking the results that I'm posting.
So is it possible to modify the code so I can use this function only to the first cell of the column and then just drag it down (using the auto-Fill) taking the right results?

Screenshot_1.jpg Screenshot_2.jpg

Originally Posted by Lalikos

So is it possible to modify the code so I can use this function only to the first cell of the column and then just drag it down (using the auto-Fill) taking the right results?

Not really, you just need to add the $ signs in to make it an absolute reference.
Have a look at this website that explains how relative and absolute references work: http://www.gcflearnfree.org/excel2013/15.2

Originally Posted by Lalikos

Hello. Well I don't know if this is illegal or something, or if these two forums have (?) any hostility between them, but the reason I posted on both forums, was that I would have greater chance of getting 1) a reply and 2) the solution I needed. I'm really sorry if I wasn't allowed to do this.

It is a good idea to read the rules before starting a thread. As per rule #8 you can cross-post but you should provide a link, which is what shg has done for you.

... deleted ...