Description of numeric improvement in Analysis ToolPak ANOVA tools in Excel 2003 (829215)

MORE INFORMATION

Many functions require the calculation of the sum of squared deviations about a mean. To do so accurately, Excel 2003 uses a two-pass procedure that finds the mean on the first pass, and then calculates squared deviations about the mean on the second pass. In precise arithmetic, the same result occurs in earlier versions of Excel that use the "calculator formula" (so named because it was in widespread use when statisticians used calculators instead of computers). With the calculator formula, earlier versions sum the squares of the observations, and then subtracts from this total the following quantity:

((sum of observations)^2) / number of observations

This calculation occurs in a single pass through the data.

In finite precision arithmetic, the calculator formula is subject to roundoff errors in extreme cases. Excel 2002 and earlier use the calculator formula for most functions that require a sum of squared deviations about a mean (such as VAR, STDEV, SLOPE, and PEARSON). However, these versions of Excel also use the more numerically robust two-pass procedure for the CORREL, COVAR, and DEVSQ functions.

Experts in statistical computing recommend not using the calculator formula. The calculator formula is presented as "how not to do it" in texts about statistical computing. Unfortunately, all three of the Analysis ToolPak (ATP) ANOVA tools make widespread use of the calculator formula or an equivalent single pass approach in Excel 2002 and earlier.

Excel 2003 uses the two-pass procedure for all three ATP ANOVA models. This article discusses these computational improvements in ATP's three ANOVA models:

Single Factor
Two-Factor with Replication
Two-Factor without Replication

This article discusses these models later.

Because Excel has always used the two-pass procedure with DEVSQ, this article makes frequent use of it to describe the improved procedures. These revised procedures either effectively call DEVSQ or use code whose functionality is exactly the same as DEVSQ's functionality.

For each ANOVA tool, ATP output contains a Summary table with values of Count, Sum, Average, and Variance, and an ANOVA table that has various sums of squares and values of SS, df, MS, F and P-value. Results in the summary table are calculated by calling Excel functions COUNT, SUM, AVERAGE, and VAR. Of these four functions, only VAR is subject to roundoff errors. Excel 2002 and earlier versions implement VAR with the calculator formula. The article about VAR describes the improvements for Excel 2003 and permits the reader to experiment with numeric data to see when roundoff errors are likely to occur in earlier versions.

For additional information about VAR, click the following article number to view the article in the Microsoft Knowledge Base:

826112 Excel Statistical Functions: VAR

As it discusses the three ANOVA models, this article focuses on the ANOVA output tables. In each case, the Summary tables are well-behaved in Excel 2003. In Excel 2002 and earlier, problems occur in the Variance column when data have extreme values. However, this article includes the Summary tables in the model sections because they are useful for comparison purposes when you review the modified examples in the Appendix.

Model 1: Single Factor

This is a simple example with data.

ANOVA 1 BASIC MODEL:
1	2	3
2	4	4
3	6	5
4	8	6
5		7
6		8
Anova: Single Factor

SUMMARY
Groups	Count	Sum	Average	Variance
Column 1	6	21	3.5	3.5
Column 2	4	20	5	6.666667
Column 3	6	33	5.5	3.5


ANOVA
Source of Variation	SS	df	MS	F	P-value	F crit
Between Groups	12.75	2	6.375	1.506818	0.257897	3.805567
Within Groups	55	13	4.230769

Total	67.75	15

Excel 2002 and earlier use the following pseudocode to calculate the sums of squares:

GrandSum = 0;
GrandSumOfSqs = 0; 
GrandSampleMeanSqrd = 0; 
GrandMeanSqrd = 0; 
GrandSampleSize = 0;

For s = 1 to Number_of_Samples do
   GrandSum = GrandSum + sum of observations in s-th sample;
   GrandSumOfSqs = GrandSumOfSqs + sum of squared observations in s-th sample;
   GrandSampleMeanSqrd = GrandSampleMeanSqrd  +
      (sum of observations in s-th sample^2)/size of s-th sample;
   GrandSampleSize = GrandSampleSize + size of s-th sample
Endfor;

GrandMeanSqrd = (GrandSum^2) / GrandSampleSize;

TotalSS = GrandSumOfSqs - GrandMeanSqrd;
BetweenGroupsSS = GrandSampleMeanSqrd - GrandMeanSqrd;
WithinGroupsSS = GrandSumOfSqs - GrandSampleMeanSqrd;

This approach is essentially the calculator formula: compute the sums of squares of observations, and then subtract a quantity from them, just as VAR computes the sum of squares of the observations, and then subtracts sum of observations^2/sample size. Similar pseudocode for the model 2 and model 3 has been omitted. Again, for model 2 and model 3, sums of squares are calculated and a quantity is subtracted from the sum of squares as in the calculator formula. Unfortunately, basic statistics texts frequently suggest approaches for ANOVA such as the one shown earlier in this article.

Excel 2003 uses a different approach to calculate the various entries in the SS column of the ANOVA table. For illustration, this article assumes that the numeric data in the earlier example appear in cells A2:C7 with missing data in cells B6 and B7:

