ANOVA with Two-Factor Without Replication - Contains non-numeric data

Question

Hello,

I am trying to run a ANOVA with two-factor without replication, however, I keep getting this alert that my input contains non-numeric data. I have tried to format it as a number and by using the text to column function but nothing seems to work. I do have data missing in my table but there is at least one values in each row (so I should be okay). I looking to find the ICC therefore I need the ANOVA information to do so. Please help.

Answer 1

Add zero to blank cells. Then it will work.

Answer 2

The ANOVA "rows sum of squares" calculation involves matrix multiplication and for this it requires data in all cells. Here is a formula equivalent calculation:

=SUMSQ(MMULT(E4:H11,ROW(1:4)^0))/COUNT(E4:H4)-SUM(E4:H11)^2/COUNT(E4:H11)

The problem lies in the MMULT(E4:H11,ROW(1:4)^0) portion which evaluates to #VALUE!

This is interesting because you can calculate the internal components and MMULT will make a calculation and the whole formula will return a result. However,

Microsoft say: MMULT returns the #VALUE! error when:

• Any cells are empty or contain text
• ....

hence our ANOVA calculation fails.

Answer 3

To use Excel's Analysis ToolPak "Two-Factor ANOVA without Replication" you need an observed value for each combination of the two factors.

You have useful values only for the row effect Competition 1,2,3,4 and column effect Person 1,3,4,7,8,10, i.e., 4 rows by 6 columns.

If you rearrange your data and include labels, your input range will be five rows by seven columns (with an empty cell in the top left corner), e.g., A1:G5.

Answer 4

Will this affect the value of the output? I don't want it to affect the overall output the blank cell really just represents an n/a.

Answer 5

Hi Michael,

The only problem is I need to have all the values in the input because in the end I am looking for intraclass correlation to see if they are nested of not within each team. Is there any way to do that?