The data analysis tool is another library function in Excel which is found under the tab Data and Data Analysis in the group Analysis. If you don’t find it on the ribbon, you need to install it in the same way as we explained for the solver in section 4.2.
When we choose Data Analysis on the ribbon, the dialogue box in figure 5.1 appears. Here we can choose different statistical tests, time series analysis, simulations, etc. The next example illustrates how we can perform an F-test in an effective manner using the data analysis tool.
Figure 5.1 The Data Analysis dialogue box.
Example 5.1
The columns A and B in the spreadsheet in figure 5.2 contain two sets of data observed from two different populations. We want to run an F-test to see whether the two populations show different variances or not.
In the dialogue box in figure 5.1 we choose F-Test Two-Sample for Variances and click OK, so that the dialogue box in figure 5.2 appears. As Variable 1 Range and Variable 2 Range we choose the cells A4:A10 and B4:B9 respectively. We choose a 5 per cent significance level and set Alpha to 0.05. If we choose D3 as Output Range and click OK, the results shown to the right in figure 5.2 appear. In cell E11 we see that the p-value is 0.46, which means that we cannot claim that the two populations show different variances.
Figure 5.2 F-test with the data analysis tool.
Problems
5-1. We want to check if the two different methods A and B (for concentration measurements) give different results. The two methods are used on the same liquid solution with the following results:
Perform an F-test and a t-test with the data analysis tool in Excel and check if the two methods give different results.