ZTEST

Syntax: ZTEST(Range, X[, S])

Range = a range containing sample values
X = a value to be tested
S = (optional) the standard deviation of the sampled population, if known

ZTEST returns the Z-score for a two-tailed test of the hypothesis that X is the mean of the population from which the samples in Range are drawn. The population is assumed to be normally distributed with standard deviation S. If S is omitted, it is estimated from the sample values in Range. If S is given, it must be greater than 0.

The mathematical formulation is given as follows:

Let z = $\frac{(\mu - X)\sqrt{N}}{\sigma}$, where $\mu$ is the actual mean, N is the number of samples, and $\sigma$ is the standard deviation. Then,

$$\mbox{Z-score} = 1 - \int_{-\infty}^{z} \frac{1}{\sqrt{2\pi}} e^{-\frac{t^2}{2}} dt$$

Examples:

Suppose A1..A10 contains the sample values 0.552, 5.090, 4.494, 7.833, 7.101, 0.590, 2.439, -3.197, 8.861, and -2.795.

ZTEST(A1..A10, 3) = 0.47151484

ZTEST(A1..A10, 3, 4) = 0.46949987

ZTEST(A1..A10, 3, 0) = Error

Excel function: ZTEST