Excel doesn’t provide tools for ANOVA with more than two factors. On this webpage we show how to construct such tools by extending the analysis provided in the previous sections. See Real Statistics Support for Three Factor ANOVA for how perform the same sort of analysis using the **Real Statistics Three Factor ANOVA** data analysis tool.

Alternatively, see ANOVA using Regression for how to perform ANOVA with any number of factors using regression (either using the standard Excel **Regression** data analysis tool or the Real Statistics **Linear Regression** data analysis tool).

We begin by extending the definitions from Two Factor ANOVA with Replication to three factors.

**Definition 1**: Using the terminology of Definition 1 of Two Factor ANOVA with Replication (where we use *a* and *b* instead of *r* and *c*), define

As before, we can also define the between groups terms. This time there are four types:

And similarly for *SS _{BetAC}, df_{BetAC}, SS_{BetBC}, df_{BetBC},*. There is also the following version:

As usual, we can define the error terms:

**Property 1**:

Proof: The proof is similar to the proof of Property 1 of Two Factor ANOVA with Replication.

**Property 2**:

Similar properties hold for the between AC and BC terms. We also have the following properties about the between ABC term:

**Property 3**: If a sample is made as described in Definition 1 with the *x _{ijkl} *independently and normally distributed and with all (or ) equal, then

**Theorem 1**: Suppose a sample is made as described in Definition 1, with the *x _{ijk }*independently and normally distributed.

If all *μ _{i}* are equal and all are equal then

If all *μ _{j} *are equal and all are equal then

Also, under certain circumstances,

Proof: The result follows from Property 1 and Theorem 1 of F Distribution.

**Property 4**:

Here terms like *(βγ) _{jk}^{2}* are not a product of a beta and gamma term squared. Instead such as term refers to a single parameter named

*(βγ)*which is squared.

_{jk}**Observation**: We use the following tests:

Recall that the assumptions for using these tests are:

- All samples are drawn from normally distributed populations
- All populations have a common variance
- All samples are drawn independently from each other
- Within each sample, the observations are sampled randomly and independently of each other

We now show how to conduct the above tests in Excel. Excel doesn’t have a three factor ANOVA data analysis tool, and so we will need to carry out the analysis using Excel formulas.

**Example 1**: An Italian research psychologist decides to conduct an experiment to understand the ability of subjects to perform simple tasks when instructed in Italian. She creates 8 sample groups, each with 12 subjects. The three factors are: gender (male/female) of the subject, nationality of the subject (Italian/foreign) and whether the subject performs the task seated or lying down (Seated/Prone). Each participant is given a test to measure their ability to perform the required tasks, with the scores recorded in the table shown in Figure 16.19. The psychologist wants to know if there are any significant differences between the groups.

**Figure 1 – Data for Example 1**

We begin by constructing the tables shown in Figure 2.

**Figure 2 – Counts and means for Example 1**

Now using the information in Figure 1 and 2, we can construct the ANOVA analysis as shown in Figure 3.

**Figure 3 – ANOVA for Example 1**

We conclude there are significant differences between the nationalities, but no significant differences between the genders or positions. There are also significant interactions between gender and position as well as between all the factors.

the programe shows the follwoing error: Alpha must be a number between 0 and .5

I selected 0.05 but even it shows the same error

Garapati,

Which data analysis tool were you using?

In the meantime, I suggest that you enter the value 0 for Alpha and then change its value on the output report.

Charles

Hi Sir Charles!

I have my thesis that works like this:

FACTORS

A = Concentration: 3 levels

B = Time: 6 levels (7days, 6days, 5days for 25 deg C) (5, 10 and 15 minutes for 100 degC)

C =Temperature: 25degC and 100 degC (2 levels)

The problem is when I manually calculate the three way anova of this, there will be no intersection of B and C at certain points; that is, when the temp. is 25 the intersection to time is only 7, 6 and 5 days and when the temp. is 100, the intersection to time is only 5, 10 and 15 minutes. It makes the value of B x C negative and ruins the whole ANOVA table (no values for p-value can be obtained)

I also tried using the Real Statistics Data Analysis Tool on my Excel and yielded the same results. My question is: is the three way anova applicable to my case? Is there any more statistical treatment that I can use to find out which of the factors influences the variable on my thesis. I badly need you help sir! Thank you very much

Ronald,

If you send me an Excel file with your data, I will try to provide a suggestion. You can find my email address at Contact Us.

Charles

Hello Dr. Charles!

