**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.

**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.

#### 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 –