Excel F-Test (Table of Contents)
F-Test in Excel
F-Test is a statistical tool in Excel which is used to Hypothesis Test with the help of variance of 2 datasets or population. We calculate whether the Null Hypothesis (H0) for the given set of data is TRUE or not. This can be sure when the variance of both the data sets are equal. To perform F-Test, go to the Data menu tab, and from the Data Analysis option, select F-Test Two-Sample Of Variances. Select both the data population in the variable 1 and 2 range, keeping alpha as 0.05 (Standard for 95% probability). This will give us a final F-Test Calculation. If F > F Critical One Tail, then we will reject the Null Hypothesis, which means that the selected data populations are not equal.
How to do F-Test in Excel?
F-Test in Excel is very simple and easy. Let’s understand the working of F-Test in Excel with some examples.
Excel F-Test – Example #1
Suppose we have 6-months data of Demand and Forecast of any product. Data is given in A2:C7.
Now, if we want to test the variation and difference in the variability of data.
- Go to Data in the Menu bar and select Data Analysis.
- Once you click on it, a Data Analysis option box will come. Now select F-Test Two-Sample Variances and click on Ok.
- After that, another dialog box of F-Test will appear.
- Here you need to select the variable range of Demand and Forecast from the data, which is shown below.
- After selecting the Variables 1 Range and Variable 2 Range, choose the desired value of Alpha in the same box. Here, we have taken 0.05 as Alpha, which means we are considering 5% tolerance in calculation and analysis.
- After that, select Output Range cell anywhere in the same sheet or else, you can select a New Workbook as well, which is given just below of it, for your ease. Here we have selected the output range as E2, And then click on OK.
- An F-Test in excel will look like below.
Now let’s analyze the above data;
- The mean of Variable 1 and Variable 2 are 15.66666667 and 16, respectively, which is the mid-point of Demand and Forecast.
- A variance of Variable 1 and Variable 2 are 18.66666667 and 9.2, respectively, which show the variation in the data set.
- Observations of Variable 1 and Variable 2 is 6, which means only 6 data points or parameters are considered in doing F-Test.
- df is the Degree of Freedom, which is shown only 5 variable can be assigned to this statistical distribution.
- P (F<=f) one-tail is the probability distribution of variation in both the data set, which is coming 0.227989507 (22.7% appx).
As you can see, the value of F is 2.02898507, which is lesser than the value of F Critical one-tail, which means that this Null Hypothesis can be accepted.
Excel F-Test – Example #2
Suppose we have Delivery data of some Part Numbers. For better understanding, we have sorted the data in ascending order with column name Date of Delivery, as shown below.
Follow the same process for this data for performing F-Test in Excel. The data set has only one column which has statistical or numeric figures. Here, the analysis will be based on segmenting the dates into two sections.
- As you can see in the below screenshot, for Variable 1 Range data selected is from B2:B9, and for Variable 2 Range data selected is from B10:B16.
- Alpha is kept at 0.05, which is 5% of tolerance (You can change the value of Alpha as per data size and requirement).
- Then select Output Range Cell and click OK.
- An F-Test in excel for Delivery Data of shown Part Numbers will look like this:
Now let’s analyze the above data;
- The mean of Variable 1 and Variable 2 are 26.75 and 26.85714286, respectively, which is the mid-point of Quantity Delivered. And there is not much difference in these parameters.
- A variance of Variable 1 and Variable 2 are 72.21428571 and 235.1428571, respectively, which show the variation in the data set.
- Observations of Variable 1 and Variable 2 are 8 and 7, which means selected upper data points are 8 and selected lower points are 7 in numbers.
- df is the Degree of freedom shown, and only 7 and 6 variables can be assigned to the upper and lower set of data in this statistical distribution.
- P (F<=f) one-tail is the probability distribution of variation in both the data set, which comes as 0.073953335 (7.3% appx).
As you can see, the value of F is 0.307108141, which is greater than the value of F Critical one-tail, which means that this Null Hypothesis cannot be accepted.
Pros of F-Test in Excel
- It can be used in any statistical sets of data where comparison of Before/After, Latest/Previous can be performed in order to accept if the statistical data can be accepted or not.
- Mean gives the mid-value, which is the average of total values; Variance gives the difference between the actual or predicted/future value. So, centricity can be seen easily.
Cons of F-Test in Excel
- For un-statistical background people, it becomes difficult to understand and measure different observations.
- If there is very little difference in F and F Critical one-tail values, then it becomes very difficult to accept or reject the test while performing in real-life scenarios.
Things to Remember
- F-Test can be performed on one or more than one set of data in Excel. It is not restricted to a data set which has two parameters.
- Always sort the data before performing F-Test in Excel. And the sorting parameter should be the base which is correlated with data.
- Do the basic formatting before performing the F-Test to get a good sanitized output.
This has been a guide to F-Test in Excel. Here we discuss its pros and cons and how to do F-Test in Excel along with excel examples and a downloadable excel template. You can also go through our other suggested articles –