The following will work for completely balanced designs, and is better
numerically than what is in the Analysis ToolPak. For unbalanced
designs, you could form and solve the Normal Equations ... but it would
be a lot of work and not very numerically accurate (the Normal Equations
are a great theory/teaching tool, but a lousy numerical method). If you
want to do unbalanced designs, you would be better served to switch to a
dedicated statistics program. R is reasonable and free
www.r-project.org
If y_data is the cell range containing the observed values (no empty
cells), and id_data is a cell range of the same shape that indicates the
appropriate classification for a particular factor, then the following
array formula (Ctrl-Shift-Enter)
=DEVSQ(SUMIF(id_data,id_data,y_data)/COUNTIF(id_data,id_data))
gives the corrected sum of squares for this factor before all other
factors. In a balanced design, this can be used for each main effect.
You would need similar id_data ranges for each interaction term, but
would then have to subtract off SS for corresponding main effects and
lower order interactions. Similarly the array formula
=SUM(1/COUNTIF(id_data,id_data))
would give the number of levels for this factor. Subtract 1 to get
degrees of freedom for a main effect. Also subtract df for each
corresponding main effect and lower order interaction to get df for an
interaction.
The corrected total sum of squares is
=DEVSQ(y_data)
with degrees of freedom
=COUNT(y_data)-1
so the residual line can be obtained by subtraction.
Jerry
