Z Test in Excel

In this tutorial we will learn how to perform Z Test in Excel. When we want to compare two means when variance are known, then we use Z Test. Z Test is the test of null hypothesis, which states the mean of two population are equal.

H0: μ1 – μ2 = 0
H1: μ1 – μ2 ≠ 0

Alternate hypothesis H1 states that the two population means are not equal.

Note: Z test is performed when the variance of the two population are known

Data:

We will be using the scores of students across two different subjects to depict example on Z test in Excel, Which is shown below.

Z Test in Excel 1

 

Step 1: calculate population variance of variable 1 and variable 2 with VAR.P formula as shown below

Z Test in Excel 2

 

Step 2: On the top right corner of the data tab click data analysis.

Z Test in Excel 3

Note: if you can’t able to find the Data Analysis button? Click here to load the Analysis ToolPak add-in.

 

Step 3: Select Z – Test: Two Sample for Means and click ok

Z Test in Excel 4

 

Step 4: Click in the Input Range boxes and select the Variable 1 Range A2:A10 and Variable 2 Range B2:B10. Mention the variable 1 variance and variable 2 variance that we have calculated and also mention the output range as shown below and click ok

z-test-in-excel-5

So the result will be

z-test-in-excel-6

If z < – z Critical two-tail or z Stat > z Critical two-tail, we reject the null hypothesis. In our case 0.213 > -1.959 and 0.213 < 1.959, so we cannot reject null hypothesis.  So both the means do not differ significantly.

previous small Z Test in excel                                                                                                                next small Z Test in Excel