One Sample Z test in Excel

One Sample Z test in Excel In this tutorial, we will show how you can perform a one sample z-test using Excel. A z-test is a statistical tool in hypothesis testing to test the mean of a distribution when the variances are known or the sample size is large. Download Excel file: https://softtechtutorials.com/microso... 0:00 One Sample Z-test intro 0:55 Defining the Problem for the Z-test 1:21 Finding the Population Variance 2:05 Performing the Z-test in Excel 3:40 Z-test Output - Variable information 4:00 Difference between One- and Two-tailed Tests 5:04 P-values 1. One Sample Z-test in Excel We have 30 observations of the average resting heart rate of athletes. We know that a normal resting heart rate for adults ranges from 60 to 100 beats per minute. We know that an athlete often has a lower heart rate. What we will investigate here is whether or not the athletes’ average heart rate is significantly smaller than 60, which represents the lower end of the normal heart rate range. 2. Defining the Problem for the Z-test To test this, we state our null hypothesis H_0 as the average heart rate of the athletes stated by mu_athletes strictly higher than 60. The alternative hypothesis is then mu_athletes is smaller than or equal to 60. 3. Finding the Population Variance Before we can perform the test, we need to know the population variance of the average heart rate of our athletes. Since we have independent and identically distributed observations, we know that the sample variance converges to the population variance. 4. Performing the Z-test in Excel Now, we are ready to do the z-test. We navigate to Data and select Data Analysis. A menu opens where we scroll down to z-Test: Two Sample for Means. We select this and press OK. Here, we have to enter both variable ranges. Variable 1 is the average heart rate of the athletes in range A3 till A52. As the second variable, we will insert the hypothesized value in cell B3. To fill in the hypothesized mean difference we have to look at the null hypothesis. We assume that the mean is 60 which is the same as saying that the difference between the mean and the hypothesized value is 0. We just computed the variance of the variable, this is 104. We also need the variance for our second variable. As 60 is the exact value for the second variable, the variance should be 0. However, Excel won’t allow us to enter this value. To circumvent this shortcoming, we will enter the very small number 1E-99 (1E minus 99). We did not include the labels in our variable ranges, so we keep this box unchecked. Next, we have to enter the level of confidence for our test. This is automatically set to 0.05 which corresponds to a 95% confidence level. Finally, we choose where the output of the z-test should appear. 5. Z-test Output 5.1. Variable information The first half of the output table summarizes the variables. We see the means, variances and number of observations. Next, we see the hypothesized mean difference, the value of the test statistic z and the p-value and critical z-value for a one-tailed and two-tailed test. 5.2. Difference between One- and Two-tailed Tests The difference between a one- and a two-tailed test is shown in the graphs next to the table. A one-tailed test is where you are only interested in one direction. If a mean is x, you might want to know if a set of results is more than x or less than x. In a one-tailed test we therefore reject the null hypothesis when the test statistics’s value is higher than the critical z-value or rather lower than the critical value depending on what you are interested in. In a two-tailed test, we look at both ends of the distribution and we will reject the null when the test statistic’s value is smaller than the lower z-value or higher than the upper z-value. In this case, we are interested in a one-tailed test as we want to test if the average heart rate is significantly smaller than 60. We observe that the test statistic’s value is lower than the one-tail critical z-value. So, we can conclude that the average heart rate of athletes is significantly lower than 60. 5.3. P-values Another way to conclude this is by making use of the p-value. The p-value for the one-tailed test is 4 to the power -12. This value is smaller than our confidence level of 0.05, so we can conclude to reject the null hypothesis. This concludes our video on two sample z-test in Excel. #Excel #Tutorials #Statistics