Table of Contents

Procedures

ix

Preface

xiii

Chapter 1 An Introduction to the Workbook

1

Section 1.1

Overview

1

Section 1.2

Objectives of the Workbook

1

Section 1.3

Description of the Data Sets

2

Section 1.4

Organization of the Workbook

2

Section 1.5

How to Use the Workbook

4

Section 1.6

Web Pages

4

Chapter 2 The Basics of Windows

5

Section 2.1

Overview

5

Section 2.2

Starting Windows 95

5

Section 2.3

The Windows 95 Desktop

5

Section 2.4

Using the Mouse

7

Section 2.5

Using the Keyboard

9

Section 2.6

Windows Structure and Behavior

14

Section 2.7

My Computer

22

Section 2.8

Windows Explorer

24

Section 2.9

Exiting Explorer and Windows

29

Chapter 3 The Basics in Microsoft Excel 97

31

Section 3.1

Overview

31

Section 3.2

Launching Excel

31

Section 3.3

Statistical Data in Excel

36

Section 3.4

Saving a Workbook

39

Section 3.5

Using Named References in Excel

41

Section 3.6

Printing Your Work

43

Section 3.7

Exiting Excel

47

Chapter 4 Displaying Qualitative Data

49

Section 4.1

Overview

49

Section 4.2

Problem Statement

49

Section 4.3

Characteristics of the Data Set

50

Section 4.4

Overview of Graphs in Excel

51

Section 4.5

How to Create Column Charts in Excel

51

Section 4.6

Clustered and Stacked Column Charts

66

Section 4.7

How to Create a Pie Chart in Excel

76

Section 4.8

Consolidating Data

83

Section 4.9

Pareto Diagrams

87

Section 4.10

Looking at the Data for Different Time Periods

89

Section 4.11

Investigative Exercises

90

Chapter 5 Displaying Quantitative Data

95

Section 5.1

Overview

95

Section 5.2

Problem Statement

95

Section 5.3

Characteristics of the Data Set

97

Section 5.4

How to Create a Histogram in Excel

99

Section 5.5

Creating Histograms for Subsets of a Variable

117

Section 5.6

Investigative Exercises

129

Chapter 6 Numerical Descriptors

135

Section 6.1

Overview

135

Section 6.2

Problem Statement

135

Section 6.3

Column of data

136

Section 6.4

Generating Descriptive Statistics for Grouped Data

146

Section 6.5

Descriptive Statistics Tool Versus Excel Functions

156

Section 6.6

Creating a Boxplot and Detecting Outliers

157

Section 6.7

Investigative Exercises

162

Chapter 7 A Study of The Binomial Distribution

169

Section 7.1

Overview

169

Section 7.2

Problem Statement

170

Section 7.3

Characteristics of the Data Set

170

Section 7.4

Detecting a Binomial Variable

172

Section 7.5

The Binomial Distribution in Excel

174

Section 7.6

Plotting the Binomial Distribution in Excel

179

Section 7.7

Estimating the Probability of Success, p

182

Section 7.8

The Beginnings of Hypothesis Testing

188

Section 7.9

Investigative Exercises

191

Chapter 8 A Study of the Normal Distribution

193

Section 8.1

Overview

193

Section 8.2

Problem Statement

193

Section 8.3

Characteristics of the Data Set

195

Section 8.4

Normal Distribution in Excel

196

Section 8.5

Plotting the Normal Distribution

203

Section 8.6

Changing the Normal Distribution

205

Section 8.7

Empirical Rules for Testing Normality

208

Section 8.8

Investigative Exercises

215

Chapter 9 A Study of the Central Limit Theorem

219

Section 9.1

Overview

219

Section 9.2

Problem Statement

219

Section 9.3

Characteristics of the Data Set

220

Section 9.4

Finding Sample Means

221

Section 9.5

The Central Limit Theorem

227

Section 9.6

Control Charts in Excel

