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/microsoft-office/excel/one-sample-z-test-in-excel/

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 o ... https://www.youtube.com/watch?v=AFWDlXOzXP4