ANOVA - Comparing averages of multiple data sets

In an earlier post, we used the Two Sample Mean Test to compare the mean of two samples (duh). If you have 3 samples you could use the same test, and compare between pairs of samples, i.e. sample 1 & sample 2, sample 2 & sample 3, and sample 1 & sample 3.

The problem arises when you have many more sample sets. Running this test means keeping track of the permutations. This approach is wastefully repetitive and you are likely to make mistakes.

Also, sometimes, you only want to know if there is at least one sample that is different from the rest. Scientific or medical break throughs are rare. In most experiments and trials, there will be no change/difference. A single test that tells you that there is no difference between the samples allows you to move on.

Is there a better method to compare the mean of multiple samples?

Enter ANOVA

Analysis Of Variance (ANOVA) is a statistical method used to compare the means between samples. It tells you if there's any significant difference between them. It will not tell you which sample is different, but in a single test, you will know whether it warrants further investigation.

Excel provides ANOVA with the Data Analysis package. Here I will show how ANOVA can be implemented using Excel LAMBA.

Implementing ANOVA in Excel LAMBDA

I will assume the data samples are in grouped in adjacent columns.

Step 1: Calculate entirety: number of data, sum, mean, variance, sum of squares.

    array_size, COUNT(array), array_grp,COLUMNS(array), array_sum, SUM(array), array_mean, AVERAGE(array), array_var, VAR.S(array),
    array_SUMSQ, SUMSQ( IF(array<>"", array-array_mean, array) ),

Step 2: Calculate each group's mean, variance, and sum of squares.

To do this we will use the BYCOL function.

    grp_size, BYCOL(array, LAMBDA(p_column, COUNT(p_column))),
    grp_sum, BYCOL(array, LAMBDA(p_column, SUM(p_column))),
    grp_mean, BYCOL(array, LAMBDA(p_column, AVERAGE(p_column))),
    grp_var, BYCOL(array, LAMBDA(p_column, VAR.S(p_column))),
    grp_SUMSQ, BYCOL( IF(array<>"", array-grp_mean, array), LAMBDA(p_column, SUMSQ(p_column))),

Step 3: Calculate the within group and between group sum of squares, degrees of freedom, and mean squares. The values come from Step 2.

    within_grp_SS, SUM(grp_SUMSQ),
    within_grp_df, array_size - array_grp,
    within_grp_MS, within_grp_SS / within_grp_df,
    between_grp_SS, total_grp_SS - within_grp_SS,
    between_grp_df, total_grp_df - within_grp_df,
    between_grp_MS, between_grp_SS / between_grp_df,
    between_grp_F, between_grp_MS/ within_grp_MS,

Step 4: Use Excel F.DIST to calculate the p-value and critical value.

    between_grp_p_value, F.DIST.RT(between_grp_F, between_grp_df, within_grp_df),
    between_grp_F_critical, F.INV.RT(0.05, between_grp_df, within_grp_df),

It is not difficult and learning to use BYCOL will assist in the intermediate calculation steps.

Putting it all together

The final implementation of ANOVA is as follows:

dcrMean.ANOVA.OneFactor.Test
=LAMBDA(array_data_in_columns, [show_details],
  LET(show_details, IF(ISOMITTED(show_details), FALSE, show_details),
    array, array_data_in_columns,
    array_size, COUNT(array), array_grp,COLUMNS(array), array_sum, SUM(array), array_mean, AVERAGE(array), array_var, VAR.S(array),
    array_SUMSQ, SUMSQ( IF(array<>"", array-array_mean, array) ),
    grp_size, BYCOL(array, LAMBDA(p_column, COUNT(p_column))),
    grp_sum, BYCOL(array, LAMBDA(p_column, SUM(p_column))),
    grp_mean, BYCOL(array, LAMBDA(p_column, AVERAGE(p_column))),
    grp_var, BYCOL(array, LAMBDA(p_column, VAR.S(p_column))),
    grp_SUMSQ, BYCOL( IF(array<>"", array-grp_mean, array), LAMBDA(p_column, SUMSQ(p_column))),
    total_grp_SS, array_SUMSQ,
    total_grp_df, array_size - 1,
    within_grp_SS, SUM(grp_SUMSQ),
    within_grp_df, array_size - array_grp,
    within_grp_MS, within_grp_SS / within_grp_df,
    between_grp_SS, total_grp_SS - within_grp_SS,
    between_grp_df, total_grp_df - within_grp_df,
    between_grp_MS, between_grp_SS / between_grp_df,
    between_grp_F, between_grp_MS/ within_grp_MS,
    between_grp_p_value, F.DIST.RT(between_grp_F, between_grp_df, within_grp_df),
    between_grp_F_critical, F.INV.RT(0.05, between_grp_df, within_grp_df),
    details_grp, HSTACK(
      VSTACK("Description", "Group", "Count", "Sum", "Mean", "Variance", "Sum of Squares"),
      VSTACK("", "Group " & SEQUENCE(1,COLUMNS(array)), grp_size, grp_sum, grp_mean, grp_var, grp_SUMSQ),
      VSTACK("", "Array", array_size, array_sum, array_mean, array_var, array_SUMSQ)
    ),
    details_anova, VSTACK(
      "ANOVA",
      HSTACK("Sources", "SS", "df", "MS", "F", "p-value", "F critical"),
      HSTACK("Between Groups", between_grp_SS, between_grp_df, between_grp_MS, between_grp_F, between_grp_p_value, between_grp_F_critical),
      HSTACK("Within Groups", within_grp_SS, within_grp_df, within_grp_MS),
      HSTACK("Total", total_grp_SS, total_grp_df)
    ),
    details, VSTACK(details_grp, details_anova),
    IF(show_details, IFNA(details,""), between_grp_p_value)
  )
)

Seeing ANOVA in action

Suppose we have 3 samples (Groups 1, 2 and 3). Using our Excel LAMBDA function, we select the data and indicate if we want the details to be shown.

This will return a summary of calculations leading to the p-value. In this particular example the value is 0.099... or ~10%. Testing against a significance value of 5%, we conclude that the differences between the groups (samples) are not significant enough, i.e. the samples are the similar.

The LAMBDA function assumes the data samples are in columns. We do not need to indicate the different samples, but we do need to place the samples in adjacent columns. The number of data in each sample does not need to be the same. Below is an example illustrating this.

NOTE: Cell highlighting is not possible with Excel LAMBDA.

Conclusion

The Excel LAMBDA ANOVA implementation is not difficult compared to some of the other previous statistical test functions. ANOVA compares the means of samples and determines whether the difference is significant.

Go put ANOVA in your DC-DEN!

NOTE:

By virtue that ANOVA compares means, it presupposes the samples are normally distributed. For non-normally distributed samples, ANOVA is not appropriate. Kruskal Wallis comparison of variance through ranking should be considered. I will cover Kruskal Wallis in the next blog.
ANOVA test tells you whether the samples are different. It does not tell you which sample is different. If you interested in which sample, then you might want to consider doing a Tukey Test.

Search This Blog

DC-Den