VBA Linear Regression function?

[Daniel86]

Hi everyone,

So, I want to code a VBA function that can make a linear regression and estimate the Beta-coefficients on two variables.
I know how to do the whole thing using matrix algebra (and of course the Data Analysis Toolpack), but I would love to have a VBA function for it.

So, I have a Y-vector and then a matrix consisting of a X1 and X2 data (as attached) and I need to estimate the:
1) Intercept
2) Beta1
3) Beta2

For the linear regression function: Y = alpha + B1X + B2X


Does anybody have any ideas?

[:) Sixthsense :)]

Check whether this helps you

http://www.freevbcode.com/ShowCode.asp?ID=9070

[MrShorty]

What role do you need the VBA code to play? Multiple linear regression is quite straightforward using the LINEST() worksheet function: https://support.office.com/en-us/art...rs=en-US&ad=US

You can simply hand enter the function into the spreadsheet, and bypass any use of VBA.

VBA can enter the LINEST() function into an array of cells (you will need to become familiar with the .formulaarray property of the desired range: https://msdn.microsoft.com/EN-US/lib.../ff837104.aspx

The LINEST() function is also available to be used in VBA, if you want to return the regression coefficients to a VBA array: https://msdn.microsoft.com/en-us/lib...ice.11%29.aspx

[Daniel86]

Oh, yeah. Well, I would like to do the calculations as matrix calculations in VBA.
However, I have no idea on where to start. For example, it would be awesome to make a function that takes input as ranges -> i.e. Regression(Yrange, range2, range3) and then converts them to vectors/matrices and does calculation from there.

[MrShorty]

Help us understand your requirements. Are you suggesting that you do not want or cannot use the built in LINEST() function for these regressions? Are you allowed to use other Excel functions (such as the built in matrix functions such as MINVERSE())? Ore are you required to write your own regression/matrix inversion algorithm from the bottom up? What you describe is essentially the LINEST() function, so it seems important to me to understand why you do not want or cannot use it.

[Daniel86]

Ooh, nevermind :-)
I solved it.

This is the code:

Please Login or Register  to view this content.

[shg]

Or ...

Please Login or Register  to view this content.