StatPakF-test for two variances in Google Sheets
The F-test checks whether two groups have the same spread. It is often run before a two-sample t-test to choose between the equal and unequal variance versions. Example: fill volumes (ml) of 500 ml bottles from two machines.
| A | B | |
|---|---|---|
| 1 | Machine 1 | Machine 2 |
| 2 | 502.1 | 500.4 |
| 3 | 498.7 | 499.6 |
| 4 | 503.4 | 501.1 |
| 5 | 497.2 | 500.2 |
| 6 | 500.9 | 499.1 |
| 7 | 504 | 500.8 |
| 8 | 496.5 | 499.9 |
| 9 | 501.8 | 500.6 |
| 10 | 499.3 | 501.3 |
| 11 | 503.1 |
Run the F-test
- Open Extensions, StatPak, Data Analysis and choose F-Test Two-Sample for Variances.
- Variable 1 range:
A1:A11. Variable 2 range:B1:B10. The groups can have different sizes. - Tick Labels in first row, leave Alpha at 0.05 and click Run.
| F-Test Two-Sample for Variances | ||
| Machine 1 | Machine 2 | |
| Mean | 500.7 | 500.3333 |
| Variance | 7 | 0.51 |
| Observations | 10 | 9 |
| df | 9 | 8 |
| F | 13.72549 | |
| P(F<=f) one-tail | 0.0005780868 | |
| F Critical one-tail | 3.38813 |
How to read the output
- F is the ratio of the two sample variances, Variable 1 over Variable 2: 7 / 0.51 = 13.72549. A value near 1 suggests equal variances.
- df is n - 1 for each group: 9 and 8.
- P(F<=f) one-tail is 0.0005780868. Because it is below 0.05 (and F is above F Critical one-tail, 3.38813), Machine 1's fill volumes vary more than Machine 2's.
Put the group with the larger variance in Variable 1 so that F is above 1. If F is below 1, the tool (like Excel) reports the left tail and a critical value below 1, which is easy to misread.
F.TEST gives a two-tailed p-value
=F.TEST(A2:A11, B2:B10) returns the two-tailed p-value, which here is twice the tool's one-tail value: 0.001156174. Use the two-tailed value when you only ask "are the variances different?" and the one-tail value when you predicted which group varies more. You can rebuild the tool's numbers with =VAR.S(A2:A11)/VAR.S(B2:B10) for F, =F.DIST.RT(F, 9, 8) for the one-tail p-value and =F.INV.RT(0.05, 9, 8) for the critical value.
What to do next
With variances this different, compare the mean fill volumes with t-Test: Two-Sample Assuming Unequal Variances; see the t-test guide. The F-test is sensitive to non-normal data, so treat a borderline result with care.