Basic Concepts
You can use Property 1 of F Distribution to test whether the variances of two populations are equal. We show how to do this by using Excel worksheet functions and the data analysis tool described below. In order to deal exclusively with the right tail of the distribution, when taking ratios of sample variances from the property you should put the larger variance in the numerator of
In order to use this test, the following assumptions must hold:
- Both populations are normally distributed
- Both samples are drawn independently from each other.
- Within each sample, the observations are sampled randomly and independently of each other.
Worksheet Functions
Excel Functions: Excel provides the following function to carry out this test:
F.TEST(R1, R2) = two-tailed F-test comparing the variances of the samples in ranges R1 and R2 = the two-tailed probability that the variance of the data in ranges R1 and R2 are not significantly different.
Thus F.TEST(R1, R2) = 2 ∙ F.DIST(x, df1, df2) where df1 = the number of elements in R1 – 1, df2 = the number of elements in R2 – 1 and x = var1 / var2 where var1 is the variance of the data in range R1 and var2 = the variance of the data in range R2. F.TEST is a two-tail test, while F.DIST, F.DIST.RT, F.INV, and F.INV.RT are one-tailed.
Also, F.TEST(R1, R2) = F.DIST(x, df1, df2, TRUE) + F.DIST(1/x, df2, df1, TRUE), i.e. the sum of the right tail starting from x plus the left tail ending at 1/x. This is true since F.DIST(1/x, df2, df1, TRUE) = 1 − F.DIST(x, df1, df2, TRUE).
Versions of Excel prior to Excel 2010 don’t include the F.TEST function. These versions of Excel use the equivalent FTEST function (see Built-in Statistical Functions).
F.TEST and FTEST ignore all empty and non-numeric cells.
In addition, Excel provides an F-Test Two-Sample for Variances data analysis tool which automates the process of comparing two variances.
Example
Example 1: A company is trying to decide between two methods for manufacturing pipes based on the method with the least variability in the width of the pipes. To test this, it creates a random sample for each method as shown on the left side of Figure 1. Based on these samples, determine which manufacturing method it should employ.
Figure 1 – Hypothesis testing for comparing two variances
We test the following null hypothesis:
H0: σ1 – σ2 = 0 (equivalently: σ1 = σ2; i.e. both methods have the same variability)
and use the statistic 
with 11, 14 degrees of freedom, as described on the right side of Figure 1. Since this is a two-tail test, we note that
p-value = 2 * F.DIST.RT(F, df1, df2) = 2 * F.DIST.RT(1.85, 11, 14) = 0.279 > 0.05 = α
F-crit = F.INV.RT(α/2, df1, df2) = F.INV.RT(.025, 11, 14) = 3.09 > 1.85 = F
Either of the above tests shows there is no significant difference in the variance between the two methods with 95% confidence. Note that we needed to double the value for F.DIST.RT or halve α since this is a two-tail test.
Alternatively, we can use F.TEST which is a two-tail test:
F.TEST(A4:A18, B4:B18) = .279 > 0.05 = α
Data Analysis Tool
Excel Data Analysis Tool: Excel provides an F-Test Two-Sample for Variances data analysis tool that automates the process of comparing two variances.
Using this data analysis tool for Example 1 results in the output shown in Figure 2.
Figure 2 – Comparing variances using Excel’s data analysis tool
This tool only performs a one-tail test, and so the p-value (0.1393) needs to be doubled to get 0.279, which is the same value we calculated in Figure 1. The critical value for F is calculated based on α = .05.
Examples Workbook
Click here to download the Excel workbook with the examples described on this webpage.
References
Excel Tip (2023) How to use the F-Test in Microsoft Excel
https://www.exceltip.com/tips/f-test-in-microsoft-excel-2010-and-2013.html
Causal (2023) F.TEST Excel
https://www.causal.app/formulae/f-test-excel
Hello, I want to test, two companies employees work satisfaction. I have given one group data as % and other group is distribution of 500 samples. Question is ” Are the satisfaction level same or differ? Should I use F test or Chi-square test to do this ? We should compare Mean of two or Variance of group and compare it ? Satisfaction are categories as Fully , somewhat good , neither , somewhat bad , and bad ( provided as column ).
Please suggest.
Hello JD,
1. You need to use the same metric for both groups. If you use % for one group, you need to use % for the other. If not you need to find some way to map the metric from one group to the metric used by the other group.
2. To compare the two groups you would usually use a two-independent sample t-test. Since you are using a 5-point Likert scale, you might be better off using a non-parametric test instead such as Mann-Whitney or Brunner-Munzel. All these tests are described on the Real Statistics website.
Charles
Hello!
I am asked to use a statistical test to find the difference of variability of the time between two independent normally distributed samples. I conducted the f-test in excel and the p-value is
P(F<=f) one-tail 2,2178E-06. Can you interpret it? I am confused. Thank you in advance.
Hello Lina,
2,2178E-06 is a way of representing the value .0000022178, which is a very small number. This means that you have a significant result, which undermines the null hypothesis. This means that is quite likely that the population variances are not equal.
Charles
Hi sir Charles, where can I email you so that I can show you the result or output of the F test? thank you
See Contact Us
Charles
Hi Charles,
So I have a “batch” problem. At the end of every month, I receive data as 30 pairs of (X, Y).
I want to find if I can do regression on them. So, at the end of every month I do an f-test on the data for that month. In an year, I have 12 different f-test values, some below the critical level, some above.
My question is, is this a valid approach? If it is, how should I decide if the regression is meaningful (since some f-test values are below the critical level).
Thanks,
Fred
Fred,
Your question is not clear to me.
What are you testing? Are you testing whether the variances for the x and y values are equal? This is not necessary for regression.
If you are performing regressions, then use the tests for regression.
Charles
Hello Charles,
Can you please explain the difference in a significant change in variance at the .10 level as opposed to the .05 level?
Thanks!
Julie,
See the following webpage:
Null Hypothesis
Charles
If varience is equal than which test should apply or if unknown then which test?
Fatima,
What hypothesis are you trying to test?
Charles
i have age and motivational need as variables, i wanted to know if age affects the motivational need of the respondents, should i use F test?
Ever,
The referenced webpage uses an F test to compare variances. Whether “age affects the motivational need of the respondents” is a different issue. You can use correlation or regression to address this issue.
Charles
WHY IS THE LARGER VARIANCE IN NUMERATOR
It is really just convention. You could adjust all the tests to put the larger variance in the denominator.
Charles
You wish to see if the larger variance is SIGNIFICANTLY larger than the small variance. If this is the case, then the F-test is significantly above the standard F-distribution for those parameters.
So it makes sense to put the larger variance there.
Oh, look there, 3.09>1.85. Amazing! I’ll just reject my null hypothesis then. I have no idea what those numbers mean. Thanks for explaining.
Easy, 1.85 is a small number compared to the F criterion, which means that the ratio between the variances is not that large. So the two variances are close enough to each other, i.e. the difference between the variances it not significantly large.
Actually you do not reject the null hypothesis.
I just want to now why the large value must be put on the Numerator while computing the F test statistic.
I do understand F>0 since S^2 is always positive, however the above is unclear. Kindly clarify
You don’t have to do this. It just makes the analysis more consistent.
Charles
I am trying to compare 2 variables to see if they are equal. I am using the F-test in the Data Analysis Toolpack to determine the correct T-test to use (equal or unequal variances). However, when I enter the range 1 (Method 1), and range 2 (Method 2), I get one F and F Critical ouput FF Crit (dont’ reject Ho). How should the data be entered into the ranges, and does it matter? This giving me 2 decisions, to reject and the other to not reject Ho. Could you explain why this would happen and which output I should go with? Am I missing something?
Brad,
Are you saying that based on comparing F with alpha you get a different result from that of comparing F with F crit?
In any case, when in doubt use the t test with unequal variances. If the variances are equal, the result of this test will be very similar to the t test with equal variances.
Charles
Sir I want to know that I am calculating F.inv.Rt for right tail for alpha 0.02 and it is coming exact equal to f.inv(which calculates left tail ) for 0.98 why ?
Perhaps I don’t understand your question, but in general =F.INV.RT(alpha,df1,df2) yields the same value as =F.INV(1-alpha,df1,df2).
Charles
Two tailed F-testing for differences in population variances. Why not put the ratio of the highest variance in the nominator and do a right one tailed testing?
Actually, the same question can be linked to differences in the population means: why not take the subtraction of the smallest from the highest sample mean and further with a one tailed t-testing?
You can do these things. Typically, the left tail is tested.
Charles
Hello Charles,
Do you know how I should report this F value in APA style?
I’m currently using
F(n1-1,n2-1) = ___, p = ___
but I’m not sure if this is correct.
Maybe I should use the ANOVA way of reporting (i.e. F(k-1,N-k) = ___, p = ___; with k being the number of groups)? Or use something entirely different (like F = x, p(f>x) = __)?
The APA manual and Google aren’t very helpful thus far, so I hope you know what to do.
Lotte
Lotte,
Sorry, but I am not sure of the response, but I would use the ANOVA way of reporting, but with the correct values for the comparison of variances (as described on the referenced webpage).
Charles
Hi Charles, I think you meant FDIST(1/x, df2, df1) + FDIST(x, df1, df2) = 1 and not FDIST(1/x, df2, df1) = FDIST(x, df1, df2).
Kristian,
Yes. Thanks for catching this error. I have now revised the referenced webpage.
Charles
Will you please solve this problem??
“For a random sample of 10 pigs fade on diet A the increase in weights in a certain periods was: 10, 6, 16, 17, 13, 12, 8, 14, 15 and 9. For another random sample of 12 pigs fade on diet B, the increase in the same period were 7, 13, 22, 15, 12, 14, 18, 8, 21, 23, 10, 17. Show that the estimate of the population variance from sample does not differ significantly.”
The procedure to use is the one explained on the referenced webpage.
Charles
Charles,
Can you explain the notation P(F<=f) one-tail? I understand that this is the area under curve in the upper tail, but the value doesn't change when I re-run the test with different alphas for the same two populations (F-crit, however, does change).
Norm
Norm,
If you are referring to Figure 2 then it is important to note that Excel’s data analysis tool only uses an alpha value of .05.
If you are referring to Figure 1, then you are correct that the p-value does not depend on alpha, whereas F-crit does depend on alpha. In any case alpha enters into the picture since you are typically testing whether p-value < alpha (or equivalently whether F > F-crit).
See Hypothesis Testing for more details about this.
Charles
Charles,
Thank you. It’s curious that the F-test for two population variances in the Analysis Pak allows you to enter other alpha values in the dialogue box when it only calculates at .05. Thank you for that tip. I’m still confused about the notation for the p-value – “P(F<=f) one-tail."
Is "f" the value that you calculate based on the d.f., sample variance, and hypothesized variance? If that's true, does it mean the p-value for when "f" is less than or equal to Fcrit? It's not explained in any literature on Excel's F-test.
Thanks again,
Norm
Norm,
Sorry, but I gave you the wrong information. Excel’s data analysis tool does take the value you enter for alpha into account. In the term P(F < f), you need to interpret F as the random variable and f as the value of that random variable. Essentially, P(F < f) is the one-tailed p-value. To get the two-tailed p-value you need to double the value presented. This is not the p-value for when "f" is less than or equal to Fcrit. That value is alpha (or alpha/2 for the two-tailed test). For more information as to the meaning of the p-value, see Hypothesis Testing.
Charles
Dear Dr. Zaiontz,
thanks for your valuable contributions to understand different statistical methods and your information on how to use them in Excel.
I’m not quite sure about the interpretation of the results of the F test. As you state, Excel functions FTest or F.Test give “the two-tailed probability that the variance of the data in ranges R1 and R2 are not significantly different”. On the other hand, in example 1, it is said that a p value = 0.279 > alpha, among others, “shows there is no significant difference in the variance between the two methods with 95% confidence”. To my understanding, according to the first statement, there is only a 27.9% probability that the variances are not significantly different.
I would like to confirm equality of variances as a precondition to do a two sample t test. Now, is it sufficient to check whether p value (of F test) > alpha?
Dear Felix,
That p-value = .279 > alpha does not mean that there is only a 27.9% probability that the variances are not significantly different. See https://real-statistics.com/hypothesis-testing/null-hypothesis/, especially the last observation.
You can use the F test to check whether the two variances are equal as a precondition to using the two sample t test, but you should note that there is a version of the two sample t test which you cab use even when the variances are unequal. See https://real-statistics.com/students-t-distribution/two-sample-t-test-uequal-variances/
Charles
dose % structural aberrations
negative control 2
negative control 2
solvent control-1 5
solvent control-1 6
what test can be applied to this data
I’m afraid that you need to provide additional information before I can provide an answer.
Charles