Excel 2016 data analysis plus

Statistics

LAB SESSION 3

ANALYZING MEAN

INTRODUCTION: Two indispensable statistical decision-making tools for a single

parameter are

(i) Confidence intervals

(ii) Hypothesis tests to investigate theories about parameters.

KNOWN SIGMA

CONFIDENCE INTERVALS

As an introduction, let’s follow Example 8.4 in your text.

Begin a new worksheet and generate 40 random integers the range 0 to 9 in column A. Use

the Random Number Generation tool of Excel and then use the INT (integer) function to

transform to integers in the range 0 to 9:

Choose: Data > Data Analysis > Random Number Generation > OK

Enter: Number of Variables: 1

Number of Random Numbers: 40

Select: Distribution: Uniform

Enter: Parameters, Between 0 and 10

Output Range: B1 > OK

Enter: in cell A1: =INT(B1)

Click and drag: lower right corner to cell A40

To see the mean, standard deviation and maximum and minimum values for the data set use:

Select: Data > Data Analysis > Descriptive Statistics > OK

Enter input and output range as appropriate, and select Summary Statistics

Find the 90% confidence interval for the mean of these values we generate in column A:

Choose: Data > Data Analysis Plus** > Z-Estimate: Mean > OK

Enter: Input Range: A1:A40

Standard Deviation (SIGMA): 2.87 > OK

Alpha: .10 > OK

So the 90% confidence interval for the mean is to .

** If “Data Analysis Plus” does not show on the File menu:

Save and Close your excel file

Open: http://www.kellerstatistics.com/KellerStatistics/DataAnalysisPlus

Click on: “Download DAPv9” bottom (Mac or Windows)

Save the file > Extract it > open it > double click on Excel file:“Stats_2007-

2013_v9b” > Click on “Enable Macros” > Find “Data Analysis Plus” in “Add-Ins” at

the menu toolbar of present Excel file > Reopen your previous excel file by dragging

inside of present Excel file.

HYPOTHESIS TESTING

A standard final examination in an elementary statistics course is designed to produce a mean

score of 75 and a standard deviation of 12. The hypothesis you will try to verify is: "This

particular statistics class is above average." At the .05 level of significance, test the claim that

the following sample scores reflect an above-average class (assuming sigma = 12):

79 79 78 74 82 89 74 75 78 73 74 84 82 66 84 82 82 71 72 83

Test the hypothesis, "The mean test grade for this class is greater than 75."

Choose: Add-Ins > Data Analysis Plus > Z-Test: Mean > OK

Enter: Input Range: A1:A20 or select cells > OK

Hypothesized mean: 75

Standard Deviation (SIGMA): 12 > OK

Alpha: .05 > OK

Questions:

1. What are the formal null and alternative hypotheses?

2. What is the value of the test statistic, and what is your decision? Is the mean of this class

above “average”?

UNKNOWN SIGMA

THE CONFIDENCE INTERVAL

To generate a confidence interval using the t-statistic we use Inference about a Mean

command, specifying the level of confidence and the column of data for which the estimation

is being made.

Consider the data presented in exercise 9.31[EX09-031] of your text. Open the data file.

Before we complete a 95% confidence interval estimate for the mean length of lunch breaks

at Giant Mart, we check the normal probability plot and boxplot to verify the normality

assumption.

Excel uses a test for normality (Chi-Squared Test of Normality), not the probability plot.

Choose: Add-Ins > Data Analysis Plus > Chi-Squared Test of Normality > OK

Enter: Input Range: select cells

Select: Labels (if column heading was used) > OK

** If p-value greater the .05, given distribution is approximately normal.

To complete a 95% confidence interval estimate for the mean length of lunch breaks at Giant

Mart complete the following steps:

Choose: Add-Ins > Data Analysis Plus > t-Estimate: Mean > OK

Enter: Input Range: A1:A22

Enter: Alpha: .05 > OK

With 95% confidence we estimate the mean length of lunch breaks at Giant Mart to be

between and minutes.

THE T TEST

Using text exercise 9.29 [EX02-177] as the basis of our discussion, open the data file.

Suppose we have been asked to determine whether this accelerator has decreased the drying

time by significantly more than 4% at the 0.01 level.

To perform the test, use the following commands:

Choose: Add-Ins > Data Analysis Plus > t-Test Mean > OK

Enter: Input Range: A2:A9 > OK

Hypothesized mean: 4

Alpha: 0.01 > OK

Question:

3. What are the formal null and alternative hypotheses?

4. Is there sufficient evidence to show that this accelerator has decreased the drying time

significantly more than 4% at the .01 level?

Assignment

• Answer the 3 questions inside the workshop plus:

• EXERCISE 8.41

• EXERCISE 8.125