Doing Statistics with Excel 5.0 for Windows
An Introductory Course Supplement
for Explorations in Data Analysis
Doing Statistics With Excel 5.0
Marilyn K. Pelosi, Theresa M. Sandifer, and Jerzy J. Letkowski

Wiley John Wiley & Sons, Inc.
Copyright © 1996 by John Wiley & Sons. Inc.
All rights reserved

Your comments and suggestions for improvement will be greatly appreciated. Please drop us your note at jletkows@wnec.edu. Thank you.

Visit # since September 7, 1996.
Last Updated August 23, 1997.


Table of Contents

Preface ix
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 3

Chapter 2 The Basics of Windows 5
Section 2.1 Overview 5
Section 2.2 The Windows Desktop 5
Section 2.3 Parts of a Window 7
Section 2.4 Other Windows Features 8
Section 2.5 The File Manager 11
Section 2.6 Exiting File Manager and Windows 15

Chapter 3 The Basics in Excel for Windows, Version 5.0 17
Section 3.1 Overview 17
Section 3.2 Launching Excel 17
Section 3.3 Statistical Data in Excel 20
Section 3.4 Using Named References in Excel 25
Section 3.5 Saving and Closing Your Work in Excel 27
Section 3.6 Opening an Excel Worksheet 29
Section 3.7 Printing Your Work in Excel 32
Section 3.8 Exiting Excel 37
Section 3.9 Excel for Windows 95 37

Chapter 4 Displaying Qualitative Data 45
Section 4.1 Overview 45
Section 4.2 Problem Statement 45
Section 4.3 Characteristics of the Data Set 46
Section 4.4 Overview of Graphs in Excel 47
Section 4.5 How to Create Column Charts in Excel 48
Section 4.6 Clustered and Stacked Column Charts 68
Section 4.7 How to Create a Pie Chart in Excel 81
Section 4.8 Consolidating Data 86
Section 4.9 Pareto Diagrams 90
Section 4.10 Looking at the Data for Different Time Periods 92
Section 4.11 Investigative Exercises 93

Chapter 5 Displaying Quantitative Data 105
Section 5.1 Overview 105
Section 5.2 Problem Statement 105
Section 5.3 Characteristics of the Data Set 107
Section 5.4 How to Create a Histogram in Excel 109
Section 5.5 Creating Histograms for Subsets of a Variable 120
Section 5.6 Investigative Exercises 127

Chapter 6 Numerical Descriptors 145
Section 6.1 Overview 145
Section 6.2 Problem statement 145
Section 6.3 Generating Descriptive Statistics for an entire column of data 146
Section 6.4 Generating Descriptive Statistics for Grouped Data 154
Section 6.5 Descriptive Statistics Tool Versus Excel Functions 164
Section 6.6 Creating a Boxplot and Detecting Outliers 165
Section 6.7 Investigative Exercises 169

Chapter 7 A Study of The Binomial Distribution 179
Section 7.1 Overview 179
Section 7.2 Problem Statement 180
Section 7.3 Characteristics of the Data Set 180
Section 7.4 Detecting a Binomial Variable 183
Section 7.5 The Binomial Distribution in Excel 185
Section 7.6 Plotting the Binomial Distribution in Excel 192
Section 7.7 Estimating the Probability of Success, p 195
Section 7.8 The Beginnings of Hypothesis Testing 200
Section 7.9 Investigative Exercises 202

Chapter 8 A Study of the Normal Distribution 213
Section 8.1 Overview 213
Section 8.2 Problem Statement 213
Section 8.3 Characteristics of the Data Set 215
Section 8.4 Normal Distribution in Excel 216
Section 8.5 Plotting the Normal Distribution 219
Section 8.6 Changing the Normal Distribution 221
Section 8.7 Empirical Rules for Testing Normality 223
Section 8.8 Investigative Exercises 228

Chapter 9 A Study of the Central Limit Theorem 233
Section 9.1 Overview 233
Section 9.2 Problem Statement 233
Section 9.3 Characteristics of the Data Set 234
Section 9.4 Finding Sample Means 235
Section 9.5 The Central Limit Theorem 241
Section 9.6 Control Charts in Excel 241
Section 9.7 Investigative Exercises 248

Chapter 10 Hypothesis Tests - One Population 255
Section 10.1 Overview 255
Section 10.2 Problem Statement 255
Section 10.3 Characteristics of the Data Set 256
Section 10.4 Testing a Hypothesis 257
Section 10.5 Hypothesis Testing in Excel 258
Section 10.6 Interpreting the Test Output 260
Section 10.7 After the Test - Now What? 261
Section 10.8 Other Considerations 263
Section 10.9 Investigative Exercises 265