Can you please give me a copy of the excel file =(

I really wanted to study it on excel, especially the last picture (construction of ANOVA table)

Thanks!

Ronald,

You can download any of the examples by going to Workbook Examples.

Charles

hello Dr. Charles i want to know about the 4 factor anova ? how to design this ? what is mean by 2^k factorial design ?

Mat,

The approach to 4 factor Anova is similar to 3 factor Anova, excepty that it is more complicated and difficult to interpret since now you have the factors A, B, C, D, AxB, AxC, AxD, BxC, BxD, CxD, AxBxC, AxBxD, AxCxD, BxCxD and AxBxCxD.

A 2^k factorial design has k factors, where each factor has two levels.

Charles

Hi Friends ,

Can you anyone tell me how we can observe the distribution by looking through existing data

Rasool,

Sorry, but what do you mean_

Charles

Hi Charles,

Thanks for the detailed explanations and for the extremely helpful website. I only took an introductory stats class quite some time ago and I was only taught the one-way ANOVA, so please excuse my ignorance on this matter. I’m wondering if we can eliminate some factors based on ANOVA analysis on limited preliminary data for a system involving many possible factors. As an example, I’ll use a case with 2 factors. Please bear with me and my lengthy questions.

Say we are investigating the effect of 2 factors, A and B, on a dependent variable. However, there is no control over the values of these 2 factors. For example, factors are the waiting time for a student before giving a presentation and the length of the presentation time and the dependent variable is the grade they received.

Two questions:

1/ We have too much variations for the values of these factors. Say, none of them are the same (10, 15, 17, 12, etc). Do we lump the values together and make them into categorical – e.g., low, mid, high (or even numerical based on their average)? Would this introduce large errors in the calculation?

2/ If the data that we obtained do not actually “fill” the table completely, could we still calculate the sum of squares between these factors as if they are 2-way ANOVA? For example, we only have data for:

a) Students who have waited for a “short” time and presented either a ” mid-length” or a “long” presentation

b) Students who have waiting for a “mid-length” time and presented either a “short” or a “mid-length” presentation

c) Students who have waiting for a “long” time and presented either a “short” or a “long” presentation

Or is there an alternative statistical test in order to investigate whether these factors affect the scores at all?

Would greatly appreciate your advice. Thank you!

Chelsea,

I only partially understand the situation you are describing, but, in any case, here are a couple of observations:

1. To use ANOVA, the independent variables need to be categorical. If they aren’t then by necessity you need to lump some of them together.

2. If the data doesn’t completely fill the table (or if you have an unequal number of sample elements in the cells of the table), then you have an unbalanced model. You can still analyze such models using ANOVA (although under the covers the analysis is regression). You can learn more about this at the webpage: Unbalanced ANOVA.

Charles

Thank you for your answer, Charles. Very much appreciated.

I am thinking along the line of how to identify which factors in experiments affect the result and which don’t (whether or not there is a correlation between each of these factors and the result). Problem is these factors aren’t controllable, thus the effect of each factor cannot be isolated from the others. Sorry for not expressing myself clearly. I really gotta do more reading on various stats method.

Thanks again! Your website has been a great help!

Dear Dr Charles

This is an excellent tutorial.

I want calculate ANOVA for the following data.

The data provided is the gray rational grade values for input parameters 4 for 3 levels

PARAMETER LEVEL-1 LEVEL-2 LEVEL-3

VOLTAGE V 0.450339471 0.634585192 0.71200671

PULSE OFF 0.617898144 0.557872095 0.621161134

PULSE ON 0.607522282 0.487965309 0.701443781

CURRENT 0.501263495 0.665427997 0.630239881

The total DF value is equal to 8.

Thank You

Dear Srikant,

The website explains how to address these sorts of problems. The Real Statistics software makes it easy for you to do so. I am afraid it is up to you to do the rest.

Charles

In figure 3, I understand everything except the computation necessary to find the P-Value. What did you do to find that number?

Samantha,

The p-value for any factor is given by the formula =FDIST(F,df,dfE,True).

Charles

Hi Charles,

thank you the calculations. they are really helpful.

I am unable to find how to calculate the F value and P value as the figure 3 does not show the formulas.

Can you help?

As always F for any factor is equal to MS for that factor divided by MS for the error term. E.g. for factor A, F is MSA/MSW. The p-value is FDIST(F,df1,df2), where df1 is the degrees of freedom for that factor and df2 is the degrees of freedom for the error term.

Charles

pls can you help out with this question

an experiment consists of two factors,A and B ,each at 3 levels

a.write the appropriate ststistical model

b.specify the appropriate hypothesis to be tested

c.give a skeletal Anova for presenting results of the analysis of data fro m the experiment

You need to provide additional information to get a definitive answer, but assuming that you are looking for a two fixed factor ANOVA model with replication, please see the following webpage>

Two Factor ANOVA with Replication

Charles

sir please help me out wt the above question i have battling to solve it preparing for en exam it might likely be an exam question thank you

As I wrote in my previous reply, please look at the webpage Two Factor ANOVA with Replication. It has the answers to your questions.

Charles

Hi!

I have a Three-Way Within-Subjects ANOVA. Independent Variable 1 has 2 levels, Independent Variable 2 has 2 levels, and Independent Variable 3 has 3 levels. I have 4 subjects, who went through all the 12 conditions.

