Two Sample Z test in Excel

Two Sample Z test in Excel

In this video, we will show how you can perform a two 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. Generally, we can speak of a large sample in these kinds of settings when the sample size n is larger than or equal to 30. Under the null hypothesis, the test statistic is approximately normally distributed.

0:00 Two Sample Z-test in Excel Intro 0:42 Defining the Problem for the Z-test 1:01 Finding the Population Variance 1:51 Performing the Z-test in Excel 3:10 Z-test Output 3:31 Difference between One- and Two-tailed Tests 4:55 P-values

To do a z-test this, we state a null and alternative hypothesis. Before we can perform the test, we need to know the population variances of both groups of data. When we have independent and identically distributed observations, we know that the sample variance converges to the population variance.

So, when our sample is large enough and this is the case when we have 30 observations or more, we can substitute the sample variance for the population variance.

Hence, we first compute the sample variances. This can be done by using the function VAR.S which computes the sample variance of a given range. 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.

To fill in the hypothesized mean difference we have to look at the null hypothesis. We assume that both means are the same which is the same as saying that the difference between them is 0. We just computed the variances of both variables, so we fill these into the corresponding boxes. 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.

This is a commonly used level, so we keep this. The first half of the output table summarizes the variables. We see the means, variances, 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.

The difference between a one- and a two-tailed test is shown in the graphs. 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’ value is higher than the critical z-value or rather lower than the critical value depending on what you a ... https://www.youtube.com/watch?v=oeD2NSZVKlw