ANOVA Analysis Tool and Confidence Intervals

As described in One Sample Hypothesis Testing, the confidence interval is given by

estimated mean ± critical value ∙ std error

Confidence interval ANOVA

Alternatively by Property 1 of F Distribution, we can use the following as the critical value


We could have used s_j^2 as the estimate of the variance for the jth group in the calculation of the standard error, but since it is assumed that the variances of all the groups are equal, MSW is an estimate of group j based on a larger sample than s^2_j, and so by the Law of Large Numbers, MSW provides a better estimate of the jth group variance than s_j^2.

Example 1: Find the confidence intervals for each of the methods in Example 3 of Basic Concepts for ANOVA.

To do this we use the ANOVA data analysis tool found in the Real Statistics Resource Pack. Enter Ctrl-m and then double click on Analysis of Variance. Next select Anova: one factor from the dialog box that appears. as in Figure 0.

ANOVA dialog box

Figure 0 – Analysis of Variance dialog box

The dialog box shown in Figure 1 is now displayed.

Single Factor ANOVA dialog

Figure 1 – Dialog box for Single Factor Anova

Enter A3:D11 in the Input Range, select Excel format with column headings as the Input Format, select the ANOVA option and click on OK. The output appears as shown in Figure 2.

One-way ANOVA output

Figure 2 – Output of Real Statistics ANOVA data analysis tool

The confidence intervals are given in the range M7:N10. E.g. the confidence interval for Method 1 is calculated as follows:


tcrit = TINV(α, dfW) = TINV(.05, 25) = 2.06

Confidence interval = i ± tcrit s.e. = 61.29 ± 2.06∙ 5.03 = (50.92, 71.65)

Note that most of the rest of the output in Figure 2 is similar to that found in the standard Excel data analysis tool (see, for example, Figure 5 of Basic Concepts for ANOVA). The entry for RMSSE is explained in Effect Size for ANOVA, while the entry for Omega Sq is explained in Other Measures of Effect Size for ANOVA.

Example 2: Conduct ANOVA for the data in the range A3:B31 of Figure 3 (only the first 20 elements are shown). This time the data is stored in what we will call stacked or standard format. Here the first column contains group names and the second column contains the corresponding scores. The data can be listed in any order.

One factor ANOVA stacked

Figure 3 – ANOVA for input in standard format

To conduct the analysis, click on cell D3 (where the output will start), enter Ctrl-m and select Analysis of Variance and press the OK button. Next select Single Factor Anova from the dialog box that appears. A dialog box will then appear similar to that shown in Figure 1. This time enter A3:B31 in the Input Range, select Standard format as the Input Format, deselect Columns/row headings included with data, select the ANOVA option and click on OK. The output appears in the rest of Figure 3.

Note that the data analysis tool first converts the input data from standard format to the usual Excel Anova format with column headings (range D5:G13). Since this data is the same as that used in Example 1, the ANOVA results are the same as shown in Figure 3.

Real Statistics Functions: The Real Statistics Resource Pack contains the following two array functions for converting between Single Factor Anova format and standard format.

StdAnova1(R1) = takes the data in R1 which is in standard format and outputs an array with the same data in Single Factor Anova format.

Anova1Std(R1): takes the data in R1 which is in Single Factor Anova format and outputs an array with the same data in standard format.

Observation: Referring to Figure 3, StdAnova1(A3:B31) yields the results shown in D5:G13. If you highlight the range S5:T35 and enter the array formula Anova1Std(D5:G3), the result is shown in Figure 4.

Data standard format Excel

Figure 4 – Data in standard format

Note that the result is the same as range A3:B31, but in sorted order. Also note that any extra entries are filled in with #N/A.

Real Statistics Functions: In One-way ANOVA Basic Concepts we described the functions SSW, SSBet and SSTot, which can be used for data in Excel format. The following functions provide the same functionality for data in standard format.

SSWStd(R1, col) = SSW

SSBetStd(R1, col) = SSBet 

SSTotStd(R1, col) = SSTot

