Tài liệu Statistical Analysis with Excel For Dummies, 2nd Edition pdf

Joseph Schmuller, PhD

Author of Active X: No Experience Required

Learn to:

• Understand mean, median, margin of

error, standard deviation, permutations,

and correlations

• Crunch numbers and interpret statistics

• Use Excel formulas and functions

• Work with probabilities, related

distributions, trends, and more

Statistical Analysis

with Excel ®

2nd Edition

Making Everything Easier!™

Open the book and find:

• Ten statistical and graphical tips

and traps

• The difference between

descriptive and inferential

statistics

• Why graphs are good

• How to measure variations

• What standard scores are and

why they’re used

• When to use two-sample

hypothesis testing

• How to use correlations

• Different ways of working with

probability

Joseph Schmuller, PhD, is a technical architect at Blue Cross-Blue Shield

of Florida. A former member of the American Statistical Association, he

has taught statistics at the undergraduate, honors undergraduate, and

graduate levels, and has been honored with an award for excellence in

teaching.

$29.99 US / $35.99 CN / £19.99 UK

Desktop Applications/Spreadsheets

ISBN 978-0-470-45406-0

Go to dummies.com®

for more!

You too can understand

the statistics of life, even

if you’re math-challenged!

What do you need to calculate? Manufacturing output?

A curve for test scores? Sports stats? You and Excel can

do it, and this non-intimidating guide shows you how.

It demystifies the different types of statistics, how Excel

functions and formulas work, the meaning of means and

medians, how to interpret your figures, and more — in

plain English.

• Getting there — learn how variables, samples, and probability

are used to get the information you want

• Excel tricks — find out what’s built into the program to help you

work with Excel formulas

• Playing with worksheets — get acquainted with the worksheet

functions for each step

• Graphic displays — present your data as pie graphs, bar graphs,

line graphs, or scatter plots

• What’s normal? — understand normal distribution and probability

• Hyping hypotheses — learn to use hypothesis testing with means

and variables

• When regression is progress — discover when and how to use

regression for forecasting

• What are the odds — work with probability, random variables,

and binomial distribution

Statistical Analysis with Excel ®

Schmuller

2nd Edition

spine=1.01”

www.it-ebooks.info

www.it-ebooks.info

Statistical Analysis

with Excel ®

FOR

DUMmIES‰

2ND EDITION

01 454060-ffirs.indd i 4/21/09 7:13:09 PM

www.it-ebooks.info

01 454060-ffirs.indd ii 4/21/09 7:13:10 PM

www.it-ebooks.info

Joseph Schmuller, PhD

Statistical Analysis

with Excel ®

FOR

DUMmIES‰

2ND EDITION

01 454060-ffirs.indd iii 4/21/09 7:13:10 PM

www.it-ebooks.info

Statistical Analysis with Excel® For Dummies,® 2nd Edition

Published by

Wiley Publishing, Inc.

111 River Street

Hoboken, NJ 07030-5774

www.wiley.com

Copyright © 2009 by Wiley Publishing, Inc., Indianapolis, Indiana

Published by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or

by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permit￾ted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written

permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the

Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600.

Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley

& Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://

www.wiley.com/go/permissions.

Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the

Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, Making Everything

Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or

its affi liates in the United States and other countries, and may not be used without written permission.

Excel is a registered trademark of Microsoft Corporation in the United States and/or other countries All

other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated

with any product or vendor mentioned in this book.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO

REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF

THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITH￾OUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE

CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES

CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE

UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR

OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF

A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE

AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZA￾TION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE

OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES

THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT

MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS

WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND

WHEN IT IS READ.

For general information on our other products and services, please contact our Customer Care

Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002.

For technical support, please visit www.wiley.com/techsupport.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may

not be available in electronic books.

Library of Congress Control Number: 2009926356

ISBN: 978-0-470-45406-0

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

01 454060-ffirs.indd iv 4/21/09 7:13:11 PM

www.it-ebooks.info

About the Author

Joseph Schmuller is a veteran of over 25 years in Information Technology.

He is the author of several books on computing, including the three editions

