Hypothesis Testing Using Excel
Using an Excel worksheet, you can conduct a hypothesis test.  The first decision that needs to be made is the type of test: upper-tail, lower-tail, or two-sided test.  The type of information available dictates the specific type of test that will be made: Z-test or t-test.  Below are examples of how a hypothesis test can be conducted using Excel.

Hypothesis Test for m (s known)--Two-sided Z-Test
Follow the steps listed below to perform a two-sided hypothesis test concerning a population mean when the population standard deviation is known.  Start with a blank Excel worksheet.
Example 1. A manufacturer of canned foods uses a machine to fill its cans with fruit.  The machine is designed to fill cans with 16 ounces of fruit with a standard deviation of .30 ounce.  Periodically, a random sample of 100 cans from the machine's output is selected  and weighed.  The last sample of 100 cans yielded a sample mean of 15.92 ounces.  Test the null hypothesis that the machine is operating as designed (m = 16 oz.) at the 5% level of significance.
1.  H₀: m = 16      H₁: m ¹ 16    [This test will be a two-sided Z-test since s is known.]
2.  Enter the labels for the worksheet.  Select cell A1 and enter the label Sample size n.  Select cell A2 and enter the label Sample mean.  Select cell A3 and enter the label s.  Select cell A4 and enter the label Standard error.  Select cell A5 and enter the label Null hypothesis  m = .  Select cell A6 and enter the label a.  Select cell A7 and enter the label Z test statistic.  Select cell A8 and enter the label Lower critical value.  Select cell A9 and enter the label Upper critical value.  Select cell A10 and enter the label Decision.
3.  Enter the given information. Select cell B1 and enter 100 (the sample size).  Select B2 and enter 15.92 (the sample mean).  Select B3 and enter .30 (the population standard deviation).  Select cell B5 and enter 16 (null hypothesis mean value).  Select cell B6 and enter .05 (significance level).
4.  Standard error.  Select cell B4 and enter the following formula:  =B3/SQRT(B1).  Excel computes the value of .03.
5.  Z test statistic.  Select cell B7 and enter the following formula:  =(B2-B5)/B4.  Excel computes the value of -2.6667. 
6.  Lower critical value.  Select cell B8 and enter the following formula:  =NORMSINV(B6/2).  This formula returns the Z value that corresponds to a lower tail area of .025.  Excel computes the value of -1.95996.
7.  Upper critical value.  Select cell B9 and enter the following formula:  =NORMSINV(1-(B6/2)).  This formula returns the Z value that corresponds to an upper tail area of .025.  Excel computes the value of 1.959961.
8.  Decision.  Select cell B10 and enter the following formula:  =IF(ABS(B7)<=B9, "Do not reject H0", "Reject H0").  This formula compares the computed Z test statistic with the critical Z values.  If the computed Z value falls on or between the two critical values, the null hypothesis is accepted.  If the computed Z value falls outside of the two critical values, the null hypothesis is rejected.  Excel makes the desired decision to Reject H0 at the 5% level of significance.

Hypothesis Test for m (s known)--One-sided Z-Test
Follow the steps listed below to perform a one-sided hypothesis test concerning a population mean when the population standard deviation is known.  Start with a blank Excel worksheet.
Example 2.  For the same manufacturer discussed in Example 1, assume that it is suspected that the machine is overfilling cans of fruit.  A random sample of 36 cans is selected and weighed from the machine's output and the sample mean is computed to be 16.13 ounces.  Assume that the population standard deviation of .30 ounce still applies.  Test the null hypothesis that the machine is not overfilling cans (m £ 16) at the 1% significance level.
1.  H₀: m £ 16      H₁: m > 16    [This test will be an upper-tailed Z-test since s is known.]
2.  Enter the labels for the worksheet.  Select cell A1 and enter the label Sample size n.  Select cell A2 and enter the label Sample mean.  Select cell A3 and enter the label s.  Select cell A4 and enter the label Standard error.  Select cell A5 and enter the label Null hypothesis  m £ .  Select cell A6 and enter the label a.  Select cell A7 and enter the label Z test statistic.  Select cell A8 and enter the label Upper critical value.  Select cell A9 and enter the label Decision.
3.  Enter the given information. Select cell B1 and enter 36 (the sample size).  Select B2 and enter 16.13 (the sample mean).  Select B3 and enter .30 (the population standard deviation).  Select cell B5 and enter 16 (null hypothesis mean value).  Select cell B6 and enter .01 (significance level).
4.  Standard error.  Select cell B4 and enter the following formula:  =B3/SQRT(B1).  Excel computes the value of .05.
5.  Z test statistic.  Select cell B7 and enter the following formula:  =(B2-B5)/B4.  Excel computes the value of 2.60. 
6.  Upper critical value.  Select cell B8 and enter the following formula:  =NORMSINV(1-B6).  This formula returns the Z value that corresponds to an upper tail area of .01.  Excel computes the value of 2.326342.
7.  Decision.  Select cell B9 and enter the following formula:  =IF(B7<=B9, "Do not reject H0", "Reject H0").  This formula compares the computed Z test statistic with the critical Z value.  If the computed Z value falls at or below the critical value, the null hypothesis is accepted.  If the computed Z value falls above the critical value, the null hypothesis is rejected.  Excel makes the desired decision to Reject H0 at the 1% level of significance.

