How to use Z.TEST Function in Excel

Excel Z TEST is a kind of hypothesis test that is used to test the alternative hypothesis against the null hypothesis. The null hypothesis is a hypothesis that refers to a common statement in general. By conducting a hypothesis test we try to prove that the null hypothesis is false against the alternative hypothesis.

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

The Z.TEST function syntax has the following arguments:

1. Array (required argument) – This is the array or range of data against which we need to test x. The array is a set of values against which the hypothesized sample mean is to be tested.
2. X (optional argument) – This is the hypothesized sample. It is the value to be tested.
3. Sigma (optional argument) – This represents the population’s standard deviation if this is known. If omitted, the function uses the sample standard deviation.

Example: Let’s look at some Excel Z.TEST function examples and explore how to use the Z.TEST function as a worksheet function in Microsoft Excel:

Example 1:

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:

Syntax:  =Z.TEST(A2:A13,5)

Result: 0.000123409

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.

Syntax: =2*B16

Result: 0.000246818

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₀: µ_{1 –} µ ₂ = 0

H₁: µ_{1 –} µ ₂ ≠ 0

Where H1 is called an alternative hypothesis, the mean of two populations is not equal.

Example 2:

Let’s take the example of student’s marks of two different subjects.

 

Step 1: The first thing we need to do is to calculate the variables for these two values by using VAR.P function.

Syntax: =VAR.P(B2:B17)

Result: 126.5273438

Syntax: =VAR.P(C2:C17)

Result: 148.1835938

Step 2: Now go to the Data tab and click on Data Analysis.

Step 3: For Variable 1 Range select “Student 1” scores and for Variable 2 Range select “Student 2” scores.

Step 4: Variable 1 Variance select Student 1 variance score and Variable 1 Variance select Student 2 variance score.

Step 5: Select the Output Range as a cell and press Ok.

we got the result.

If Z < – Z Critical Two Tailor Z > Z Critical Two Tail, then we can reject the null hypothesis.

So from the ZTEST result below are results.

• Z < – Z Critical Two Tail = 2.20219052059159 > – 1.959963985
• Z > Z Critical Two Tail = 2.20219052059159 > 1.959963985

Since it is meeting our criteria we can reject the null hypothesis. So the means of two students differ significantly.

Note:

1. Z.TEST represents the probability that the sample mean would be higher than the observed value AVERAGE(array), when the underlying population mean is μ0. From the symmetry of the normal distribution, if AVERAGE(array) < x, Z.TEST will return a value greater than 0.5.
2. #VALUE! error – Occurs when the value of x or the value of sigma is non-numeric.
3. #NUM! error – This error occurs when the sigma argument provided is equal to zero.
4. #N/A error – Occurs when the provided array is empty.
5. #DIV/0! error – Occurs when:
   • The sigma is not provided and the standard deviation of the supplied array is zero.
   • The given array contains just one value.