Using the Central Limit Theorem we can extend the approach employed in Single Sample Hypothesis Testing for normally distributed populations to those that are not normally distributed. Suppose we take a sample of size *n*, where *n* is sufficiently large, and pose a null hypothesis that the population mean is the same as the sample mean; i.e.

If we assume the null hypothesis, we know from the Central Limit Theorem that the sample mean has a normal distribution

This approach works well provided the standard deviation of the population is known, which is not so common.

As we saw in Property 3 of Estimators, the standard deviation of the sample is an unbiased estimator of the standard deviation of the population, and so when the standard deviation of the population is not known, we can estimate *σ* by *s*.

As was done in Confidence Intervals for Sampling Distributions we can now set a confidence interval for the population mean as follows:

**Excel Function**: Excel provides the following two functions that can be useful in hypothesis testing.

**ZTEST**(R, *μ*_{0}, *σ*) = 1 – NORMDIST(*x̄*, μ_{0}, , TRUE) where *x̄* = AVERAGE(R) = the sample mean of the data in range R and n = COUNT(R) = sample size. The third parameter is optional; when it is omitted the value of the sample standard deviation of R is used instead; i.e. ZTEST(R, *μ*_{0}) = ZTEST(R, *μ*_{0}, *s*) where *s* = sample standard deviation = STDEV(R).

**CONFIDENCE**(*α*, *σ*, *n*) = *k* such that (*x̄* – *k*, *x̄* + *k*) is the confidence interval for the mean based on the normal distribution; i.e. CONFIDENCE(*α*, *σ*, *n*) = *z _{crit} *∙ std err, where

*n*= sample size,

*σ*= population standard deviation (or sample standard deviation s used as an estimate for

*σ*) and 1 –

*α*is the confidence %

Excel 2010/2013 also provide two additional functions **Z.TEST** and **CONFIDENCE.NORM** which are equivalent to ZTEST and CONFIDENCE respectively.

**Observation**: ZTEST(R, *μ*_{0}, *σ*) represents the probability that the true sample mean is greater than the observed sample mean AVERAGE(R) under the assumption that the population mean is *μ*_{0}. This is a right tail test (i.e. it assumes that *x̄* ≥ *μ*_{0}). If *x̄* < *μ*_{0} then ZTEST will return a value > .5.

If a left-tail test is desired (assuming *x̄* ≤ *μ*_{0}), then use 1 – ZTEST(R, *μ*_{0}, *σ*). If a two-tail test is desired then use 2 * MIN(ZTEST(R, *μ*_{0}, *σ*), 1 – ZTEST(R, *μ*_{0}, *σ*)).

**Observation**: ZTEST and Z.TEST ignore any empty cells and cells with non-numeric values.

**Observation**: We could have calculated the confidence interval in Example 1 of Confidence Intervals for Sampling Distributions as follows:

CONFIDENCE(.05, 20, 60) = 5.06, and so the population mean is in the interval (80 – 5.06, 80 + 5.06) = (74.94, 80.06) with 95% confidence.

**Example 1**: A company selling batteries claims that the average life for its batteries before a recharge is necessary is at least 100 hours. One of its clients wanted to verify this claim by testing 48 batteries as described in the Figure 1. Is the company’s claim correct?

**Figure 1 – One sample testing of the mean using ZTEST and CONFIDENCE**

We test the following null hypothesis:

H_{0}: *x̄* ≤ 100

Since the sample size is sufficiently large (*n* = 48 ≥ 30), based on the Central Limit Theorem, the sampling distribution of the mean should be approximately normal with distribution *N*(*x̄*, ). Since the population standard deviation is not known we use the sample standard deviation (23.96) as an estimate for *σ*, and so the standard error is

Since the sample mean *x̄* = 103.81, assuming the null hypothesis we can compute the p-value as follows:

p-value = 1 – NORMDIST(103.81, 100, 3.46, TRUE) = .135 > .05 = *α*

Since p-value > α, we cannot reject the null hypothesis, and so conclude there is not enough evidence to show that the company’s claim is false. Alternatively, we can arrive at the same result by using the ZTEST as follows:

p-value = ZTEST(A3:F10, 100) = .135 > .05 = *α*

**Observation**: If we had run a two-tail test, we could calculate CONFIDENCE(*α*, *s*, *n*) = CONFIDENCE(.05, 23.96, 48) = 6.78, and so the 95% confidence interval is (103.81 – 6.78, 103.81 + 6.78) = (97.03, 110.59). Since the hypothetical mean of 100 lies in this interval, we must retain the null hypothesis.

**Real Statistics Excel Functions**: The Real Statistics Resource Pack contains the following supplemental functions:

**STDERR**(R1) = STDEV(R1) / SQRT(COUNT(R1)), i.e. standard error for the data in range R1

**NORM_CONF**(R1, *α*) = CONFIDENCE(*α*, STDEV(R1), COUNT(R1))

**NORM_LOWER**(R1, *α*) = AVERAGE(R1) – NORM_CONF(R1, *α*)

**NORM_UPPER**(R1, *α*) = AVERAGE(R1) + NORM_CONF(R1, *α*)

**Observation**: All these functions ignore any empty cells and cells with non-numeric values. If *α* is omitted it defaults to .05.

**Observation**: For Example 1, we have STDERR(A3:F10) = 3.46, NORM_CONF(A3:F10, .05) = 6.78, NORM_LOWER(A3:F10, .05) = 97.03 and NORM_UPPER(A3:F10, .05) = 110.59.

Hi Charles,

Thank you for putting this website together, it is amazingly helpful.

I am a bit confused whether the Z.TEST function gives the p-value for a left-tail test or a right-tail test though…

You say here that it is a right-tail test, but the p-value it gives back is equal to NORM.DIST(100, 103.81, 3.46, TRUE) and not 1-NORM.DIST(100, 103.81, 3.46, TRUE), hence corresponding to a left-tail test.

Regards,

Florian

Florian,

Sorry for the delayed response.

You are testing the hypothesis that the population mean is 100. Thus the second argument in the NORM.DIST function should be 100 and not 103.81 (I have also corrected this on the referenced webpage). Note that the p-value = Z.TEST(A3:F10, 100) = 1-NORM.DIST(103.81, 100, 3.46, TRUE), a right tailed test (since 103.81 > 100).

Charles

Hi Sir

I find a problem with NORM_CONF function. I used the example above but NORM_CONF gave me a result of 498.28

Colin,

The NORM_CONF, NORM-LOWER and NORM-UPPER functions were implemented poorly. I have corrected the Real Statistics Resource Pack software and the revised version is now available (with release 2.8).

Charles