Hypothesis Test for m (s unknown)--Two-sided t-Test
Follow the steps listed below to perform a two-sided hypothesis test concerning a population mean when the population standard deviation is unknown.  Start with a blank Excel worksheet.
Example 3.  A random sample of 35 retired employees of a large company is selected and the ages of those retirees are collected.  The mean age of the sample is 68.029 years with a standard deviation of 9.92 years.  Test the null hypothesis that the mean age of all retired employees of the company is equal to 70 years (m = 70) at the 10% significance level.
1.  H₀: m = 70      H₁: m ¹ 70    [This test will be a two-sided t-test since s is unknown.]
2.  Enter the labels for the worksheet.  Select cell A1 and enter the label Sample size n.  Select cell A2 and enter the label Sample mean.  Select cell A3 and enter the label s.  Select cell A4 and enter the label Standard error.  Select cell A5 and enter the label Null hypothesis  m = .  Select cell A6 and enter the label a.  Select cell A7 and enter the label df.  Select cell A8 and enter the label t test statistic.  Select cell A9 and enter the label Lower critical value.  Select cell A10 and enter the label Upper critical value.  Select cell A11 and enter the label Decision.
3.  Enter the given information. Select cell B1 and enter 35 (the sample size).  Select B2 and enter 68.029 (the sample mean).  Select B3 and enter 9.92 (the sample standard deviation).  Select cell B5 and enter 70 (null hypothesis mean value).  Select cell B6 and enter .10 (significance level).
4.  Standard error.  Select cell B4 and enter the following formula:  =B3/SQRT(B1).  Excel computes the value of 1.676786.
5.  Degrees of freedom df.  Select cell B7 and enter the following formula:  =B1-1.  Excel computes the value of 34.
5.  t test statistic.  Select cell B8 and enter the following formula:  =(B2-B5)/B4.  Excel computes the value of -1.17546. 
6.  Lower critical value.  Select cell B9 and enter the following formula:  =- (TINV(B6,B7)).  This formula returns the t value that corresponds to a lower tail area of .05.  Excel computes the value of -1.69092.
7.  Upper critical value.  Select cell B10 and enter the following formula:  =TINV(B6,B7).  This formula returns the t value that corresponds to an upper tail area of .05.  Excel computes the value of 1.690923.
8.  Decision.  Select cell B11 and enter the following formula:  =IF(ABS(B8)<=B10, "Do not reject H0", "Reject H0").  This formula compares the computed t test statistic with the critical t values.  If the computed t value falls on or between the two critical values, the null hypothesis is accepted.  If the computed t value falls outside of the two critical values, the null hypothesis is rejected.  Excel makes the desired decision Do not reject H0 at the 10% level of significance.