of Teach Yourself UML in 24 Hours (SAMS), and the fi rst edition of Statistical

Analysis with Excel For Dummies. He has written numerous articles on

advanced technology. From 1991 through 1997, he was Editor-in-Chief of

PC AI magazine.

He is a former member of the American Statistical Association, and he has

taught statistics at the undergraduate and graduate levels. He holds a B.S.

from Brooklyn College, an M.A. from the University of Missouri-Kansas City,

and a Ph.D. from the University of Wisconsin, all in psychology. He and his

family live in Jacksonville, Florida, where he is an Adjunct Professor at the

University of North Florida.

01 454060-ffirs.indd v 4/21/09 7:13:11 PM

www.it-ebooks.info

01 454060-ffirs.indd vi 4/21/09 7:13:11 PM

www.it-ebooks.info

Dedication

In loving memory of Jesse Edward Sprague, my best friend in the whole

world — a man who never met a stranger.

“Friends have all things in common” —Plato

Author’s Acknowledgments

One thing I have to tell you about writing a For Dummies book — it’s an

incredible amount of fun. You get to air out your ideas in a friendly, conver￾sational way, and you get a chance to throw in some humor, too. To write a

second edition is almost more fun than one writer should be allowed to have.

I worked again with a terrifi c team. Acquisitions Editor Stephanie McComb

and Project Editor Beth Taylor of Wiley Publishing have been encouraging,

cooperative, and patient. Technical Editor Namir Shammas helped make this

book as technically bulletproof as possible. Any errors that remain are under

the sole proprietorship of the author. My deepest thanks to Stephanie and

Beth. My thanks to Waterside Productions for representing me in this effort.

Again I thank mentors in college and graduate school who helped shape my

statistical knowledge: Mitch Grossberg (Brooklyn College); Mort Goldman,

Al Hillix, Larry Simkins, and Jerry Sheridan (University of Missouri-Kansas

City); and Cliff Gillman and John Theios (University of Wisconsin-Madison).

A long time ago at the University of Missouri-Kansas City, Mort Goldman

exempted me from a graduate statistics fi nal on one condition — that I learn

the last course topic, Analysis of Covariance, on my own. I hope he’s happy

with Appendix B.

I thank my mother and my brother David for their love and support and for

always being there for me, and Kathryn for so much more than I can say.

Finally, a special note of thanks to my friend Brad, who suggested this whole

thing in the fi rst place!

01 454060-ffirs.indd vii 4/21/09 7:13:11 PM

www.it-ebooks.info

Publisher’s Acknowledgments

We’re proud of this book; please send us your comments through our online registration form located

at http://dummies.custhelp.com. For other comments, please contact our Customer Care

Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002.

Some of the people who helped bring this book to market include the following:

Acquisitions, Editorial, and

Media Development

Project Editor: Beth Taylor

(Previous Edition: Sarah Hellert)

Senior Acquisitions Editor: Stephanie McComb

Copy Editor: Beth Taylor

Technical Editor: Namir Shammas

Editorial Manager: Cricket Krengel

Editorial Assistant: Laura Sinise

Cartoons: Rich Tennant (www.the5thwave.com)

Composition Services

Project Coordinator: Kristie Rees

Layout and Graphics: Carrie A. Cesavice,

Shawn Frazier, Melissa K. Jester

Proofreaders: Melissa Cossell,

Bonnie Mikkelson,

Indexer: Steve Rath

Publishing and Editorial for Technology Dummies

Richard Swadley, Vice President and Executive Group Publisher

Barry Pruett, Vice President and Executive Publisher

Andy Cummings, Vice President and Publisher

Mary Bednarek, Executive Acquisitions Director

Robyn Siesky, Editorial Director

Sandy Smith, Senior Marketing Director

Amy Knies, Business Manager

Publishing for Consumer Dummies

Diane Graves Steele, Vice President and Publisher

Composition Services

Debbie Stailey, Director of Composition Services

01 454060-ffirs.indd viii 4/21/09 7:13:11 PM

www.it-ebooks.info

Contents at a Glance

Introduction ................................................................ 1

