Z Test in Excel (Table of Contents)
Excel Z Test
Excel Z TEST is a kind of hypothesis test that is used to test the alternative hypothesis against the null hypothesis. The null hypothesis is a hypothesis which refers to a common statement in general. By conducting a hypothesis test we try to prove that the null hypothesis is false against the alternative hypothesis.
Z-TEST is one such hypothesis test function. This tests the mean of the two sample data sets when the variance is known and the sample size is large. Sample size should be >= 30 otherwise we need to use T-TEST. To ZTEST we need to have two independent data points which are not related to each other or doesn’t affect each other data points and data should be normally distributed.
The general formula for Z TEST is as follows.
- m = mean or an average of the sample.
- u0 = mean of population.
- α = Standard Deviation of the population.
- n = No., of Observations.
This is a brief introduction to Z TEST. Now we will see how this can be performed in excel spreadsheet using formula.
Z TEST in Excel with Formula
Z.TEST is the built-in function in excel. Below is the formula of Z.TEST function in excel.
- Array: This is the range of cells which contains data points against which we need to test X. This is the value of cells against hypothesis sample mean is to be tested.
- X: From the array the Xth value to be tested.
- Sigma: This is the overall population’s standard deviation. This is an optional argument if this is omitted then excel use the sample standard deviation.
How to Perform the Z Test in Excel?
Below are the examples of Z Test in excel.
Example #1 – Using the Z Test Formula
For an example look at the below data.
Using this data we will calculate the One-tailed probability value of Z TEST. For this assume hypothesis population means is 6.
So open Z TEST formula in excel cell.
Select the array as scores i.e. A2 to A11.
Next argument is “X”. Since we have already assumed the hypothesized population mean is 6, apply this value to this argument.
The last argument is optional, so close the formula to get the Z TEST value.
This is one-tailed Z TEST value to get the two-tailed Z TEST value to multiply this value by 2.
Example #2 – Z TEST Using Data Analysis Option
We can conduct Z TEST by using the Data Analysis option in excel. In order to compare two means when variance are known we use Z TEST. We can frame two hypotheses here, one is “Null Hypothesis” and another one is “Alternative Hypothesis”, below is the equation of both these hypothesis.
H0: μ1 – μ2 = 0 (Null Hypothesis)
H1: μ1 – μ2 ≠ 0 (Alternative Hypothesis)
The alternative hypothesis (H1) states that the two population means are not equal.
For this example, we will use Two Students scores in multiple subjects.
The first thing we need to do is to calculate the variables for these two values by using VAR.P function.
Now go to the Data tab and click on Data Analysis.
Scroll down and select z-Test Two Sample for means and click on Ok.
For Variable 1 Range select “Student 1” scores and for Variable 2 Range select “Student 2” scores.
Variable 1 Variance select Student 1 variance score and Variable 1 Variance select Student 2 variance score.
Select the Output Range as a cell and press Ok.
we got the result.
If Z < – Z Critical Two Tailor Z > Z Critical Two Tail, then we can reject the null hypothesis.
So from ZTEST result below are results.
- Z < – Z Critical Two Tail = -1.080775083 > – 1.959963985
- Z > Z Critical Two Tail = -1.080775083 < 1.959963985
Since it is meeting our criteria we cannot reject the null hypothesis. So means of two students do not differ significantly.
Things to Remember here
- All the arguments should be numerical value other-wise we will get #VALUE!.
- Array value should contain numbers otherwise we will get #N/A error.
- ZTEST can be applied to large data sets.
This has been a guide to Z Test in Excel. Here we will learn how to perform Z test in excel using its formula along with examples and with downloadable template. You may learn more about excel from the following articles –