Table Of Content

Data Analysis with Microsoft® Excel Updated for Offi ce 2007® Kenneth N. Berk Illinois State University Patrick Carey Carey Associates, Inc. Australia • Brazil • Japan • Korea • Mexico • Singapore • Spain • United Kingdom • United States Data Analysis with Microsoft® Excel: © 2010, 2004 Brooks/Cole, Cengage Learning Updated for Oﬃ ce 2007®, Third Edition ALL RIGHTS RESERVED. No part of this work covered by the copyright Berk, Carey herein may be reproduced, transmitted, stored, or used in any form or by Publisher: Richard Stratton any means graphic, electronic, or mechanical, including but not limited to photocopying, recording, scanning, digitizing, taping, Web distribution, Senior Sponsoring Editor: Molly Taylor information networks, or information storage and retrieval systems, except Associate Editor: Daniel Seibert as permitted under Section 107 or 108 of the 1976 United States Copyright Editorial Assistant: Shaylin Walsh Act, without the prior written permission of the publisher. Associate Media Editor: Catie Ronquillo For product information and technology assistance, contact us at Senior Marketing Manager: Greta Kleinert Cengage Learning Customer & Sales Support, 1-800-354-9706 Marketing Coordinator: Erica O’Connell For permission to use material from this text or product, Marketing Communications Manager: submit all requests online at www.cengage.com/permissions. Further permissions questions can be emailed to Mary Anne Payumo [email protected]. Content Project Manager: Jessica Rasile Art Director: Linda Helcher Library of Congress Control Number: 2009928574 Print Buyer: Linda Hsu ISBN-13: 978-0-495-39178-4 Permissions Editor: Margaret Chamberlain-Gaston ISBN-10: 0-495-39178-6 Production Service/Compositor: PrePress PMG Brooks/Cole Photo Manager: John Hill 20 Channel Center Street Cover Designer: Blue Bungalow Design Boston, MA 02210 USA Cover Image: ©Fotolia Cengage Learning products are represented in Canada by Nelson Education, Ltd. For your course and learning solutions, visit www.cengage.com Purchase any of our products at your local college store or at our preferred online store www.ichapters.com Printed in the United States of America 1 2 3 4 5 6 7 13 12 11 10 09 A b o u t t h e A u t h o r s Kenneth N. Berk Kenneth N. Berk (Ph.D., University of Minnesota) is an emeritus professor of mathematics at Illinois State University and a Fellow of the A merican Statistical Association. Berk was editor of Software Reviews for the American Statistician for six years. He served as chair of the Statistical Computing Section of the American Statistical Association. He has twice co-chaired the annual Symposium on the Interface between Computing Science and Statistics. Patrick Carey Patrick Carey received his M.S. in biostatistics from the University of Wisconsin where he worked as a researcher in the General Clinical Research Center designing and analyzing clinical studies. He coauthored his first textbook with Ken Berk on using Excel as a statistical tool. He and his wife Joan founded Carey Associates, Inc., a software textbook development com- pany. He has since authored or coauthored over 20 academic and trade texts for the software industry. Besides books on data analysis, Carey has w ritten on the Windows® operating system, Web page design, database manage- ment, the Internet, browsers, and presentation graphics software. Patrick, Joan, and their six children live in Wisconsin. I thank my wife Laura for her advice, because here she is the one who knows about publishing books. —Kenneth N. Berk Thanks to my wife, Joan, and my children, John Paul, Thomas, Peter, Michael, Stephen, and Catherine, for their love and support. —Patrick M. Carey iii P r e f a c e Introduction Data Analysis with Microsoft® Excel: Updated for Offi ce 2007® harnesses the power of Excel and transforms it into a tool for learning basic statistical analysis. Students learn statistics in the context of analyzing data. We feel that it is important for students to work with real data, analyzing real-world problems, so that they understand the subtleties and complexities of analysis that make statistics such an integral part of understanding our world. The data set topics range from business examples to physiological studies on NASA astronauts. Because students work with real data, they can appre- ciate that in statistics no answers are completely fi nal and that intuition and creativity are as much a part of data analysis as is plugging numbers into a software package. This text can serve as the core text for an introductory statistics course or as a supplemental text. It also allows nontraditional students outside of the classroom setting to teach themselves how to use Excel to analyze sets of real data so they can make informed business forecasts and decisions. Users of this book need not have any experience with Excel, although previous experience would be helpful. The fi rst three chapters of the book cover basic concepts of mouse and Windows operation, data entry, formulas and functions, charts, and editing and saving workbooks. Chapters 4 through 12 emphasize teaching statistics with Excel as the instrument. Using Excel in a Statistics Course Spreadsheets have become one of the most popular forms of computer software, second only to word processors. Spreadsheet software allows the user to combine data, mathematical formulas, text, and graphics together in a single report or workbook. For this reason, spreadsheets have become indis- pensable tools for business, as they have also become popular in scientifi c research. Excel in particular has won a great deal of acclaim for its ease of use and power. iv As spreadsheets have expanded in power and ease of use, there has been increased interest in using them in the classroom. There are many advan- tages to using Excel in an introductory statistics course. An important ad- vantage is that students, particularly business students, are more likely to be familiar with spreadsheets and are more comfortable working with data entered into a spreadsheet. Since spreadsheet software is very common at colleges and universities, a statistics instructor can teach a course without requiring students to purchase an additional software package. Having identifi ed the strengths of Excel for teaching basic statistics, it would be unfair not to include a few warnings. Spreadsheets are not s tatistics packages, and there are limits to what they can do in replacing a full-featured statistics package. This is why we have included our own downloadable add-in, StatPlus™. It expands some of Excel’s statistical capabilities. (We explain the use of StatPlus where appropriate throughout the text.) Using Excel for anything other than an introductory statistics course would prob- ably not be appropriate due to its limitations. For example, Excel can easily perform balanced two-way analysis of variance but not unbalanced two-way analysis of variance. Spreadsheets are also limited in handling data with missing values. While we recommend Excel for a basic statistics course, we feel it is not appropriate for more advanced analysis. System Information You will need the following hardware and software to use Data Analysis with Microsoft® Excel: Updated for Offi ce 2007®: • A Windows-based PC. • Windows XP or Windows Vista. • Excel 2007. If you are using an earlier edition of Excel, you will have to use an earlier edition of Data Analysis with Microsoft® Excel. • Internet access for downloading the software fi les accompanying the text. The Data Analysis with Microsoft® Excel package includes: • The text, which includes 12 chapters, a reference section for Excel’s statistical functions, Analysis ToolPak commands, StatPlus Add-In commands, and a bibliography. • The companion website at www.cengage.com/statistics/berk contains 92 different data sets from real-life situations plus a summary of what the data set fi les cover, ten interactive Concept Tutorials, and installation fi les for StatPlus—our statistical application. Chapter 1 of the text includes instructions for installing the fi les. • An Instructor’s Manual with solutions to all the exercises in the text is available, password-protected on the companion website, to adopting instructors. Preface v Excel’s Statistical Tools Excel comes with 81 statistical functions and 59 mathematical functions. There are also functions devoted to business and engineering problems. The statistical functions that basic Excel provides include descriptive s tatistics such as means, standard deviations, and rank statistics. There are also cumulative distribution and probability density functions for a variety of distributions, both continuous and discrete. The Analysis ToolPak is an add-in that is included with Excel. If you have not loaded the Analysis ToolPak, you will have to install it from your original Excel installation. The Analysis ToolPak adds the following capabilities to Excel: • Analysis of variance, including one-way, two-way without replication, and two-way balanced with replication • Correlation and covariance matrices • Tables of descriptive statistics • One-parameter exponential smoothing • Histograms with user-defi ned bin values • Moving averages • Random number generation for a variety of distributions • Rank and percentile scores • Multiple linear regression • Random sampling • t tests, including paired and two sample, assuming equal and unequal variances • z tests In this book we make extensive use of the Analysis ToolPak for multiple linear regression problems and analysis of variance. StatPlus™ Since the Analysis ToolPak does not do everything that an introductory statistics course requires, this textbook comes with an additional add-in called the StatPlus™ Add-In that fi lls in some of the gaps left by basic Excel 2007 and the Analysis ToolPak. Additional commands provided by the StatPlus Add-In give users the ability to: • Create random sets of data • Manipulate data columns • Create random samples from large data sets • Generate tables of univariate statistics vi Preface • Create statistical charts including boxplots, histograms, and normal probability plots • Create quality control charts • Perform one-sample and two-sample t tests and z tests • Perform non-parametric analyses • Perform time series analyses, including exponential and seasonal smoothing • Manipulate charts by adding data labels and breaking charts down into categories • Perform non parametric analyses • Create and analyze tabular data A full description of these commands is included in the Appendix’s Reference section and through on-line help available with the application. Concept Tutorials Included with the StatPlus add-in are ten interactive Excel tutorials that pro- vide students a visual and hands-on approach to learning statistical concepts. These tutorials cover: • Boxplots • Probability • Probability distributions • Random samples • Population statistics • The Central Limit Theorem • Confi dence intervals • Hypothesis tests • Exponential smoothing • Linear regression Preface vii Acknowledgments We thank Mac Mendelsohn, Managing Editor at Course Technology, for his support and enthusiasm for the First Edition of this book. For this edition, our thanks to Jessica Rasile, Content Project Manager, Blue Bungalow D esign for the cover design, and Carol A. Loomis, Copyeditor, for their professional attention to all the details of production. Special thanks go to our reviewers, who gave us valuable insights into improving the book in each edition: Aaron S. Liswood, Sierra Nevada C ollege; Abbot L. Packard, State University of West Georgia; Andrew E. Coop, US Air Force Academy; Barry Bombay, J. Sargeant Reynolds Community C ollege; Beth Eschenback, Humboldt State University; Bruce Trumbo, California State University – Hayward; Carl Grafton, Auburn University; Carl R. Williams, University of Memphis; Cheryl Dale, William Carey College; Dang Tran, California State University – Los Angeles; Bruce Marsh, Texas A & M University – Kingsvile; Edward J. Williams, University of Michigan – Dearborn; Eric Zivot, University of Washington; Farrokh Alemi, George Mason University; Faye Teer, James Madison University; Gordon Dahl, University of Rochester; Ian Hardie, University of Maryland; Jack Harris, Hobart and William Smith Colleges; Ames E. Pratt, Cornell University; James Zumbrunnen, Colorado State University; John A. Austin, Jr., Louisiana State University – Shreveport; Kelwyn A. D’Souza, Hampton University; Kevin Griffi n, Eastern Arizona College; Lea Cloninger, University of Illinois at Chicago; Lorrie Hoffman, University of Central Florida; Marion G. Sobol, Southern Methodist University, and Matthew C. Dixon, USAF Academy. We thank Laura Berk, Peter Berk, Robert Beyer, David Booth, Orlyn Edge, Stephen Friedberg, Maria Gillett, Richard Goldstein, Glenn Hart, Lotus Hershberger, Les Montgomery, Joyce Nervades, Diane Warfi eld, and Kemp Wills for their assistance with the data sets in this book. We especially want to thank Dr. Jeff Steagall, who wrote some of the original material for Chapter 12, Quality Control. If we have missed anyone, please forgive the omission. Kenneth N. Berk Patrick M. Carey viii C o n t e n t s Chapter 1 Using the Advanced Filter 59 Using Calculated Values 62 GETTING STARTED WITH EXCEL 1 Importing Data from Text Files 63 Getting Started 2 Importing Data from Databases 68 Special Files for This Book 2 Using Excel’s Database Query Wizard 68 Installing the StatPlus Files 2 Specifying Criteria and Sorting Data 71 Excel and Spreadsheets 4 Exercises 75 Launching Excel 5 Viewing the Excel Window 6 Chapter 3 Running Excel Commands 7 Excel Workbooks and Worksheets 10 WORKING WITH CHARTS 81 Opening a Workbook 10 Introducing Excel Charts 82 Scrolling through a Workbook 11 Introducing Scatter Plots 86 Worksheet Cells 14 Editing a Chart 91 Selecting a Cell 14 Resizing and Moving an Embedded Moving Cells 16 Chart 91 Printing from Excel 18 Moving a Chart to a Chart Sheet 93 Previewing the Print Job 18 Working with Chart and Axis Titles 94 Setting Up the Page 19 Editing the Chart Axes 97 Printing the Page 21 Working with Gridlines and Legends 100 Saving Your Work 22 Editing Plot Symbols 102 Excel Add-Ins 24 Identifying Data Points 105 Loading the StatPlus Add-In 24 Selecting a Data Row 106 Loading the Data Analysis ToolPak 28 Labeling Data Points 107 Unloading an Add-In 30 Formatting Labels 109 Features of StatPlus 30 Creating Bubble Plots 110 Using StatPlus Modules 30 Breaking a Scatter Plot into Hidden Data 31 Categories 117 Linked Formulas 32 Plotting Several Variables 120 Setup Options 32 Exercises 123 Exiting Excel 34 Chapter 4 Chapter 2 DESCRIBING YOUR DATA 128 WORKING WITH DATA 35 Variables and Descriptive Statistics 129 Data Entry 36 Frequency Tables 131 Entering Data from the Keyboard 36 Creating a Frequency Table 132 Entering Data with Autofi ll 37 Using Bins in a Frequency Table 134 Inserting New Data 40 Defi ning Your Own Bin Values 136 Data Formats 41 Working with Histograms 138 Formulas and Functions 45 Creating a Histogram 138 Inserting a Simple Formula 46 Shapes of Distributions 141 Inserting an Excel Function 47 Breaking a Histogram into Categories 143 Cell References 50 Working with Stem and Leaf Plots 146 Range Names 51 Distribution Statistics 151 Sorting Data 54 Percentiles and Quartiles 151 Querying Data 55 Measures of the Center: Means, Medians, Using the AutoFilter 56 and the Mode 154 ix