Fix #NUM Errors in Excel Two-Factor ANOVA with Replication
Question details
The user needs to understand why Excel returns #NUM! for the Sample and Interaction P-values in a two-factor ANOVA with replication while the Columns result remains valid.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is running a two-factor ANOVA with replication on a dataset and reviewing the P-values for Sample, Columns, and Interaction effects.
- Observed behavior
- The Columns P-value calculates normally, but the Sample and Interaction P-values display #NUM!.
Before you start, make sure the Data Analysis command is available on the Data tab; if it is not visible, enable the Analysis ToolPak or Data Analysis add-in from the add-ins manager. Then check that your input range includes row and column labels and that each factor combination has at least two replicated observations.
Verify Input Layout and Replication for Two-Factor ANOVA
Use this first when #NUM! appears only for Sample and Interaction P-values while the Columns P-value remains valid.
The #NUM! error is not a mistake in the P-value formula itself. It occurs when the Sample or Interaction sum of squares and mean squares are zero, which makes the F statistic zero and leaves the significance calculation undefined. Most cases are resolved by correcting the data range, labels, and replication count.
In WPS Office Spreadsheets, open the workbook and click Data > Data Analysis, then choose Anova: Two-Factor With Replication from the list.
In the Anova: Two-Factor With Replication dialog box, set the Input Range to include exactly one row of column labels, one column of row labels, and at least two observations for every factor combination.
Enter the correct Rows per sample value so it equals the number of replicated observations in each sample group; an incorrect value can create empty or zero-variance effects.
Check that the sample groups and interaction combinations have different averages; if a factor is constant, its sums of squares are zero and the P-value cannot be computed.

Check for Zero Sample or Interaction Variance in the ANOVA Table
Use this when the data layout is correct but the ANOVA output still shows zero SS and MS for Sample or Interaction.
Use the Correct Analysis When Only One Factor Varies
Use this when the column factor is meaningful but the sample or interaction effect is structurally zero in the experimental design.
Run Two-Factor ANOVA With Replication in WPS Office Spreadsheets
You can rerun the two-factor ANOVA with replication in WPS Office Spreadsheets, review the same ANOVA source table, and correct #NUM! P-values by checking the input range, replication count, and factor variation.
- 1. Open the Excel workbook: Launch WPS Office Spreadsheets and open the .xlsx workbook containing the two-factor ANOVA data.
- 2. Start the ANOVA tool: Click the Data tab, choose Data Analysis, and select Anova: Two-Factor With Replication.
- 3. Configure the analysis: Select the input range including row and column labels, enter the correct Rows per sample value, and choose an Output Range.
- 4. Review the results: Click OK and inspect the Sample, Columns, and Interaction rows in the ANOVA table for #NUM! P-values.
- 5. Correct and rerun: If Sample or Interaction shows #NUM!, return to the data layout, ensure every factor combination has replicated observations and varying averages, then rerun the analysis.

Frequently Asked Questions
Why does Excel return #NUM! for Sample and Interaction P-values even though Columns is valid?
The #NUM! value appears when the Sample or Interaction sum of squares and mean squares are exactly zero. This produces an F statistic of zero, and the P-value and F critical value cannot be calculated for that effect. Columns can still be valid because its variance is not zero.
How do I fix #NUM! in the Sample row of two-factor ANOVA with replication?
Check the input range in Data > Data Analysis > Anova: Two-Factor With Replication. Confirm that Rows per sample matches the number of replicates for each factor combination, and make sure the row factor levels actually vary. If all sample means are equal, the Sample effect is zero and no P-value can be computed.
Can WPS Office open and run a two-factor ANOVA with replication workbook from Excel?
Yes. Open the .xlsx workbook in WPS Office Spreadsheets, click the Data tab, choose Data Analysis, select Anova: Two-Factor With Replication, and configure the same input range, rows per sample, and output range.
What should I check when the Interaction P-value is #NUM!?
Confirm that every combination of the row and column factors has replicated observations and that the interaction cell averages are not all identical. Zero interaction variance makes the interaction F statistic zero, so the P-value is undefined and the data layout must be corrected.