Part I: Statistics and Excel: A Marriage Made

in Heaven ................................................................... 7

Chapter 1: Evaluating Data in the Real World ................................................................9

Chapter 2: Understanding Excel’s Statistical Capabilities ..........................................27

Part II: Describing Data ............................................. 53

Chapter 3: Show and Tell: Graphing Data .....................................................................55

Chapter 4: Finding Your Center .....................................................................................79

Chapter 5: Deviating from the Average .........................................................................93

Chapter 6: Meeting Standards and Standings ............................................................111

Chapter 7: Summarizing It All .......................................................................................123

Chapter 8: What’s Normal? ...........................................................................................141

Part III: Drawing Conclusions from Data ................... 153

Chapter 9: The Confi dence Game: Estimation ...........................................................155

Chapter 10: One-Sample Hypothesis Testing .............................................................171

Chapter 11: Two-Sample Hypothesis Testing ............................................................187

Chapter 12: Testing More Than Two Samples ...........................................................217

Chapter 13: Slightly More Complicated Testing ........................................................243

Chapter 14: Regression: Linear and Multiple .............................................................255

Chapter 15: Correlation: The Rise and Fall of Relationships....................................291

Part IV: Working with Probability ............................. 311

Chapter 16: Introducing Probability ............................................................................313

Chapter 17: More on Probability .................................................................................335

Chapter 18: A Career in Modeling ................................................................................349

Part V: The Part of Tens ........................................... 367

Chapter 19: Ten Statistical and Graphical Tips and Traps .......................................369

Chapter 20: Ten Things (Twelve, Actually) That Didn’t Fit in Any

Other Chapter .............................................................................................................375

02 454060-ftoc.indd ix 4/21/09 7:14:15 PM

www.it-ebooks.info

Appendix A: When Your Worksheet Is a Database....... 405

Appendix B: The Analysis of Covariance .................... 419

Appendix C: Of Stems, Leaves, Boxes, Whiskers,

and Smoothies ......................................................... 433

Index ...................................................................... 453

02 454060-ftoc.indd x 4/21/09 7:14:15 PM

www.it-ebooks.info

Table of Contents

Introduction ................................................................. 1

About This Book ..............................................................................................1

What You Can Safely Skip ...............................................................................2

Foolish Assumptions .......................................................................................2

How This Book Is Organized ..........................................................................3

Part I: Statistics and Excel: A Marriage Made in Heaven ..................3

Part II: Describing Data .........................................................................3

Part III: Drawing Conclusions from Data .............................................3

Part IV: Working with Probability ........................................................3

Part V: The Part of Tens ........................................................................4

Appendix A: When Your Worksheet Is a Database ............................4

Appendix B: The Analysis of Covariance ............................................4

Appendix C: Of Stems, Leaves, Boxes, Whiskers, and Smoothies ...... 4

Icons Used in This Book .................................................................................5

Where to Go from Here ...................................................................................5

Part I: Statistics and Excel: A Marriage Made

in Heaven .................................................................... 7

Chapter 1: Evaluating Data in the Real World. . . . . . . . . . . . . . . . . . . . . .9

The Statistical (And Related) Notions You Just Have to Know .................9

Samples and populations ....................................................................10

Variables: Dependent and independent............................................11

Types of data ........................................................................................12

A little probability ................................................................................13

Inferential Statistics: Testing Hypotheses ..................................................14

Null and alternative hypotheses ........................................................15

Two types of error ...............................................................................16

What’s New in Excel? ....................................................................................18

Some Things about Excel You Absolutely Have to Know ........................20

Autofi lling cells .....................................................................................20

Referencing cells ..................................................................................22

What’s New in This Edition? ........................................................................25

Chapter 2: Understanding Excel’s Statistical Capabilities . . . . . . . . .27

Getting Started ..............................................................................................27

Setting Up for Statistics ................................................................................30

Worksheet functions in Excel 2007 ....................................................30

Quickly accessing statistical functions .............................................33

02 454060-ftoc.indd xi 4/21/09 7:14:15 PM

www.it-ebooks.info

