Subscribe for More!

Tutorials and Guides

Pharmaceutical Calculations: One-Way ANOVA with Microsoft Excel

What is One-Way ANOVA? A Short Introduction

Analysis of Variance (ANOVA) is a widely used statistical technique in clinical research, pharmacology, psychology, molecular medicine, and other fields of experimental science for analysing data. ANOVA can be used to determine if there is a statistically significant difference between the means of groups, due to some influence factor. In graphs and in figures, statistical significance is typically expressed using asterisks (*) and sometimes, authors also state the significance level (α).

In the context of ANOVA, an influence factor is an independent treatment variable that an experimenter can control and/or vary, which may or may not result in significant differences in means. Examples of possible factors include temperature, solvent polarity, catalyst, location, method of analysis, diet or drug treatments.

One-way ANOVA looks at the effects of one factor. The null and the alternative hypotheses for one-way ANOVA can be stated as follows:

  • Null Hypothesis (H0): There is no difference in the means due to a factor. Using mathematical expressions, we can express H0 as: µ1 = µ2 = µ3 … = µn
  • Alternative Hypothesis (H1): There is a significance difference in the means due to a factor.

One-Way ANOVA is also referred to as unifactor ANOVA or single factor ANOVA.

The calculation of the F-statistic, which is simply the ratio of the variation between sample means to the variation within the samples, is the basis of ANOVA calculations. The higher this ratio, the weaker the evidence supporting H0.

In this article, we will show you how to do one-way ANOVA calculations on Microsoft Excel.

Why are multiple t-tests not recommended?

The t-test is a robust test for detecting a statistical difference in means between two populations that are normally distributed. When you are analysing more than two groups, it is strongly recommended to analyse your data using ANOVA instead. Using multiple t-tests increases the chances of making a Type I error (incorrect rejection of a true H0!).

What if there is more than one factor?

Research questions are often complex and typically involve more than one factor. Suppose a biochemist wants to look at the influence of stirring speed and temperature on the outcome of an assay or if a pharmacology researcher wants to determine if there is an interaction between an experimental drug treatment and gender on cytochrome P450 activity. In both scenarios, you have two factors (example 1: stirring speed and temperature & example 2: drug treatment and gender). In such scenarios, you would need to perform multifactor ANOVA (AKA multiway ANOVA) to analyse your data. You can also do Two-Way ANOVA calculations using Microsoft Excel’s “Data Analysis” but not Three-Way or greater.

How do I do One-Way ANOVA calculations with Microsoft Excel?

In practice, doing one-way ANOVA by hand can be laborious and time-consuming compared to doing the calculations with software but yes, ANOVA by hand is possible. Aside from Microsoft Excel, Minitab, GraphPad PRISM and SPSS. Of course, you can do ANOVA calculations with R. In this tutorial, we will show you how to do ANOVA calculations with Microsoft Excel and how to interpret the results by showing you solutions to sample problems below:

You will need to enable the “Data Analysis” feature on Excel for this part. (See our tutorial here)

Problem 1:

A pharmacology research laboratory is testing the effect of four drug candidates on the concentration of nitric oxide (NO) in rat plasma (n = 12). The data for the quantification of NO, in µmol/L, is displayed below. Determine if the treatments result in a significant change to the concentration of NO in rat plasma. (α = 0.05)

Solution: One-Way ANOVA on Excel:

Although the example below uses Excel 2013, other versions of Excel work in a similar fashion.

Step 1: Enable “Data Analysis” on Excel. If it’s already enabled, you can skip Step 1.

Step 2: Tabulate your data on a completely empty spreadsheet as shown below

Step 3: Select the “Data” tab > Click “Data Analysis” > Click “Anova: Single Factor” > Press OK

Step 4: Click the highlighted box

Step 5: Highlight your data, starting at the top left corner and ending at the bottom right corner of your data as shown below. Exclude the labels.

Step 6: Set your significance level (α) under “Alpha:”

The default setting is “Alpha: 0.05”. Since α = 0.05, we will leave the “Alpha:” box as it is.

Step 7: Select the circle to the left of “Output Range:”

Step 8: Click the highlighted box to the right of “Output Range:”

Step 9: Choose an empty cell on your spreadsheet. Bear in mind that Excel will generate the results on a 7×17 area so an area of that size to the bottom right of your chosen cell must also be empty.

Step 10: Click the highlighted box

Step 11: Press “OK”

Result: Excel should generate the table shown below

Our p-value is 0.00281. Excel also shows you the F-statistic.

Interpretation of the result (Problem 1):

You performed the test at a significance level (α) of 0.05. If you obtain a p-value greater than 0.05, that means there is no statistically significant difference between the means due to a factor. However, in the example shown above, we obtained a p-value of 0.00281, which is lower than 0.05, meaning there is a statistically significant difference (we reject H0!).

Since p ≤ 0.05, we have strong statistical evidence that the factor (treatment) has an effect (concentration of NO in rat plasma) that is likely not due to chance and we may reject H0. We may also state that since p ≤ 0.05, there is a statistically significant difference in the mean concentrations of NO in rat plasma due to the drug treatments (we accept H1!).

Problem 2:

Several groups of healthy male Struthio camelus (n = 15 per group, age = 25 years old) were subjected to special diets for a month to investigate the effects on body mass (kg). Determine, at a significance level of 0.05, if the special diets resulted in a significant change in body mass.

Solution: One-Way ANOVA on Excel:

Doing Steps 1 to 11 as shown in Problem 1 should give you the following results:


Our p-value is 0.0914

Interpretation of the result (Problem 2):

You performed the test at a significance level (α) of 0.05 and we obtained p = 0.0914. Since p is greater than 0.05, our evidence for rejecting H0 is weak. You can conclude that there is no statistically significant difference in mean body mass of the groups of Struthio camelus due to their diet (we accept H0!).

When doing ANOVA, how do I find out where the differences in means lie?

ANOVA is an omnibus test, meaning ANOVA tests for an overall experimental effect. ANOVA can tell you that there is a difference between the groups due to a factor but it won’t tell you where the differences are. Tests referred to as post hoc tests such as Dunnett’s test can be used to tell you where the differences lie. Other software packages such as SPSS and GraphPad PRISM can perform post hoc tests.

References and Further Reading:

(1) Kim, H.-Y. Analysis of Variance (ANOVA) Comparing Means of More than Two Groups. Restor. Dent. Endod. 2014, 39 (1), 74. DOI: 10.5395/rde.2014.39.1.74

Nayak, B.; Hazra, A. How to Choose the Right Statistical Test? Indian J. Ophthalmol. 2011, 59 (2), 85. DOI: 10.4103/0301-4738.77005

Kao, L. S.; Green, C. E. Analysis of Variance: Is There a Difference in Means and What Does It Mean? J. Surg. Res. 2008, 144 (1), 158–170 DOI: 10.1016/j.jss.2007.02.053

error: Content is protected !!