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. Z-test 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:
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. 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 –