Hypothesis Test for m (s unknown)--One-sided t-Test
Follow the steps listed below to perform a one-sided hypothesis test concerning a population mean when the population standard deviation is unknown.  Start with a blank Excel worksheet.
Example 4.  For the same population of retirees discussed in Example 3, assume that a different random sample of 30 retirees is selected and the sample mean age is computed to be 70.77 years with a standard deviation of 8.82 years.  Test the null hypothesis that the population mean age of retirees is at least 72 years (m ³ 72) at the 5% significance level.
1.  H₀: m ³ 72      H₁: m < 72    [This test will be a lower-tailed t-test since s is unknown.]
2.  Enter the labels for the worksheet.  Select cell A1 and enter the label Sample size n.  Select cell A2 and enter the label Sample mean.  Select cell A3 and enter the label s.  Select cell A4 and enter the label Standard error.  Select cell A5 and enter the label Null hypothesis  m ³ .  Select cell A6 and enter the label a.  Select cell A7 and enter the label df.  Select cell A8 and enter the label t test statistic.  Select cell A9 and enter the label Lower critical value.  Select cell A10 and enter the label Decision.
3.  Enter the given information. Select cell B1 and enter 30 (the sample size).  Select B2 and enter 70.77 (the sample mean).  Select B3 and enter 8.82 (the sample standard deviation).  Select cell B5 and enter 72 (null hypothesis mean value).  Select cell B6 and enter .05 (significance level).
4.  Standard error.  Select cell B4 and enter the following formula:  =B3/SQRT(B1).  Excel computes the value of 1.610304.
5.  Degrees of freedom df.  Select cell B7 and enter the following formula:  =A1-1.  Excel computes the value of 29.
6.  t test statistic.  Select cell B8 and enter the following formula:  =(B2-B5)/B4.  Excel computes the value of -.76383. 
7.  Lower critical value.  Select cell B9 and enter the following formula:  =- (TINV(2*B6,B7)).  This formula returns the t value that corresponds to a lower tail area of .05.  Excel computes the value of -1.69913.
8.  Decision.  Select cell B10 and enter the following formula:  =IF(B8>=B9, "Do not reject H0", "Reject H0").  This formula compares the computed t test statistic with the critical t value.  If the computed t value falls at or above the critical value, the null hypothesis is accepted.  If the computed t value falls below the critical value, the null hypothesis is rejected.  Excel makes the desired decision Do not reject H0 at the 5% level of significance.

Hypothesis Test for p--One-sided Z-Test
Follow the steps listed below to perform a one-sided hypothesis test concerning a population proportion.  Start with a blank Excel worksheet.
Example 5.  Refer to the population of retirees discussed in the previous two examples.  Suppose that a different random sample of 60 retirees is selected from the company and that 37 of them are under age 65.  Test the null hypothesis that the population proportion of retired employees under age 65 is at most .50 (p £ .50) at the 5% significance level.
1.  H₀: p £ .50      H₁: p > .50    [This test will be an upper-tailed Z-test.]
2.  Enter the labels for the worksheet.  Select cell A1 and enter the label Sample size n.  Select cell A2 and enter the label Successes.  Select cell A3 and enter the label Sample proportion p_S.  Select cell A4 and enter the label Null hypothesis  p £ .  Select cell A5 and enter the label Standard error of p_S.  Select cell A6 and enter the label a.  Select cell A7 and enter the label Z test statistic.  Select cell A8 and enter the label Upper critical value.  Select cell A9 and enter the label Decision.
3.  Enter the given information. Select cell B1 and enter 60 (the sample size).  Select B2 and enter 37 (the number of successes).  Select cell B4 and enter .50 (null hypothesis proportion value).  Select cell B6 and enter .05 (significance level).
4.  Sample proportion.  Select cell B3 and enter the following formula:  =B2/B1.  Excel computes the value of .616667.
5.  Standard error of p_S.  Select cell B5 and enter the following formula:  =SQRT((B4*(1-B4))/B1).  Excel computes the value of .06455.
6.  Z test statistic.  Select cell B7 and enter the following formula:  =(B3-B4)/B5.  Excel computes the value of 1.807392. 
7.  Upper critical value.  Select cell B8 and enter the following formula:  =NORMSINV(1-B6).  This formula returns the Z value that corresponds to an upper tail area of .05.  Excel computes the value of 1.644853.
8.  Decision.  Select cell B9 and enter the following formula:  =IF(B7<=B8, "Do not reject H0", "Reject H0").  This formula compares the computed Z test statistic with the critical Z value.  If the computed Z value falls at or below the critical value, the null hypothesis is accepted.  If the computed Z value falls above the critical value, the null hypothesis is rejected.  Excel makes the desired decision to Reject H0 at the 5% level of significance.