Regression with VBA

**ellizar** · 11-20-2015, 07:41 AM

Hello! Just registered to this forum, it seems very interesting and helpfull.

I want to do some regressions on excel (about 900 regressions). I know nothing about vba or macros.

I want to run a regression of Y for every Xi variable

Y

X1

X2

X3

Xn

I found this

Please Login or Register  to view this content.

at ozgrid.com forum, which seems to work, as I get the same results with running the regression with the add-in tool. I read that you can repeat the macro process.

How I must modify it to run it for all my Xi variables?

Also, is there a way to choose if I want to receive the results on the same sheet, or every regression on new sheets, or new books?

Thank you!

**MrShorty** · 11-20-2015, 09:57 AM

IMO, the easiest way (especially for someone who "know[s] nothing about vba or macros"), is to use the LINEST() function instead (I am assuming you know more about using worksheet functions). Help file for the LINEST() function: https://support.office.com/en-us/art...a-fa7abf772b6d

If I understand what you are trying to do, this should be fairly easy. You would create the LINEST() function once. If you pay attention to the relative and absolute references (assuming you are familiar with this spreadsheet concept), you should then just need to copy the function to get the rest of the regressions. If you are unfamiliar with array functions, be sure to review that section of the help file. Since your data are in columns, you may want to nest the LINEST() function within a TRANSPOSE() function to get the LINEST() output as a vertical array.