Data Analysis Add In - Storing Multiple Regression Coefficients

Hi Guys,

I am doing some statistical work with Excel 2007 and have been using the data analysis add-in to do some regression. In terms of a standard linear regression, one can enter the =slope(known y's , known x's) formula to find the regression coefficient for x. My regression is slightly more complicated as it has multiple independent variables (i.e. the y variable is not just a function of x, but also of another variable, say z). I can perform a multiple regression easily but I would like to be able to store the coefficients of the multiple regression as I must eventually add the coefficient estimate for x and z together. The multiple regression output is in a standard format however as I have to repeat this process for 1500 firms (one regression for each) it would be much more convenient if excel just reported the two coefficients for each regression. That way I could copy a formula across all firms and not use the standard regression window 1500 times.

Is there anyway I can perform the multiple regression formulaically, outputting just the two coefficients I require from each regression?

I apologise for the long winded post. Any help is very much appreciated. Thanks in advance.

Hi

Try using LINEST

If you data is in columns A1-C12, with x in A, z in B and y in C then array enter the formula
=LINEST(C2:C12,A2:B12,,TRUE)
in A17:C17. This will give you the intercept and the 2 coefficients in the order z, x and intercept.

rylo

Originally Posted by rylo

Hi

Try using LINEST

If you data is in columns A1-C12, with x in A, z in B and y in C then array enter the formula
=LINEST(C2:C12,A2:B12,,TRUE)
in A17:C17. This will give you the intercept and the 2 coefficients in the order z, x and intercept.

rylo

Thanks Rylo, That did the trick!