Syntax. Z.TEST(array,μ₀,sigma)

Definition. This function returns the one-tailed probability value for a Gauss test (normal distribution). For the expected value of a random variable (μ₀), the Z.TEST() function returns the probability that the sample mean would be greater than the average of observations in the data set (array)—that is, the observed sample mean.

Arguments

Note

If array is empty, the Z.TEST() function returns the #N/A error.

Z.TEST() is calculated as follows when sigma is specified:

or when sigma is omitted:

where:

• x is the sample mean AVERAGE(array).

• s is the sample standard deviation STDEV.S(array).

• n is the number of observations in the sample COUNT(array).

Z.TEST() indicates the probability that the sample mean is greater than the observed value AVERAGE(array) when the underlying expected value of a random variable is μ₀.

Because of the symmetry of the normal distribution, if AVERAGE(array) is smaller than μ₀, Z.TEST() returns a value greater than 0.5.

The following Excel formula can be used to calculate the two-tailed probability that the sample mean is further from μ₀ (in either direction) than AVERAGE(array) when the underlying expected value of a random variable is μ₀:

=2 * MIN(Z.TEST(array,μ₀,sigma), 1 – Z.TEST(array,μ₀,sigma))

Background. The Gaussian test (named after the mathematician Carl Friedrich Gauss) is a statistical test based on the standard normal distribution. This test is used to examine the significance of a value from a normal distributed population where the expected value and the standard deviation have to be known.

Example. Use the following values to calculate Z.TEST():

Figure 12-156 shows the calculation of Z.TEST().

Figure 12-156. Calculating Z.TEST().

The Z.TEST() function returns the one-tailed probability value for a Gaussian test by using the parameters shown in Figure 12-156. The following results were calculated: