Z. TEST Function in Excel

This guide provides a practical overview of Z. TEST Function, including its main features, benefits, limitations, and important considerations.

Z. TEST Function in Excel screenshot

Description: The function returns one-sided P value of the z test. Support functions from Excel 2010 onwards.

Syntax: Z. TEST (array, x, [sigma])

Inside:

- array: An array or range of data to test for x, which is a required parameter.

- x: Value to check, is the required parameter.

- sigma: The overall standard deviation, which is an optional value, if omitted the sample standard deviation to be used.

Attention:

- If array is empty -> the function returns the # N / A error value

- When sigma is omitted -> Z. TEST is calculated by the formula:

Z. TEST (array, x, sigma) = 1- Norm. S. Dist ((Average (array) - x) / (sigma / ?n), TRUE)

- Where sigma is not ignored -> Z. TEST function is calculated by the formula:

Z. TEST (array, x) = 1- Norm. S. Dist ((Average (array) - x) / (STDEV (array) / ?n), TRUE)

Inside:

+ x is the sample mean AVERAGE (array)

+ n is COUNT (array).

- Z. TEST shows that when the base overall average is ?0 -> the probability of sample mean will be greater than the observed value of AVERAGE (array).

- You can use the following formula to calculate the probability of 2 sides:

= 2 * MIN (Z. TEST (array, x, sigma), 1 - Z. TEST (array, x, sigma)).

For example:

Calculate the 1-sided probability of z-tests as described in the following data table:

Z. TEST Function in Excel screenshot 2

- Calculate the 1-sided probability value of the z-test for the dataset above. In a cell to calculate, enter the formula : = Z. TEST (D6: J6, D7, D8)

Z. TEST Function in Excel screenshot 3

- Press Enter -> probability value 1 side of the z test for the above data set is:

Z. TEST Function in Excel screenshot 4

- Calculate the one-sided probability value of the z-test for the data set above, ignoring the overall standard deviation. In a cell to calculate enter the formula : = Z. TEST (D6: J6, D7)

Z. TEST Function in Excel screenshot 5

- Press Enter -> probability value 1 side of the z test for the above data set, ignoring the overall standard deviation of:

Z. TEST Function in Excel screenshot 6

Above are instructions and some specific examples when using the Z. TEST function in Excel.

Good luck!

FAQ

What does the Z. TEST Function function do in Excel?

The Z. TEST Function function performs the calculation described in this guide. Use the correct arguments and compatible data types for reliable results.

How do I enter the Z. TEST Function function correctly?

Start with an equals sign, enter Z. TEST Function, add the required arguments inside parentheses, and confirm that cell references and separators are correct.