Excel Z TEST Function (Table of Contents)
Z TEST in Excel
When we have given two datasets of values and want to compare the means of both of the datasets, then we use Z TEST Function in excel. Ztest is a statistical test and is used to perform statistical analysis.
What is Z TEST Function in Excel?
If we want to compare the means of two datasets or in other words, we used to determine whether the means of two data sets are different or equal then we use Z TEST. This test is useful when the sample size is large and variances are known.
Z TEST Formula in Excel
Below is the Z TEST Formula in Excel:
Z TEST Formula in Excel has the below arguments:
 Array: The given set of values for which the hypothesized sample mean is to be tested.
 X: The hypothesized sample mean which is required to test.
 Sigma: This is an optional argument which represents the population standard deviation. If it’s not given, or unknown then use the sample standard deviation.
How to Use the Z Test Function in Excel?
There are two ways to use Z TEST, which are:
 One sample Z TEST
 Two sample Z TEST
Here we will cover both ways one by one in detail.
One sample Z TEST:
If we have given one dataset, then we use Z TEST function in excel, which falls under the statistical functions category. This Z TEST function gives the onetailed probability value of a test.
4.8 (260 ratings)
Z TEST function:
This function gives you the probability that the supplied hypothesized sample mean is greater than the mean of the supplied data values.
Z TEST Function in Excel is very simple and easy to use. Let’s understand the working of Z TEST Function in Excel with some example.
Excel Z TEST – Example #1
We have given below set of values:
To calculate the onetailed probability value of a Z Test for the above data, let’s assume the hypothesized population mean is 5, now we will use the Z TEST formula in excel as below:
The result is given below:
We can also calculate the twotailed probability of a Z TEST here by using the above result.
The formula is given below for calculating the twotailed Pvalue of a Z TEST for given hypothesized population means which is 5.
The result is given below:
Two Sample Z Test:
While using the Z Test, we test a null hypothesis which states that the mean of the two population is equal.
i.e.
H_{0}: µ_{1 – }µ _{2} = 0
H_{1}: µ_{1 – }µ _{2} ≠ 0
Where H1 is called an alternative hypothesis, the mean of two populations is not equal.
Let’s take an example to understand the usage of two sample Z Test.
Excel Z TEST – Example #2
Let’s take the example of student’s marks of two different subjects.
Now we need to calculate the variance of both subjects, so we will use the below formula for this:
The above formula applies for Variance 1 (Subject 1) like below:
The result is given below:
The above same formula applies for Variance 2 (Subject 2) like below:
The result is given below:
 Now, Go to Data Analysis tab in the extreme upper right corner under the DATA tab as shown in below screenshot:
 It will open a dialog box Data Analysis options.
 Click on zTest: Two Sample for Means and click on OK as shown below.
 It will open a dialog box for Ztest as shown below.
 Now in Variable 1 range box, select subject 1 range from A25:A35
 Similarly, in Variable 2 range box, select subject 2 range from B25:B35
 Under Variable 1 variance box enter cell B38 variance value.
 Under Variable 2 variance box,enter cell B39 variance value.
 In Output Range, Select cell where you want to see the result. Here we have passed cell E24 and then click on OK.
The result is shown below:
Explanation
 If z < z Critical two tail or z stat > z Critical two tail, so we can reject the null hypothesis.
 Here 1.279 > 1.9599 and 1.279 < 1.9599 hence we can’t reject the null hypothesis.
 Thus, means of both the populations don’t differ significantly.
Things to Remember about the Z TEST Function in Excel
Z test is only applicable for two samples when the variance of both the population is known. While using the Z Test function below error occurs:
 #VALUE! error: If the value of x or Sigma is nonnumeric.
 #NUM! error: If the Sigma argument value is equal to zero.
 #N/A error: If the dataset values or passed array is empty.
 #DIV/0! error: This error occurs in two conditions:
 If the given array contains only one value.
 The sigma is not given and the standard deviation is zero of the passed array.
You can download this Z TEST Excel Template here – Z TEST Excel Template
Recommended Articles
This has been a guide to Z TEST in Excel. Here we discuss Z TEST Formula and how to use Z TEST Function in Excel along with practical examples and downloadable excel templates. You can also go through our other suggested articles –
 Excel T.Test Function
 One Variable Data Table in Excel
 MATCH Function in Excel
 Easy Tutorials for Errors in Excel
Excel VBA Course  All in One Bundle
120+ Online Courses
1000+ Hours
Verifiable Certificates
Lifetime Access

Bonus Excel Courses

Excel Course

Excel Advanced course

VBA Course

Excel Data Analysis Course
Leave a Reply