This function returns the one-tailed probability value for a Gauss test (normal distribution). For a given expected value of a random variable (μ0), 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.
Syntax
Z.TEST(array ; μ0 ; sigma)
Arguments
- array (required) – The array or range of data against which to test μ0.
- μ0 (required) – The value to test.
- sigma (optional) – The known standard deviation of the population. If omitted, the sample standard deviation is used.
Notes
- If array is empty, Z.TEST() returns the #N/A error.
- The calculation differs depending on whether sigma is provided:
When sigma is specified:
When sigma is omitted:
Where:
-
- x̄ = sample mean (AVERAGE(array))
- s = sample standard deviation (STDEV.S(array))
- n = number of observations (COUNT(array))
- Φ = standard normal cumulative distribution function
- Z.TEST() indicates the probability that the sample mean is greater than the observed mean (AVERAGE(array)) when the expected value is μ0.
- Due to the symmetry of the normal distribution, if AVERAGE(array) < μ0, Z.TEST() returns a value greater than 0.5.
- For a two-tailed probability test, use:
=2×MIN(Z.TEST(array,μ0,sigma),1−Z.TEST(array,μ0,sigma))=2×MIN(Z.TEST(array,μ0,sigma),1−Z.TEST(array,μ0,sigma))
Background
The Gaussian test (named after mathematician Carl Friedrich Gauss) is a statistical test based on the standard normal distribution. It examines the significance of a value from a normally distributed population where the expected value (μ0) and standard deviation (sigma) must be known.
Example
Calculate Z.TEST() using the following parameters:
- Data = The range of values to test against μ0
- μ0 = 4 (first test value)
- μ0 = 6 (second test value)
Results (as shown in Figure below):
- For μ0 = 4, the one-tailed probability is 0.09057 (9.06%).
- For μ0 = 6, the one-tailed probability is 0.86304 (86.30%).