Total SS is just DEVSQ applied to all the data, such as DEVSQ(A2:C7). DEVSQ works correctly even though data is missing.
Between Groups SS is Total SS minus the sum of DEVSQ applied to each column, such as DEVSQ(A2:A7) + DEVSQ(B2:B7) + DEVSQ(C2:C7).
Within Groups SS is Total SS minus Between Groups SS.

If entries in the SS column of the ANOVA table are calculated correctly, the accuracy of the other entries in the table follow.

Model 2: Two-Factor with Replication

Here is a simple example with data.

ANOVA 2 BASIC MODEL	group 1	group 2	group 3
trial 1	1	2	3
	2	4	4
	3	6	5
trial 2	4	8	6
	5	10	7
	6	12	8
Anova: Two-Factor With Replication

SUMMARY	group 1	group 2	group 3	Total
trial 1
Count	3	3	3	9
Sum	6	12	12	30
Average	2	4	4	3.333333
Variance	1	4	1	2.5

trial 2
Count	3	3	3	9
Sum	15	30	21	66
Average	5	10	7	7.333333
Variance	1	4	1	6.25

Total
Count	6	6	6
Sum	21	42	33
Average	3.5	7	5.5
Variance	3.5	14	3.5


ANOVA
Source of Variation	SS	df	MS	F	P-value	F crit
Sample	72	1	72	36	6.22E-05	4.747221
Columns	37	2	18.5	9.25	0.003709	3.88529
Interaction	9	2	4.5	2.25	0.147973	3.88529
Within	24	12	2

Total	142	17

Again, if entries in the SS column are calculated correctly, the accuracy of all the other entries in the ANOVA part of the output follow.

Here is the Excel 2003 computational procedure. It uses DEVSQ to calculate the various entries in the SS column of the ANOVA table. For illustration, this example assumes that the numeric data appear in cells B2:D7.

Total SS is just DEVSQ applied to all the data, such as DEVSQ(B2:D7).
Sample SS is Total SS minus the sum of DEVSQ applied to each sample, such as DEVSQ(B2:D4) + DEVSQ(B5:D7).
Columns SS is Total SS minus the sum of DEVSQ applied to each column, such as DEVSQ(B2:B7) + DEVSQ(C2:C7) + DEVSQ(D2:D7).
Within SS is the sum of DEVSQ applied to each trial or group pair, such as DEVSQ(B2:B4) + DEVSQ(C2:C4) + DEVSQ(D2:D4) + DEVSQ(B5:B7) + DEVSQ(C5:C7) + DEVSQ(D5:D7).
Interaction SS equals Total SS minus Sample SS minus Columns SS minus Within SS.

Model 3: Two-Factor without Replication

Here is a simple example with data.

ANOVA 3 BASIC MODEL:	LOW	MED	HI
POOR	1	2	3
	2	4	4
	3	6	5
MID CLASS	4	8	6
	5	10	7
	6	12	8
RICH	7	14	10
	8	12	6
	9	10	2

Anova: Two-Factor Without Replication

SUMMARY	Count	Sum	Average	Variance
POOR	3	6	2	1
	3	10	3.333333	1.333333
	3	14	4.666667	2.333333
MID CLASS	3	18	6	4
	3	22	7.333333	6.333333
	3	26	8.666667	9.333333
RICH	3	31	10.33333	12.33333
	3	26	8.666667	9.333333
	3	21	7	19

LOW	9	45	5	7.5
MED	9	78	8.666667	16
HI	9	51	5.666667	6.25


ANOVA
Source of Variation	SS	df	MS	F	P-value	F crit
Rows	176.6667	8	22.08333	5.76087	0.001476	2.591094
Columns	68.66667	2	34.33333	8.956522	0.002455	3.633716
Error	61.33333	16	3.833333

Total	306.6667	26

If the values in the SS column are calculated correctly, the accuracy of all the other values in the ANOVA table follows.

Excel 2003 uses the following computational procedure. The procedure uses DEVSQ to calculate the values in the SS column of the ANOVA table. For illustration, this example assumes that the range of cells shown in the earlier example is cells A1:D10. Therefore, the numeric data appear in cells B2:D10.

Total SS is just DEVSQ applied to all the data, such as DEVSQ(B2:D10).
Rows SS is Total SS minus the sum of DEVSQ applied to each row, such as DEVSQ(B2:D2) + DEVSQ(B3:D3) + DEVSQ(B4:D4) + DEVSQ(B5:D5) + DEVSQ(B6:D6) + DEVSQ(B7:D7) + DEVSQ(B8:D8) + DEVSQ(B9:D9) + DEVSQ(B10:D10).
Columns SS is Total SS minus the sum of DEVSQ applied to each column, such as DEVSQ(B2:B10) + DEVSQ(C2:C10) + DEVSQ(D2:D10).
Error SS is Total SS minus Rows SS minus Columns SS.

Results in Earlier Versions of Excel

In extreme cases where there are many significant digits in the data but also a small variance, the calculator formula leads to inaccurate results. The Appendix later in this article gives examples of roundoff problems in such extreme situations.

