Conducting Hypothesis Tests in Excel

Conducting a $z$-Test for a Mean

One can use the Excel function Z.TEST() to conduct a test for a mean ($H_0 : \mu \gt \mu_0$) where the population standard deviation is known. The value output is the $p$-value for a one-sided test. If conducting a two-sided test, the value must be doubled. The syntax is as follows:

Z.TEST(range_for_sample_data, mu, sigma_for_population)

Note, if conducting a two-sided test, the actual $p$-value is double the output of the function. If testing $H_0 : \mu \lt \mu_0$, one must subtract the output from 1 to find the true $p$-value of the test.

Lastly, don't use this test if the population standard deviation is not known. Use a $t$-test instead.

Comparing Two Means

To determine whether two samples are likely to have come from populations that share a common mean, we can use the T.TEST() function in Excel. The syntax is as follows:

T.TEST(range1, range2, tails, type)

The parameters above are:

range1 : the first data set
range2 : the second data set
tails : the number of tails (i.e., 1 or 2)
type : the kind of t-test to perform (i.e., 1 if data is paired, 2 if samples come from populations with equal variances, and 3 if the data comes from populations with unequal variances)

Note, the test returns a $p$-value associated with a non-negative test statistic. The $p$-value may need adjustment if this disagrees with the alternative hypothesis being tested.

Tests for Independence

The CHISQ.TEST() function in Excel can be used to test the independence of two variables. The syntax for this function is given below:

CHISQ.TEST(observed_range, expected_range)

The value returned is the $p$-value associated with values at least as large as the test statistic below:

$$\chi^2 = \frac{\sum (O-E)^2}{E}$$

When conducting a test for independence, the values in the expected_range must be of the form $$\displaystyle{\frac{\textrm{(row total)(col total)}}{\textrm{grand total}}}$$ The degrees of freedom is generally $(r-1)(c-1)$ where $r$ is the number of rows and $c$ is the number of columns.

However, when either $r=1$ or $c=1$, the degrees of freedom used is $c-1$ or $r-1$, respectively. Thus, one can also perform a goodness of fit test with this function.