Chapter 11 Hypothesis Tests - Two Populations 269
Section 11.1 Overview 269
Section 11.2 Problem Statement 269
Section 11.3 Characteristics of the Data Set 270
Section 11.4 Hypothesis Testing - Two Populations 271
Section 11.5 Investigative Exercises 279

Chapter 12 Testing Proportions 287
Section 12.1 Overview 287
Section 12.2 Problem Statement 287
Section 12.3 Characteristics of the Data Set 288
Section 12.4 Creating Cross Tabulation Tables in Excel 290
Section 12.5 Hypothesis Test on a Single Proportion Using Excel 296
Section 12.6 Comparing Two Proportions: Hypothesized Difference of Zero 304
Section 12.7 Investigative Exercises 308

Chapter 13 Chi-Square Test for Independence 317
Section 13.1 Overview 317
Section 13.2 Problem Statement 317
Section 13.3 Chi-Square Test for Independence 318
Section 13.4 Investigative Exercises 323

Chapter 14 Linear Regression and Correlation 331
Section 14.1 Overview 331
Section 14.2 Problem Statement 331
Section 14.3 Characteristics of the Data Set 332
Section 14.4 Regression and Correlation 333
Section 14.5 Looking for Relationships 334
Section 14.6 Measuring Relationships 337
Section 14.7 Finding the Regression Model 338
Section 14.8 Testing Significance of the Model 339
Section 14.9 Examining the Fit of the Model 341
Section 14.10 Interpolation and Extrapolation 344
Section 14.11 Investigative Exercises 348

Chapter 15 Pulling It All Together 357
Section 15.1 Overview 357
Section 15.2 Problem Statement 357
Section 15.3 Characteristics of the Data Set 359
Section 15.4 Investigative Exercises 360
Get Back to the Top.


Preface

To The Student:
Data! Data! Data! It is everywhere and growing at an incredible rate. As our computer technology allows us to capture, store and retrieve more and more data, we must be able to transform that data into information. The information can then be used to make business decisions at all levels ranging from day-to-day decisions to short-term decisions to long-term strategic planning decisions. We can no longer compete internationally by making decisions based solely on experience and intuition. We must be able to see the information in the data.

This workbook is designed to help you learn how to see the valuable information in data. Each chapter is designed to allow you to explore a single statistical concept by investigating a data set. The situations which led to the collection of the data sets are based on actual consulting experiences of the authors.

It becomes quickly obvious that you can not investigate a data set without the assistance of a computer package. The computer package which is used in this workbook is known as Microsoft Excel for Windows Version 5.0. The commands necessary to use this package are explained in each chapter. If you are using Microsoft Excel for Windows 95 Version 7.0 there may be some minor differences between the screens you see and the ones shown in the workbook.

To The Instructor:
This workbook is designed to be used by students enrolled in a basic course in Statistics. It is built around the premise that students must Do Statistics in order to truly understand the statistical concepts. Thus, each chapter reinforces a topic from a standard statistics course. It is expected that the student can work independently or as a member of a team of students.

The workbook is self-contained in that all of the instructions needed to use the software are explained in the appropriate chapter. Space is provided for the students to write the answers in the workbook, if so desired.

Each chapter contains two types of exercises: (1) exercises to help the student learn the command structure of Microsoft Excel for Windows Version 5.0 and (2) exercises to help the students see the statistical concepts in action. The chapter is motivated by an actual problem drawn from the authors consulting experiences. There is one dataset for each chapter which is tied to the motivating problem. Each of the datasets is on the disk which accompanies this workbook. The dataset is then explained and the remainder of each chapter is dedicated to two major goals:

(1) to provide the student with detailed command instructions for using Microsoft Excel for Windows Version 5.0 to accomplish the desired statistical analysis;
and
(2) to provide the student with a set of structured exercises designed to direct the students statistical thinking in order to reinforce the statistical tool of the chapter.
Get Back to the Top.


Software Update

Each chapter's Excel-workbook file contains data and macros applicable to the chapter. Those macros can only be used with data stored in the same workbook. Recently, a new directory and workbook file have been included to the data disk: the MACRO directory with the workbook MACDOIT.XLS. This workbook combines the all-chapters macros which are included in the chapter related workbooks. It contains the following macros:

Macro Name

Input/Output Requirements