Results in Excel 2003

Excel 2003 uses a procedure that makes two passes through the data. On the first pass, Excel 2003 calculates the sum and count of the data values. From these Excel can calculate the sample mean (average). On the second pass, Excel calculates the squared difference between each data point and the sample mean, and then sums these squared differences. As a result, the results in Excel 2003 are more stable numerically.

Conclusions

A two-pass approach improves the numeric performance in all three ATP ANOVA tools in Office Excel 2003 as compared to earlier versions. Office Excel 2003 results are never less accurate than results in earlier versions.

In most practical cases, however, there is no difference between Office Excel 2003 results and results in earlier versions of Excel because typically, data do not exhibit the kind of unusual behavior that the following Appendix illustrates. numeric instability is most likely to occur in earlier versions of Excel when data contains a high number of significant digits with relatively little variation between data values.

If you use an earlier version of Excel and want to see whether Excel 2003 gives you different ANOVA results, compare the results of using the ANOVA tools in your earlier version with the results of the procedures that use DEVSQ that are described earlier in this article for the ANOVA table associated with each of the tools. To verify that Variances are correct in the Summary table for each range, use DEVSQ(range)/(COUNT(range) - 1).

Appendix: numeric Examples of Excel 2002 and Earlier Performance

For each basic example from models 1, 2, and 3, this article presents the ATP tool's output, including both the Summary and ANOVA tables, earlier. Data was modified in each example to create a "stressed" example. This is done by adding 10^8 to each data value. Adding a constant such as 10^8 to each data value does not affect Variance in the Summary table (but will affect Average and Sum in obvious ways). It should also not affect any entry in the ANOVA table.

If you compare Variances in the Summary tables and SS in the ANOVA tables, you will notice that all of these are incorrectly calculated in all three of the following stressed models except for one entry in model 3 that is pointed to with "<---". In all the stressed cases, Excel 2003 ANOVA results agree with the earlier results in the basic cases (as they should).

ANOVA 1 STRESSED MODEL WITH LARGE DATA VALUES

100000001	100000002	100000003
100000002	100000004	100000004
100000003	100000006	100000005
100000004	100000008	100000006
100000005		100000007
100000006		100000008

Anova: Single Factor

SUMMARY
Groups	Count	Sum	Average	Variance
Column 1	6	600000021	1E+08	4.8
Column 2	4	400000020	1E+08	8
Column 3	6	600000033	1E+08	1.6


ANOVA
Source of Variation	SS	df	MS	F	P-value	F crit
Between Groups	0	2	0	0	1	3.805567
Within Groups	64	13	4.923077

Total	64	15

ANOVA 2 STRESSED MODEL WITH LARGE DATA VALUES

	group 1	group 2	group 3
trial 1	100000001	100000002	100000003
	100000002	100000004	100000004
	100000003	100000006	100000005
trial 2	100000004	100000008	100000006
	100000005	100000010	100000007
	100000006	100000012	100000008
Anova: Two-Factor With Replication

SUMMARY	group 1	group 2	group 3	Total
trial 1
Count	3	3	3	9
Sum	300000006	300000012	300000012	9E+08
Average	100000002	100000004	100000004	1E+08
Variance	0	4	0	4

trial 2
Count	3	3	3	9
Sum	300000015	300000030	300000021	9E+08
Average	100000005	100000010	100000007	1E+08
Variance	0	4	0	6

Total
Count	6	6	6
Sum	600000021	600000042	600000033
Average	100000004	100000007	100000005.5
Variance	4.8	14.4	1.6


ANOVA
Source of Variation	SS	df	MS	F	P-value	F crit
Sample	64	1	64	24	0.000367	4.747221
Columns	32	2	16	6	0.015625	3.88529
Interaction	32	2	16	6	0.015625	3.88529
Within	32	12	2.666666667

Total	128	17

ANOVA 3 STRESSED MODEL WITH LARGE DATA VALUES

	LOW	MED	HI
POOR	100000001	100000002	100000003
	100000002	100000004	100000004
	100000003	100000006	100000005
MID CLASS	100000004	100000008	100000006
	100000005	100000010	100000007
	100000006	100000012	100000008
RICH	100000007	100000014	100000010
	100000008	100000012	100000006
	100000009	100000010	100000002

Anova: Two-Factor Without Replication

SUMMARY	Count	Sum	Average	Variance
Row 1	3	300000006	100000002	0
Row 2	3	300000010	100000003	2
Row 3	3	300000014	100000005	2
Row 4	3	300000018	100000006	4	<---
Row 5	3	300000022	100000007	6
Row 6	3	300000026	100000009	10
Row 7	3	300000031	100000010	12
Row 8	3	300000026	100000009	10
Row 9	3	300000021	100000007	18

Column 1	9	900000045	100000005	8
Column 2	9	900000078	100000009	14
Column 3	9	900000051	100000006	4


ANOVA
Source of Variation	SS	df	MS	F	P-value	F crit
Rows	128	8	16	2	0.113281	2.591094
Columns	32	2	16	2	0.167772	3.633716
Error	128	16	8

Total	288	26