Analysis ToolPack Excel

chiaretta,

FWIW, I never use the Analysis ToolPak to do regression analysis. Instead, I use LINEST function array-entered into a range of cells. The number of rows is fixed at 5, but the number of columns should equal the number of independent variables plus 1.

Let's suppose I have x data in A2:A20 and y data in B2:B20 and want to do a regression analysis using a cubic polynomial. I would select a 5 row by 4 column range of cells, then array-enter this formula:

=LINEST($B$2:$B$20,$A$2:$A$20^{1,2,3},TRUE,TRUE)

Array-entering means holding the Control and Shift keys down while hitting Enter. Excel will add curly braces surrounding your formula if you do it right.

The reason for selecting a 5 row by 4 column range of cells is that LINEST returns an array of data. The first row contains the coefficients in reverse order (constant on the right, cubic term on the left). The third row contains the R-squared. The fourth row contains the F statistic and degrees of freedom.

If you have multiple independent variables in cells C2:E20, your formula would be:

=LINEST(B2:B20,C2:E20,TRUE,TRUE)

This formula would still be array-entered into a 5 row by 4 column range of cells.