Calculating Regression Statistics

[cshenoy]

A few years ago, I posted a question in another forum about finding the standard error for regression statistics:

... What I really want that I can't find is a single function for is the standard error for the intercept. LINEST calculates it, but not STEYX. I'll have to go back to the definition. The problem seems to be that there isn't a direct function for the residual sum of squares. I need to compare a lot of different regressions based on some conditions. I was hoping to find an shortcut to calculating some of the statistics....

No one had an answer for me. Finally, I've found a way to easily get any regression statistic calculated by LINEST. Using INDEX(LINEST(y variables, x variables,0,1),1,# of y variables+1) I can get anything calculated by LINEST.

I'm not sure if anyone else has ever needed this, but it's helped me quite a bit. Might be applicable with other array functions. If anyone wants to see an example, let me know.

[HansV]

Thanks!

[Jan Karel Pieterse]

If I recall correctly, the ANOVA table generated by the Analysis Toolpak add-in's regression method also shows the relevant stats.

[cshenoy]

Jan Karel,

You're correct. The issue is not in finding the statistic, but in calculating just one number for a series of many regressions. For example, if you've got several hundred portfolios and want to calculate alpha or r2, it would be very time consuming to run the analysis toolpak or linest on each one to come up with a single number. Using INDEX(LINEST(...)), I can pull out any single coefficient, standard error, r2 or anything else calculated in the array function for a series of regressions.

Sometimes less is more!

[Jan Karel Pieterse]

Agreed!