How To Calculate Variation Ratio Test (aka. F-Ratio test)?

If we take samples from two populations and wish to ascertain whether the variations of both samples are equal, we can use the Variation Ratio test also known as the F-Ratio test to determine this.

We will need to activate the Analysis ToolPak in Excel to access the F-test function.

To run the F-test we will use sample data file Variation Ratio Test sample data.

Adding the Analysis ToolPak to the Data tab

If the Analysis section on the Data tab is visible, you can skip theses steps (see screenshot).

If the Analysis section is not activated, follow these steps to activate the Analysis ToolPak.

1. In Excel, click File.

2. Click Options.

3. In the Excel Options window click Add-ins, in Manage select Excel Add-ins, click Go.

select excel addins go

4. The Add-ins window opens, select Analysis ToolPak and click OK.

check analysis toolpak

5. The Analysis section is now active on the Data tab.

ribbon analysis tab

Running the Variation Ratio / F-Test

1. Open the Variation Ratio Test sample data spreadsheet.

2. On the Data tab, click Data Analysis, then select F-test Two-Sample for Variances, click OK.

F-test Two-Sample for Variances

3. The F-test Two-Sample for Variances, window opens, click the Variable 1 Range arrow.

Variable 1 Range

4. The window contracts to the Variable 1 Range field, select C4-C9, click the arrow again.

Variable 1 Range field

5. Click the Variable 2 Range arrow, select D4-D9, click the arrow again.

Variable 2 Range

6. Click Labels. In Output options click the Output Range arrow.

7. Select Cell F4, click the arrow again, click OK.

Output Range arrow

8. Excel displays the F-test results.

Excel displays the F-test results

Quick reference guide

Steps to add the Analysis ToolPak

  1. In Excel, click File.
  2. Click Options.
  3. In the Excel Options window click Add-ins, in Manage select Excel Add-ins, click Go.
  4. The Add-ins window opens, select Analysis ToolPak and click OK.
  5. The Analysis section is now active on the Data tab.

Running the Variation Ratio / F-Test

  1. Open the spreadsheet with the data you wish to analyse.
  2. On the Data tab, click Data Analysis, then select F-test Two-Sample for Variances, click OK.
  3. The F-test Two-Sample for Variances, window opens, click the Variable 1 Range arrow.
  4. The window contracts to the Variable 1 Range field, select the first variable range, click the arrow again.
  5. Click the Variable 2 Range arrow, select the first variable range, click the arrow again.
  6. Click Labels. In Output options click the Output Range arrow.
  7. Select the cell where you want the results to appear, click the arrow again, click OK.8. Excel displays the F-test results.
Further reading: 
How to run statistical tests?
Hypothesis testing
Chi square test
How to test statistical significance by t test?
Anova test