ZTEST Function

The Excel ZTEST function examines the means of two independent datasets. This function retrieves the z-tests one-tailed probability value further compared against the significance level(0.05). You may use the built-in Ztest function or the Descriptive Analysis Toolpak to produce a probability value.

Statisticians generally employed these T-test, Z test functions for hypothesis testing in large datasets. If the outcome of the z-test value is less than 0.05, you can accept the alternative hypothesis otherwise accept the null hypothesis inversely.

The ZTEST function has been replaced by the Z.TEST function in Excel 2010.

Compatibility

This advanced Excel function is compatible with the following versions of MS-Excel −

Excel for Microsoft 365
Excel for Microsoft 365 for Mac
Excel for the web
Excel 2021
Excel 2021 for Mac
Excel 2019
Excel 2019 for Mac
Excel 2016

Syntax

The syntax of ZTEST is as follows −

ZTEST (array,x,[sigma])

Arguments

You can use the following arguments with the ZTEST function −

Argument	Description	Required / Optional
Array	The sample dataset comprises numeric values.	Required
X	It specifies the testing value.	Required
Sigma	It denotes the standard deviation of the entire population. If not defined, the standard deviation of the sample will employ.	Optional

Points to Note

The defined array must contain integer values.

The following expression is employed to calculate the two-tailed probability.
= 2 * MIN(ZTEST(a, x, sg), 1 - ZTEST(a, x, sg))
Here, a denotes the array and the sg parameter specifies the sigma.

If any field value contains a logical value, then that field is excluded, the remaining fields are employed to evaluate the z-test.
If the array is empty, ZTEST returns the #N/A error value.
If all the data points contain zero values, ZTEST returns the #DIV/0! error value.

Examples of ZTEST Function

Practice the following examples to learn the use of the ZTEST function in Excel.

Example 1

If defined array comprises only integer values.

Solution

Consider the sample dataset and write the formula "=ZTEST(B3:B12,4)" in the B13 cell. Here, the first argument specifies the array range, and the second argument '4' represents the field value required for testing. Press the "Enter" tab to obtain the computed value of the One tail probability of a Z test.

Example 2

Use this expression to calculate the two-tailed probability.

= 2 * MIN(ZTEST(a, x, sg), 1 - ZTEST(a, x, sg))

Solution

Assume the sample dataset where the name of the players and the player's score for the Crossword Puzzles is specified in the C column.

Enter the formula "=2 * MIN(ZTEST(C4:C9,5), 1 - ZTEST(C4:C9,5))" in the C11 cell and press the Enter tab.

Example 3

If any field value contains a logical value, then that field is excluded, the remaining fields are employed to evaluate the z-test.

Solution

Assume the sample dataset where "Players score" consists of integer and logical values.

You may enter the formula "=ZTEST(C4:C9,4)" in the C11 cell.

Example 4

If all the data points contain zero values, ZTEST returns the #DIV/0! error value.

Solution

You may enter the formula "=ZTEST(C4:C9,4)" in the C11 cell and press the Enter tab.

Therefore, you can observe in the screenshot, the #DIV/0! error occurred in this case.

Example 5

If the array is empty, ZTEST returns the #N/A error value.

Solution

You can enter the formula "=ZTEST(C4:C9,4)" in the C11 cell.

As you can notice in the screenshot, the #N/A error will be retrieved in case of null values.

Save or share this article