Here the first column of range R1 contains the names of the factor levels and the other columns contain data values. The column number  col contains the data for the one-way ANOVA that we want to perform. If col is omitted it defaults to 2 (the second column).

For the data in Figure 4, the formula =SSWStd(S5:T33) yields the value 4429.137, as seen in cell J17 of Figure 3.

13 Responses to ANOVA Analysis Tool and Confidence Intervals

  1. horoscope says:

    Oh my goodness! Impressive article dude! Many thanks, However I am having issues with your RSS.
    I don’t understand why I am unable to subscribe
    to it. Is there anybody else having the same RSS issues? Anybody who knows the solution can you kindly respond?

  2. I’m not sure why but this web site is loading very slow for me.
    Is anyone else having this problem or is it a problem on my end?

    I’ll check back later and see if the problem still exists.

  3. Yohay says:

    Hi Charles,

    Could you please state the exact form of confidence interval for a ~difference of means~ between two groups? For instance, in your e.g. between method1 and method2; what would be the t statistic, the df, the relevant n value for the s.e statistic? Is it like in an independent t test? When df=n1+n2-2 etc.? I’m trying to figure it out with the numbers and seem to get lost.. SPSS claims the mean difference between those groups is -16.589, which I obviously understand, but the 0.05 CI is [-35.537,2.3592] (one side equals to 18.948).
    Would highly appreciate your explanation.
    Best regards and many thanks in advance,

    • Charles says:

      Hi Yohay,

      The referenced webpage explains how to calculate the confidence interval for the mean of each single method. This is like a one sample t test.

      You are asking about the confidence interval for a difference between group means. Since the groups are independent, this is like a two independent samples t test. This is considered a follow up to a ANOVA, and is discussed on a different webpage, namely the webpage (You need to consider the comparison where one method gets comparison coefficient +1 and the other gets comparison coefficient -1). Please read that webpage and let me know whether it answers your question.


  4. ndm says:

    i really like your array formulas–great adds! i might be misunderstanding something about them, however: For, say, n rows where there are, say, 3 missing datapoints, i think StdAnova1(R1) gives n datapoints with 3 zeroes instead of (n-3) datapoints with 3 nulls. Is that correct? If so, how do we get around this?

    • Charles says:

      Before using the StdAnova1 function or Anova data analysis tool, you could eliminate missing data by using the DELROWBLANK function (assuming the missing data are blanks). Alternatively you can use the Reformatting a Data Range by Rows data analysis tool (accessible by pressing Ctrl-m).

  5. kadirdb says:

    Suppose we take the data in the standard format and compute the squared deviations from the overall mean divided by the overall sample variance to obtain an “expected” series. Then do the same using observed sample means and variance for each group as appropriate to obtain an observed series. If we now apply the Ch-sq. goodness of fit test to the two series, would that be equivalent to ANOVA? Would it make sense, anyway?

  6. Heinrich says:

    Dear Charles, many thanks for this awesome software.
    I have a question related to the calculation of the confidence intervals.
    Performing the ANOVA in Minitab or JMP it can be seen, that the intervals are slightly different (narrower) to the one calculated with your add-on. Can you (or someone other) give me an explanation of this behavior.
    Thanks Heinrich

    • Charles says:

      Since I am not familiar with Minitab or JMP, can you send me an Excel file with your data and the results you have received using Excel? Also please send me the results you got from Mintab and JMP. I will then try to figure what is happening.
      You can get my email address from the Contact Us webpage.

  7. Gumel says:

    Dear Charles,

    I have data from fetilizer trail on vegetable, five levels of fertilizer are my treatments, replicated three times. The data was taken on three growth parameters (number of leaves, stem girth and plant height) and two yield parameters (leaf weight and stem weight). My question is:
    1. Do I use one-way or two way ANOBA?
    2. Should I analyse the whole data once or one by one (i.e to analyse data on number of leaves alone, then stem girth, and so on or analyse them together?

    Thank you

    • Charles says:

      1. It really depends on what hypotheses you want to test. Based on your description, my may also consider Manova
      2. Again it depends on what hypotheses you want to test.

Leave a Reply

Your email address will not be published. Required fields are marked *