How to Perform a Variance Ratio Test in Excel

This guide will explain how to perform a variance ratio test in Excel.

We can compute the F-statistic to determine whether two population variances are equal.

Users can conduct a variance ratio test when they want to compare the population variances of two groups.

We can perform this test by finding the F-statistic. This metric is the ratio of variances of the two groups being compared. The F in F-statistic is named in honor of Sir Ronald Fisher, who developed the metric in the 1920s.

Let’s look at an example where we can perform our variance ratio test.

Suppose you have conducted a market research survey on your brand. You want to determine whether the familiarity of your brand differs across age groups. From the results, you have produced two randomly selected groups. 

The first group is composed of respondents aged 18-25, and another group is aged 26-40. How can we determine whether there is a difference in the variance among the answers of both of these groups?

We can use Excel’s VAR.S formula to find the variance of each of these groups. We can then use the F.DIST function to determine the p-value of our findings. This p-value is crucial in understanding whether we have enough data to conclude that there is a difference between both groups.

Now that we know when to perform a variance ratio test in Excel, let’s learn how to do it on an actual sample spreadsheet.

 

 

A Real Example of a Variance Ratio Test in Excel

The following section provides a brief example of how to conduct a variance ratio test. We will also explain the Excel formulas used in every step.

First, let’s take a look at our given dataset.

We have conducted a test to measure a student’s performance in mathematics. The students in the first group were taught in classin-class while the second group took the class remotely. Each data point represents a particular student’s score in the final exam.

sample data of two groups

 

We want to know whether there is a significant difference in variance between both groups. 

use f-statistic to determine variance between both groups

 

To get the F-statistic, we just need to use the following formula:

=VAR.S(A2:A15)/VAR.S(B2:B15)

We’ll need to determine the p-value of our F-statistic to see if our result is statistically significant.

calculate p-valur of f-statistic

 

To find our p-value, we can use the following formula:

=F.DIST(E2, COUNT(A2:A15)-1, COUNT(B2:B15)-1, TRUE) * 2

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

You can use our sample spreadsheet as a template to perform your own variance ratio test.

If you want to follow a step-by-step breakdown of how to perform the variance ratio test yourself, head over to the next section!

 

 

How to Perform a Variance Ratio Test in Excel

This section will guide you through the steps you need to perform a variance ratio test in Excel. We will explain how to find the F-statistic of two groups using the VAR.S function. Afterwards, we will show how to compute the p-value of that metric.

Follow these steps to perform a variance ratio test in Excel:

  1. First, we will compute the F-statistic of both groups. We can find this metric by using the VAR.S function on both groups. We will divide the first group’s result by the second group’s to return the F-statistic.
    divide variance of both groups
  2. Hit the Enter key to return the F-statistic.
    compute f-statistic
  3. Now that we have our F-statistic, we must solve for the metric’s p-value. This metric will determine if we can conclude that the population variance of both groups is equal or not. Type “=F.DIST(“ on another cell to begin the p-value formula.
    add F.DIST in excel formula bar
  4. Add a cell reference to the F-statistic as the first argument of F.DIST.
    Use variance ratio test in Excel
  5. Next, we’ll use the COUNT function to determine the size of our first group. The second argument of F.DIST will be the sample size of the first group minus one.
    add second argument to F.DIST
  6. The third argument of F.DIST will be the sample size of the second group minus one.
    add third argument for F.DIST
  7. Set the fourth argument of F.DIST to TRUE to indicate that we want to return a cumulative distribution. We will then multiply the result of F.DIST by 2 to indicate a two-tailed p-value.
    set fourth argument and multiply F.DIST by 2
  8. Hit the Enter key to return the p-value result. Since the p-value in our example is not lower than 0.05, we cannot reject the null hypothesis.
    variance ratio test in Excel

These are all the steps needed to perform a variance ratio test in Excel. 

 

 

This step-by-step guide should provide you with all the information you need to perform a variance ratio test in Excel.

You should now understand what the variance ratio test is for and how to determine whether your results are conclusive.

The variance ratio test is just one example of the many statistical metrics you can use in an Excel spreadsheet. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.

With so many other Excel functions available, you can find one appropriate for your use case.

Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

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

You May Also Like