227

Section 9.7

Investigative Exercises

237

Chapter 10 Hypothesis Tests - One Population

241

Section 10.1

Overview

241

Section 10.2

Problem Statement

241

Section 10.3

Characteristics of the Data Set

242

Section 10.4

Testing a Hypothesis

243

Section 10.5

Hypothesis Testing in Excel

244

Section 10.6

Interpreting the Test Output

245

Section 10.7

After the Test - Now What?

247

Section 10.8

Other Considerations

250

Section 10.9

Investigative Exercises

251

Chapter 11 Hypothesis Tests - Two Populations

255

Section 11.1

Overview

255

Section 11.2

Problem Statement

255

Section 11.3

Characteristics of the Data Set

256

Section 11.4

Hypothesis Testing - Two Populations

257

Section 11.5

Investigative Exercises

266

Chapter 12 Testing Proportions

269

Section 12.1

Overview

269

Section 12.2

Problem Statement

269

Section 12.3

Characteristics of the Data Set

270

Section 12.4

Creating Cross Tabulation Tables in Excel

272

Section 12.5

Hypothesis Test on a Single Proportion Using Excel

279

Section 12.6

Comparing Two Proportions: Hypothesized Difference of Zero

286

Section 12.7

Investigative Exercises

292

Chapter 13 Chi-Square Test for Independence

299

Section 13.1

Overview

299

Section 13.2

Problem Statement

299

Section 13.3

Chi-Square Test for Independence

299

Section 13.4

Investigative Exercises

306

Chapter 14 Linear Regression and Correlation

309

Section 14.1

Overview

309

Section 14.2

Problem Statement

309

Section 14.3

Characteristics of the Data Set

310

Section 14.4

Regression and Correlation

311

Section 14.5

Looking for Relationships

312

Section 14.6

Measuring Relationships

317

Section 14.7

Finding the Regression Model

319

Section 14.8

Testing Significance of the Model

320

Section 14.9

Examining the Fit of the Model

323

Section 14.10

Interpolation and Extrapolation

326

Section 14.11

Investigative Exercises

331

Chapter 15 Pulling It All Together

335

Section 15.1

Overview

335

Section 15.2

Problem Statement

335

Section 15.3

Characteristics of the Data Set

337

Section 15.4

Investigative Exercises

338

 Procedures

Chapter 2

2.1

Setting the MouseKeys Feature

11

2.2

Duplicating a Floppy Disk

23

2.3

Opening the Readme.txt File in Explorer

25

2.4

Copying Data Files to a Hard Drive

27

Chapter 3

3.1

Opening an Excel Workbookfrom the Open Office Document Dialog Box

33

3.2

Closing a Workbook Window

35

3.3

Entering Data into a Worksheet

36

3.4

Saving a Workbook File

40

3.5

Creating Column-range Names

41

3.6

Using the Count( ) and Sum( ) Functions with Named References

42

3.7

Printing a Sheet

44

Chapter 4

4.1

Generating a Column Chart

53

4.2

Formatting the Category (X) Axis

55

4.3

Clearing the Plot Area Background

57

4.4

Reformatting the Chart Title

58

4.5

Changing the Font Size of the Data Labels

59

4.6

Changing the Font Size of the Value Axis Labels

60

4.7

Opening a Toolbar

60

4.8

Embedding a Text Box

61

4.9

Drawing an Arrow

64

4.10

Preparing Data for the Clustered Chart

68

4.11

Editing a Cell

72

4.12

Generating a Clustered Column Chart

72

4.13

Switching to a Stacked Column Chart Type

74

4.14

Preparing Data for the Pie Chart

77

4.15

Creating a Pie Chart

79

4.16

Moving and Enlarging an Embedded Chart

81

4.17

Changing the Size the Pie

82

4.18

Generating Compliant Subtotals

84

4.19

Creating a Column Chart Based on Subtotal Report

87

4.20