My question is that I have drawn up the ANOVA table in excel as followed from your website, and I found that the 3 main effects, 3 two-way interaction effects, 1 three-way interaction effect all share the same error value, which is the Error(Within). But isn’t a Repeated Measures table supposed to have individual Errors for each effect, just like the Two-way Within Subjects ANOVA table drawn on your website?

Please enlighten me on this, thank you!

Rebecca,

The Three Factor ANOVA shown on the website does not deal with repeated measures. In order to handle a three factor repeated measures ANOVA you need a four factor model which extends the two factor repeated measures ANOVA described on the website. Since this model is complicated to calculate and even more complicated to interpret, I thought that at least for now it was better not to include on the website. As you said, this model would need a separate error term for each effect.

Charles

Respected sir,

Kindly advice me to solve three factor analysis arranged in split plot design i.e.,

Main plot : First factor- three levels of irrigation (3)

Subplot: Factorial combinations of fertilizer and time application (3×2)

Second factor – three levels of fertilizer (3)

Third factor – two levels of time of application (2)

WAITING FOR U RESPONSE

Kiran,

It is not so hard to modify the three factor ANOVA design described on the referenced webpage for split plot design, but I haven’t done this yet. I hope to get to this in one of the next few releases. Stay tuned.

Charles

hello,

i tried using 3 factor anova and because i have 12 conditions my ‘within’ & ‘df’ cell is zero.so all of my f crit, f and p values are not defined. how do i fix this?

also i am not sure how to interpret the results of 3 factor anova, for example which factor has the significant effect? and so on. is there a hand out i can read more on the interpretation of the 3 way anova results because i can;t seem to be able to find anything online.

thanks

icell

icell,

If all the values are zero, it probably means that your sample is too small.

The interpretation of the results of three factor anova is quite similar to that of two factor anova, except that it is more complicated since you not only have an extra factor, but you have three more interactions. For Example 1 on the referenced webpage, there is an explanation as to which factors have a significant effect (as always you just need to see if the p-value for that factor is less than alpha).

Charles

How AA9, AA11, AA13 is the value 3? Could you pls explain, Sir?

I’ll explain AA9, the others are similar. dfAB-Bet can be calculated as ab-1 2*2-1 or as dfA+dfB+dfAB = 1+1+1 = 3.

Charles

Thank you! Your tutorial is a great help to my work.

Dear charles,

I still confuse to get anova three way table by using excel. I have no idea to calculate p-value and Fcrit. Could you give some explanation Charles?

Thank you

The explanation can be found in Figure 3. Perhaps, you can tell me which formulas in that figure are not clear.

Charles

Why is the denominator when calculating F always between(within)? Would the calculations change at all if two of the factors were random? If so, how? Thanks for the explanations.

Steve,

See the following website http://www.plantsciences.ucdavis.edu/agr205/Lectures/2011_Lectures/L10_MixModels.pdf

Charles

please i need an explanation on finding the counts

Linda,

The count for A is calculated via the formula =S4*U4*V4

B via =S4*T4*V4

C via =S4*T4*U4

AB Bet via =S4*V4

AC Bet via =S4*U4

BC Bet via =S4*T4

ABC Bet via =S4

Charles

I have a question.

Why is the count in T in figure 3 is 1?

Stanly,

As you can see in the Formulas for SS column in Figure 3 each DEVSQ formula is multiplied by a count value (e.g. for SSA this is Y6 which is equal to 48). For SST you don’t need to multiple the DEVSQ formula by anything (or equivalently you multiply it by 1).

Charles

can you guide me on how to obtain the count data? especially count for AB bet, BC bet and so on.

I am trying to do a three way anova and my a, b, and c factors each has 2, 3 and 4 variables respectively and 300 sample size per row. I can’t seem to get through on how to get the count data. your help is very much appreciated. thanks alot.

Max,

If I understand your question correctly, a = 2, b = 3, c = 4 and m = 300. This would mean that the total sample has size n = abcm = 7200. If this number is too high, then probably the m value is lower.

Assuming the above is correct, dfBetAB = dfA + dfB + dfAB = (a-1) + (b-1) + (a-1)(b-1) = 1+2+2 = 5.

Charles

can we use the 3-ways anova for non replication treatment??

Andy,

Sorry, not at present.

Charles

This is an excellent tutorial.

However, I am confused about how the counts are derived and what they mean for. (Y4:Y17) and (S3:V4).

I would greatly appreciate an explanation.

Thanks

Sorry about the comment. It appears that the counts are derived from m, a, b, c, the number of levels, and the number of participants in each group. I’m rusty on anova calculations, but this tutorial helps a lot 🙂

No problem. You are correct that the counts are derived from m, a, b, etc. Feel free to comment, even about simple things. It is better to ask questions to clarify things if necessary.

Charles

please give advice how to form annova table

Please be more specific since the referenced webpage gives an example.

Charles