How to Perform a Variance Ratio Test in Excel

A variance ratio test is used to test whether or not two population variances are equal.

This test uses the following null and alternative hypotheses:

• H₀: The population variances are equal
• H_A: The population variances are not equal

To perform this test, we calculate the following test statistic:

F = s₁² / s₂²

where:

• s₁²: The sample variance of the first group
• s₂²: The sample variance of the second group

If the p-value that corresponds to this F test-statistic is less than a certain threshold (e.g. 0.05) then we reject the null hypothesis and conclude that the population variance are not equal.

The following step-by-step example shows how to perform a variance ratio test in Excel.

Step 1: Enter the Data

Suppose we want to know if two different species of plants have the same variance in height.

To test this, we collect a simple random sample of 15 plants from each species.

First, we’ll enter the heights for each species:

Step 2: Calculate the F Test Statistic

Next, we’ll type the following formula into cell E1 to calculate the F test-statistic:

=VAR.S(A2:A16)/VAR.S(B2:B16)

The F test-statistic turns out to be 0.437178.

Step 3: Calculate the P-Value

Next, we’ll type the following formula into cell E2 to calculate the p-value that corresponds to the F test-statistic:

=F.DIST(E1, COUNT(A2:A16)-1, COUNT(B2:B16)-1, TRUE)*2

Note: In the formula, we multiplied by 2 at the end to produce a two-tailed p-value.

The p-value turns out to be 0.133596.

Recall the null and alternative hypotheses for this test:

• H₀: The population variances are equal
• H_A: The population variances are not equal

Because the p-value of our test (.133596) is not less than 0.05, we fail to reject the null hypothesis.

This means we do not have sufficient evidence to conclude that the variance in plant height between the two species is unequal.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Perform a Correlation Test in Excel
How to Perform Welch’s t-test in Excel
How to Perform a Kolmogorov-Smirnov Test in Excel