Lab 10: Instructions and Questions for Excel 2003

Inferential Statistics and Hypothesis Testing

NAME______LAB TIME______LAB BLDG______

This lab will carry out hypothesis tests and will examine the conditions which result in rejection of the null hypothesis.

  1. Go to the course webpage and open the lab10data file.
  2. Suppose we are interested in a variable whose mean is supposed to be 100. Thedistribution of this variable is believed to be normal with astandard deviation= 0.8 mm based on previous surveys. We want to detect a shift in the mean in either direction.
  1. The null hypothesis should be:__Ho:______
  1. The alternate hypothesis should be:__Ha:______
  2. To examine the effect of sample size, we will use two sample sizes, n=30 and n = 6. The first sample of 30 observations is given in column B, and the first sample of 6 observations is given in Column K.
  3. Use A45 through A49 for labels as follows: A45: sample size, A46: sample mean, A47: test statistic, z, A48: 1-side P Value, A49:2-side P Value.
  4. In B45 enter =30 for sample size.
  5. In B46 use the AVERAGE function to calculate the sample mean for B10:B39.
  6. In B47 calculate the test statistic, z, using the formula: z = (sample mean – hypothesized mean) / (0.8/ sqrt of sample size). Enter = (B46-100) / (0.8/SQRT(B45))
  7. In B48 use the NORMSDISTfunction to calculate the 1-side P Value. Enter = NORMSDIST(B47).
  8. In B49 use the NORMSDIST function to calculate the 2-side P Value. Enter = 2*NORMSDIST(B47)

CALCULATE 7 NEW COLUMNS OF HYPOTHETICAL SAMPLES OF SIZE = 30:

  1. In Column C generate a new sample of n= 30 items using excel to deduct 0.1 from each data value in the column B. ie, in C10 use the formula: =B10-0.1. Copy this formula down to generate 30 new values.
  2. Repeat the above instructions to generate 6 new data samples in columns D through I except deduct 0.05 instead of .1 from the column on the left.
  3. Copy the formulas in B45:B49 across to column I.
  4. The sample means in line 46 should be 100, 99.9, 99.85, 99.80, 99.75, 99.70, 99.65, 99.60, and the corresponding test statistics and P Values should be descending also.

CALCULATE 8 NEW COLUMNS OF HYPOTHETICAL SAMPLES OF SIZE =6

  1. In Column K a sample of n=6 items is given. In Column L generate a new sample of size n=6 using excel to deduct 0.15 from each data value in column K.
  2. Repeat the above instructions to generate 7 new samples in columns M through S except deduct 0.10 instead of 0.15 from the column on the left.
  3. In K45 enter =6. Copy K45 across to columns L through S. Copy the formulas in I46:I49 over to columns K through S.
  1. The sample means from K46 through S46 should be 100, 99.85, 99.75, 99.65…..99.15.
  2. There should be two graphs between lines 51 and 102 showing the sample means and the associated P Values which you just generated. The second plot just expands the y axis of the first plot to aid in interpretation.

INTERPRETATION OF SAMPLES 1-8 WHEN SAMPLE SIZE = 30

  1. The first sample of n=30 in Column B has a mean = 100, exactly as expected in the null hypothesis. Samples 2 - 8 in Columns C through I have descending means. Note that the P Values are also descending from Column B through I.
  2. Remember that you can reject the null hypothesis only when the P Value is equal to or less than the significance level, α.
  3. Sample 1 in Column B supports which hypothesis? Null______Alternate______
  4. As the sample mean decreases, going from Column B toward Column I the null hypothesis becomes: Easier to believe______Harder to believe______
  5. If α = .10, and with a two-side Ha, rejection of the null hypothesis would begin with what sample mean? ______
  6. If α = .01, and with a two-side Ha, rejection of the null hypothesis would begin with what sample mean? ______
  7. If α = .10, and with a one-side Ha, rejection of the null hypothesis would begin with what sample mean? ______
  8. If α = .01, and with a one-side Ha, rejection of the null hypothesis would begin with what sample mean? ______
  9. In general, is it easier (more likely) or harder (less likely) to reject a 2-side hypothesis than it is to reject a one-side hypothesis? ______

COMPARISON OF SAMPLE SIZE N=30 WITH N=6

  1. Comparing sample size n=30 with n=6, for a given value of the sample mean, and for a given set of hypotheses, the P Value is always lower for which sample size? ______
  2. For detecting small shifts in the population mean, it would be better to use a large sample size or a small sample size? ______

COMPARISON OF α = .10 VS α = .01

  1. In general, which value of α would result in rejection of Ho more often, α = .10 or α = .01? ______
  2. Which value of α would be regarded as the most rigorous criteria (most difficult to achieve) for rejection of Ho, α = .10 or α = .01? ______
  3. The P Value represents the probability that the particular value of the sample mean (or a value more extreme) could occur by chance alone WHEN THE NULL HYPOTHESIS IS ACTUALLY TRUE. Knowing this, which value of α would result is more cases where Ho is rejected when Ho is actually true, ie: the mean is actually = 100, α = .10 or α = .01? ______

Print the spreadsheet including the plots of P Values, A1:S104 using landscape with a page break after line 43, and turn in with your answers to the questions.