Z.TEST function - The function returns one-sided value of the z test in Excel

The following article introduces you to the Z.TEST function - one of the functions in the statistical function group is very popular in Excel.

Z.TEST function - The function returns one-sided value of the z test in Excel Picture 1

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 - The function returns one-sided value of the z test in Excel Picture 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 - The function returns one-sided value of the z test in Excel Picture 3

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

Z.TEST function - The function returns one-sided value of the z test in Excel Picture 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 - The function returns one-sided value of the z test in Excel Picture 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 - The function returns one-sided value of the z test in Excel Picture 6

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

Good luck!