Excel Z TEST Function (Table of Contents)
Z TEST in Excel
With the help of Z-Test, we compare the means of two datasets in Excel that are equal or not. In Excel, we have a function for Z-Test named as ZTest, where, as per syntax we need to have Array and X value (Hypothesized sample mean) and Sigma value (Optional). Mostly X is considered a minimum of 95% of probability for that it can be taken from 0 to 5. Another way of doing Z-Test is from the Data Analysis option from the Data menu tab. There we would need 2 variable ranges, 2 variances of each range. If Z < Z Critical then we will reject the null hypothesis.
Z TEST Formula in Excel
Below is the Z TEST Formula:
Z TEST Formula 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 in excel, 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, which falls under the statistical functions category. This Z TEST function in excel gives the one-tailed probability value of a test.
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 is very simple and easy to use.
4.8 (6,296 ratings)
View Course
Working of Z TEST Function in Excel with Examples
Let’s understand the working of Z TEST Function in Excel with some example.
Example #1
We have given below set of values:
To calculate the one-tailed 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 as shown below:
The result is given below:
We can also calculate the two-tailed probability of a Z TEST here by using the above result.
The formula is given below for calculating the two-tailed P-value 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.
H0: µ1 – µ 2 = 0
H1: µ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.
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 z-Test: Two Sample for Means and click on OK as shown below.
- It will open a dialog box for Z-test 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
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 non-numeric.
- #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.
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 –