XLSTAT: Two-way unbalanced ANOVA with interactions in Excel

 

Dataset for Example

The (attached) data come from an experiment testing four crop-growing methods across four field types.

Due to missing combinations—method 3 and method 2 were not tested on field type 4—the design is unbalanced.

An ANOVA with interactions was used to assess whether growing method significantly affects yield, accounting for field type and their interaction. XLSTAT’s ANOVA function supports this analysis despite the unbalanced design.

 

Setting Up

• Open XLSTAT.

• Select the Modeling data menu and the ANOVA feature: 

  

• Once you click the button, the ANOVA dialog box appears.

In the General tab:

• Select the data on the Excel sheet.

• Select "Yield" as the dependent variable (or variable to model). The objective is to assess the effect of Method, Field type, and their interaction on yield variability.

• Leave the Variable labels option activated, since column titles are included.

Note: In XLSTAT, you can select data for ANOVA in two different ways:

• Use the column format:

  • One column for the dependent variable.

  • Separate columns for each of the explanatory variables.

• Use the table format:

  • Enter the modalities of one explanatory variable in the columns.

  • Enter the modalities of the second explanatory variable in the rows.

 

In the Options tab:

• Activate the interactions option and set the maximum level of interaction to 2.

• Leave the constraint at a1 = 0, assuming that method "1" has the standard effect on yield.

  • This constraint is required for theoretical reasons, but it does not affect the model's results (goodness of fit or predictions). 

  • It only changes the way the model is expressed.

In the Outputs tab:

• Activate the Type SS options. This ensures that the model takes interactions into account. It also allows you to analyze the F values from the Type I SS and Type III SS tables (SS = sum of squares).

• Click the OK button to start the computations.

• A dialog box appears to confirm which factors should be included in the model.

• The results will then be displayed.

 

Interpreting the Results

XLSTAT first displays goodness-of-fit metrics, including R² and adjusted R².

In this case, the R² is 0.92, indicating that 92% of the yield variability is explained by the method, field type, and their interaction. The remaining 8% is attributed to unexplained random effects.

The ANOVA table helps assess whether the explanatory variables significantly improve the model. It tests the null hypothesis (H₀) that they add no value—essentially, whether using the overall mean alone is sufficient to describe the data.

Since the p-value for Fisher’s F is below 0.0003, there’s only a 0.03% risk in rejecting the null hypothesis. We can confidently conclude that the method, field type, and their interaction significantly affect yield. To assess how much information each contributes, we examine the Type I and Type III sum of squares tables.

The Type I SS table evaluates each variable’s impact as it's added to the model, so results depend on variable order. In contrast, the Type III SS table assesses each variable’s contribution independently of order, making it preferable when interactions are included.

According to the Type III SS results, the “Method” variable has the strongest effect on yield. Model parameters show that methods 2 and 3 positively impact yield. While field type has a minor effect, the method × field interaction remains important, with results evaluated at a 95% confidence level.

The table depicted above can be used to analyze the impact of the explanatory variables on the yield and/or to predict the average yield in a situation not yet covered by the experiment, such as the 3rd method and 4th type of field. In this particular example, the average yield would be 48.5, given the fact that the influence of the interaction is unknown.

The table can be used to assess variable effects or predict outcomes in untested scenarios—e.g., method 3 on field type 4—with an estimated yield of 48.5, assuming no interaction effect. Standardized residuals, expected to follow a normal distribution, all fall within [-1.96, 1.96], indicating no outliers and supporting the model’s normality assumption.