BoxPlot
Input: A single column data range.
Output: A box plot placed on a new sheet.
Outliers
Input: A single column data range.
Output: A report showing outliers (if any).
GetNormalProb
Input: None
Output: Data cells and formulas for calculating normal probabilities and percentiles.
ZTest
Input: None
Output: Data cells and formulas for doing the Z test for the mean.
tTest
Input: None
Output: Data cells and formulas for doing the t test for the mean.
SinglePropTest
Input: None
Output: Data cells and formulas for doing the test for a single proportion.
TwoPropTest
Input: None
Output: Data cells and formulas for doing the test for two proportion.
PredictionIntervalLimits
Input: Two columns of independent and dependent data to be stored on an empty sheet starting from cell A1. No labels (names) are allowed.
Output: Data cells and formulas for generating predictions, residuals, and prediction intervals for linear regression.

In order to use the macros with a new data set, open the MACDOIT.XLS and the workbook containing the data set. Then use the Tools | Macro... command to run one of the macros listed in the Macro Name/Reference list box.

The data files and macros are modified from time to time. Based on the student feedback, the macros are improved and expanded. To obtain the latest version of the data files and the macros, download the DOSTATS.EXE file. This is a self-extracting file (program). When executed in DOS or in File Manager, the program will create a directory structure and files similar to the one accompanying the book. The MACRO directory and the MACDOIT.XLS workbook have been added to the original chapter data files. In order to properly extract directories and inflate the compressed files, download the DOSTATS.EXE program to a new directory (for example to STATS), and execute it with the -d flag. In DOS, switch to the directory containing the DOSTATS.EXE program (e.g. STATS), type DOSTATS -d and press Enter. In File Manger, select the directory, invoke the File | Run... command, type DOSTATS -d, and press Enter or click the OK button. If you skip the -d flag, the program will inflate the chapter data files, but it will not extract the directories.

If you already have the chapter data files and want to obtain only the new macro file, you will need to download the MACDOIT.EXE file/program. This is a self-inflating executable file, containing the MACDOIT.XLS workbook only. In DOS, switch to the directory containing the MACDOIT.EXE program, type MACDOIT and press Enter. In File Manger, select the directory and double-click the MACDOIT.EXE file (or click the file and press Enter).

Download the File(s):

Click DOSTATS.EXE to download all chapter files with the new macro file.

Click MACDOIT.EXE to download just the macro file.

Note:
If you have not yet created a separate directory for the data/macro files, switch to File Manger now, and use the File | Create Directory command to create the directory (e.g. STATS). Then proceed with downloading one of the above files/programs to this directory.
Get Back to the Top.


Errata

Page 82, the top paragraph:
Is: Sheet1
Should be: Sheet2

Page 92, the bottom paragraph:
Is: E12:E146
Should be: E12:E133

Page 123, Figure 5.1:
Missing: Step (mouse click) number 4, at the Define option.

Page 148, the middle paragraph (right below Figure 6.4):
Is: To adjust the with
Should be: To adjust the width

Page 159, the first paragraph of Section 6.4.4:
Is: the CarryM1 and CarryM1
Should be
: the CarryM1 and CarryM2

Page 239, the middle paragraph (below Figure 9.8):
Is: On the WAvgWStats, next to Bin, select range E2:E17.
Should be: On the WAvgWStats sheet, select range F2:F17.

Is: Type =Frequency(WAvgW!B2:B53,D2:D17).
Should be: Type =Frequency(WAvgW!C2:C261,D2:D17).

Page 297, the first paragraph of Section 12.5.1:
Is: condition of the greens.
Should be: condition of the golf course.

Page 300, the lines 3,4,5 of the Selection column:
Is: D2, D3, D4
Should be: D4, D5, D6

Page 310, Problem 4:
Is:

4. Examine the proportion of respondents who rated the Condition of the greens "good". Conduct the following hypothesis test:

Ho: p >= .50
HA: p < .50

What is your conclusion about the true proportion of members who feel the Condition of the greens is "good"?

Should be:

4. Examine the proportion of respondents who rated the condition of the Greens "good". Conduct the following hypothesis test:

Ho: p >= .50
HA: p < .50

What is your conclusion about the true proportion of members who feel the condition of the Greens is "good"?


Note:
You may wish to download a Word 6.0 version of the above Errata. Just click ERRATA.EXE file/program. This is a self-extracting executable file, containing the ERRATA.DOC document. After successful downloading, in DOS, switch to the directory containing the ERRATA.EXE program, type ERRATA, and press Enter. In File Manger, select the directory and double-click the ERRATA.EXE file (or click the file and press Enter).


Get Back to the Top.