Sorting the Subtotals by Number

87

Chapter 5

5.1

Naming All Variables of the Data Set Ch05Dat

100

5.2

Generating a Histogram Using the Data Analysis ToolPak

101

5.3

Running the GetInfoForBinSetup Macro

105

5.4

Generating Class Interval Limits (Bins)

106

5.5

Naming a Range Using the Name Box

107

5.6

Generating a Frequency Distribution Using the Frequency Function

108

5.7

Generating Relative and Cumulative Frequency Distribution

111

5.8

Creating a Chart Based on Non-adjacent Data Source Ranges

115

5.9

Extracting M1 Carry Values from the Data Set

118

5.10

Copying Data Between Sheets Using Keyboard Commands

121

5.11

A Few Text Editing Tricks in Excel

122

5.12

Entering a Series of Class Interval Limits for the M1 and M2 Carry Subsets

124

5.13

Generating Frequency Distributions for CarryM1 and CarryM2

125

5.14

Selecting No-adjacent Ranges for Chart Input

128

Chapter 6

6.1

Selecting a Named Range from the Name Box

136

6.2

Generating Basic Summary Measures of Carry Using the Data Analysis Tools

137

6.3

Re-generating Basic Summary Measures of Carry Using the Data Analysis Tools

140

6.4

Computing Summary Measures for M1 and M2 Subsets of Carry

147

6.5

Extracting Subsets of the Data Set Using the Copy, Sort, and Delete Commands

150

6.6

Generating the Average and Standard Deviation from a Sorted List

153

Chapter 7

7.1

Generating Binomial Probabilities

175

7.2

Plotting the Binomial Probability Distribution, Using an XY Chart

179

7.3

Using the CountIf( ) Function to Determine Proportions

183

7.4

Generating a Cross Tabulation Report for Variables Problem and Size

185

7.5

Generating the Binomial Probability Model

189

Chapter 8

8.1

Calculating the Left-Interval Normal Probability

197

8.2

Calculating the Right-Interval Normal Probability

198

8.3

Calculating the Mid-Interval Normal Probability

199

8.4

Calculating a Normal Percentile

200

8.5

Using Goal Seek to Compute a Percentile

201

8.6

Using the NormalProbability Macro

207

8.7

Generating MDStrength Summary Measures

209

8.8

Generating a Frequency Histogram Based on a Custom Bin Range

211

Chapter 9

9.1

Generating sample means for subsets of the variable Weight

221

9.2

Generating the Frequency Distributions of MeanWeight and Weight

225

9.3

Generating a Scatter Plot of MeanWeight

229

9.4

Inserting the 3-Sigma Control Limits

231

9.5

Generating the Specification-Based Control Limits for the Variable Weight

233

Chapter 10

10.1

Providing Input for the Z-test Model

245

Chapter 11

11.1

Doing a t-Test for Two Samples with Unequal Variances

261

Chapter 12

12.1

Generating Cross-Tabulated Proportions

273

12.2

Modifying Existing Pivot Table Report

276

12.3

Hiding Data in A Pivot Table

281

12.4

Generating a Single Proportion Test

282

12.5

Calculating Frequencies Using the DCount( ) Function

288

12.6

Generating a Test for Two Proportions

290

Chapter 13

13.1

Replacing Empty Cells of a Pivot Table with Zeros

302

13.2

Calculating the Expected Frequencies for Condition by Age Responses

303

13.3

Doing the Chi-square Test for Independence

304

 Chapter 14

14.1

Generating an XY Scatter Plot for Waste vs. Speed

312

14.2

Modifying an XY-Scatter Plot

315

14.3

Calculating Covariance and Coefficient of Correlation

317

14.4

Calculating Coefficients of a Regression Line

319

14.5

Performing the Significance of Beta₁ Test

321

14.6

Adding the Tend Line to the Scatter Plot

324

14.7

Generating Prediction Intervals

327