xii Statistical Analysis with Excel For Dummies, 2nd Edition

Array functions ....................................................................................35

What’s in a name? An array of possibilities .....................................38

Creating your own array formulas ....................................................46

Using data analysis tools ....................................................................47

Accessing Commonly Used Functions ........................................................51

Part II: Describing Data .............................................. 53

Chapter 3: Show and Tell: Graphing Data . . . . . . . . . . . . . . . . . . . . . . . .55

Why Use Graphs? ...........................................................................................55

Some Fundamentals ......................................................................................57

Excel’s Graphics Capabilities .......................................................................58

Inserting a Chart ..................................................................................58

Becoming a Columnist ..................................................................................59

Stacking the columns ..........................................................................61

One more thing ...................................................................................63

Slicing the Pie .................................................................................................64

Pulling the slices apart ........................................................................66

A word from the wise ..........................................................................68

Drawing the Line ............................................................................................68

Passing the Bar .............................................................................................71

The Plot Thickens ..........................................................................................74

Chapter 4: Finding Your Center . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .79

Means: The Lore of Averages .......................................................................79

Calculating the mean ...........................................................................80

AVERAGE and AVERAGEA ..................................................................81

AVERAGEIF and AVERAGEIFS .............................................................83

TRIMMEAN ............................................................................................86

Other means to an end ........................................................................88

Medians: Caught in the Middle ....................................................................89

Finding the median ..............................................................................90

MEDIAN .................................................................................................90

Statistics À La Mode ......................................................................................91

Finding the mode .................................................................................91

MODE .....................................................................................................92

Chapter 5: Deviating from the Average . . . . . . . . . . . . . . . . . . . . . . . . . .93

Measuring Variation ......................................................................................94

Averaging squared deviations: Variance and how

to calculate it ....................................................................................94

VARP and VARPA .................................................................................97

Sample variance ...................................................................................99

VAR and VARA....................................................................................100

02 454060-ftoc.indd xii 4/21/09 7:14:15 PM

www.it-ebooks.info

Table of Contents xiii

Back to the Roots: Standard Deviation ...........................................100

Population standard deviation.........................................................101

STDEVP and STDEVPA ......................................................................101

Sample standard deviation ...............................................................102

STDEV and STDEVA ...........................................................................102

The missing functions: STDEVIF and STDEVIFS .............................103

Related Functions ........................................................................................107

DEVSQ .................................................................................................107

Average deviation ..............................................................................108

AVEDEV ...............................................................................................109

Chapter 6: Meeting Standards and Standings . . . . . . . . . . . . . . . . . . .111

Catching Some Zs ........................................................................................111

Characteristics of z-scores ...............................................................112

Bonds versus The Bambino ..............................................................112

Exam scores ........................................................................................113

STANDARDIZE ....................................................................................114

Where Do You Stand? .................................................................................116

RANK ...................................................................................................117

LARGE and SMALL .............................................................................118

PERCENTILE and PERCENTRANK ....................................................119

Data analysis tool: Rank and Percentile ..........................................121

Chapter 7: Summarizing It All. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .123

Counting Out ................................................................................................123

COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS .............123

The Long and Short of It .............................................................................126

MAX, MAXA, MIN, and MINA ...........................................................126

Getting Esoteric ...........................................................................................128

SKEW ...................................................................................................128

KURT ...................................................................................................130

Tuning In the Frequency .............................................................................132

FREQUENCY ........................................................................................132

Data analysis tool: Histogram ..........................................................134

Can You Give Me a Description? ................................................................136

Data analysis tool: Descriptive Statistics ........................................136

Instant Statistics .........................................................................................138

Chapter 8: What’s Normal?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .141

Hitting the Curve .........................................................................................141

Digging deeper ...................................................................................142

Parameters of a normal distribution ...............................................143

NORMDIST .........................................................................................145

NORMINV ............................................................................................146

A Distinguished Member of the Family .....................................................147

NORMSDIST .......................................................................................148

NORMSINV ..........................................................................................149

02 454060-ftoc.indd xiii 4/21/09 7:14:15 PM

www.it-ebooks.info