Schmuller J. Statistical Analysis with Excel For Dummies
Подождите немного. Документ загружается.
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 Con 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 ix02 454060-ftoc.indd ix 4/21/09 7:14:15 PM4/21/09 7:14:15 PM
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 x02 454060-ftoc.indd x 4/21/09 7:14:15 PM4/21/09 7:14:15 PM
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
Auto 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 xi02 454060-ftoc.indd xi 4/21/09 7:14:15 PM4/21/09 7:14:15 PM
Statistical Analysis with Excel For Dummies, 2nd Edition
xii
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 xii02 454060-ftoc.indd xii 4/21/09 7:14:15 PM4/21/09 7:14:15 PM
xiii
Table of Contents
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 xiii02 454060-ftoc.indd xiii 4/21/09 7:14:15 PM4/21/09 7:14:15 PM
Statistical Analysis with Excel For Dummies, 2nd Edition
xiv
Part III: Drawing Conclusions from Data .................... 153
Chapter 9: The Confi dence Game: Estimation. . . . . . . . . . . . . . . . . . . .155
What is a Sampling Distribution? ..............................................................155
An EXTREMELY Important Idea: The Central Limit Theorem ..............157
Simulating the Central Limit Theorem ............................................158
The Limits of Con dence ...........................................................................162
Finding con dence limits for a mean ..............................................163
CONFIDENCE ......................................................................................165
Fit to a t ........................................................................................................166
TINV .....................................................................................................168
Chapter 10: One-Sample Hypothesis Testing . . . . . . . . . . . . . . . . . . . .171
Hypotheses, Tests, and Errors ..................................................................171
Hypothesis tests and sampling distributions ................................172
Catching Some Zs Again ............................................................................175
ZTEST .................................................................................................177
t for One ........................................................................................................179
TDIST ..................................................................................................180
Testing a Variance .......................................................................................181
CHIDIST ..............................................................................................182
CHIINV .................................................................................................183
Chapter 11: Two-Sample Hypothesis Testing. . . . . . . . . . . . . . . . . . . .187
Hypotheses Built for Two ...........................................................................187
Sampling Distributions Revisited ..............................................................188
Applying the Central Limit Theorem ...............................................189
Zs once more ......................................................................................191
Data analysis tool: z-Test: Two Sample for Means ........................192
t for Two .......................................................................................................195
Like peas in a pod: Equal variances ................................................195
Like p’s and q’s: Unequal variances ................................................197
TTEST .................................................................................................. 197
Data Analysis Tools: t-test: Two Sample ........................................199
A Matched Set: Hypothesis Testing for Paired Samples .........................202
TTEST for matched samples ............................................................203
Data analysis tool: t-test: Paired Two Sample for Means .............205
Testing Two Variances ...............................................................................207
Using F in conjunction with t ............................................................209
FTEST...................................................................................................210
FDIST ...................................................................................................212
FINV .....................................................................................................213
Data Analysis Tool: F-test Two Sample for Variances ...................214
02 454060-ftoc.indd xiv02 454060-ftoc.indd xiv 4/21/09 7:14:15 PM4/21/09 7:14:15 PM
xv
Table of Contents
Chapter 12: Testing More Than Two Samples . . . . . . . . . . . . . . . . . . .217
Testing More Than Two ..............................................................................217
A thorny problem ..............................................................................218
A solution ............................................................................................219
Meaningful relationships ..................................................................223
After the F-test ....................................................................................224
Data analysis tool: Anova: Single Factor .........................................228
Comparing the means .......................................................................230
Another Kind of Hypothesis, Another Kind of Test ................................232
Working with repeated measures ANOVA ......................................232
Getting trendy ....................................................................................235
Data analysis tool: Anova: Two Factor Without Replication ........238
Analyzing trend ..................................................................................240
Chapter 13: Slightly More Complicated Testing. . . . . . . . . . . . . . . . . .243
Cracking the Combinations ........................................................................243
Breaking down the variances ...........................................................244
Data analysis tool: Anova: Two-Factor Without Replication ........246
Cracking the Combinations Again .............................................................248
Rows and columns .............................................................................248
Interactions .........................................................................................249
The analysis ........................................................................................250
Data analysis tool: Anova: Two-Factor With Replication..............252
Chapter 14: Regression: Linear and Multiple . . . . . . . . . . . . . . . . . . . .255
The Plot of Scatter .......................................................................................255
Graphing Lines .............................................................................................257
Regression: What a Line! .............................................................................259
Using regression for forecasting ......................................................261
Variation around the regression line ..............................................261
Testing hypotheses about regression .............................................263
Worksheet Functions for Regression ........................................................269
SLOPE, INTERCEPT, STEYX ..............................................................269
FORECAST ...........................................................................................271
Array function: TREND ......................................................................272
Array function: LINEST .....................................................................275
Data Analysis Tool: Regression ................................................................277
Tabled output .....................................................................................279
Graphic output ...................................................................................280
Juggling Many Relationships at Once: Multiple Regression ..................282
Excel Tools for Multiple Regression .........................................................283
TREND revisited .................................................................................283
LINEST revisited .................................................................................285
Regression data analysis tool revisited ..........................................287
02 454060-ftoc.indd xv02 454060-ftoc.indd xv 4/21/09 7:14:15 PM4/21/09 7:14:15 PM
Statistical Analysis with Excel For Dummies, 2nd Edition
xvi
Chapter 15: Correlation: The Rise and Fall of Relationships . . . . . . .291
Scatterplots Again .......................................................................................291
Understanding Correlation .........................................................................292
Correlation and Regression ........................................................................294
Testing Hypotheses About Correlation ....................................................297
Is a correlation coef cient greater than zero? ...............................297
Do two correlation coef cients differ? ............................................298
Worksheet Functions for Correlation .......................................................300
CORREL and PEARSON .....................................................................300
RSQ ......................................................................................................302
COVAR .................................................................................................302
Data Analysis Tool: Correlation .................................................................303
Tabled output .....................................................................................304
Data Analysis Tool: Covariance .................................................................307
Testing Hypotheses About Correlation ....................................................308
Worksheet Functions: FISHER, FISHERINV .....................................308
Part IV: Working with Probability ............................. 311
Chapter 16: Introducing Probability . . . . . . . . . . . . . . . . . . . . . . . . . . . .313
What is Probability? ....................................................................................313
Experiments, trials, events, and sample spaces ............................314
Sample spaces and probability ........................................................314
Compound Events .......................................................................................315
Union and intersection ......................................................................315
Intersection again ..............................................................................316
Conditional Probability ...............................................................................317
Working with the probabilities ........................................................318
The foundation of hypothesis testing .............................................318
Large Sample Spaces ...................................................................................318
Permutations .....................................................................................319
Combinations .....................................................................................320
Worksheet Functions .................................................................................321
FACT ....................................................................................................321
PERMUT ..............................................................................................321
COMBIN ..............................................................................................322
Random Variables: Discrete and Continuous ..........................................322
Probability Distributions and Density Functions ....................................323
The Binomial Distribution ..........................................................................325
Worksheet Functions .................................................................................326
BINOMDIST ........................................................................................327
NEGBINOMDIST ..................................................................................328
02 454060-ftoc.indd xvi02 454060-ftoc.indd xvi 4/21/09 7:14:15 PM4/21/09 7:14:15 PM
xvii
Table of Contents
Hypothesis Testing with the Binomial Distribution ................................329
CRITBINOM .........................................................................................330
More on hypothesis testing ..............................................................331
The Hypergeometric Distribution .............................................................332
HYPERGEOMDIST ..............................................................................333
Chapter 17: More on Probability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .335
Beta ................................................................................................................335
BETADIST ............................................................................................337
BETAINV ..............................................................................................338
Poisson ..........................................................................................................340
POISSON ..............................................................................................341
Gamma ..........................................................................................................342
GAMMADIST .......................................................................................343
GAMMAINV ......................................................................................... 345
Exponential ...................................................................................................345
EXPONDIST ......................................................................................... 346
Chapter 18: A Career in Modeling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .349
Modeling a Distribution ..............................................................................349
Plunging into the Poisson distribution ...........................................350
Using POISSON ..................................................................................352
Testing the model’s t .......................................................................352
A word about CHITEST......................................................................355
Playing ball with a model ..................................................................356
A Simulating Discussion .............................................................................359
Taking a chance: The Monte Carlo method....................................359
Loading the dice .................................................................................359
Simulating the Central Limit Theorem ............................................363
Part V: The Part of Tens ............................................ 367
Chapter 19: Ten Statistical and Graphical Tips and Traps. . . . . . . . .369
Signi cant Doesn’t Always Mean Important ............................................369
Trying to Not Reject a Null Hypothesis Has a Number
of Implications ..........................................................................................370
Regression Isn’t Always linear. ..................................................................370
Extrapolating Beyond a Sample Scatterplot Is a Bad Idea .....................371
Examine the Variability Around a Regression Line .................................371
A Sample Can Be Too Large .......................................................................371
Consumers: Know Your Axes .....................................................................372
Graphing a Categorical Variable as Though It’s a Quantitative
Variable Is Just Wrong.............................................................................372
Whenever Appropriate, Include Variability in Your Graph ...................373
Be Careful When Relating Statistics-Book Concepts to Excel ................374
02 454060-ftoc.indd xvii02 454060-ftoc.indd xvii 4/21/09 7:14:15 PM4/21/09 7:14:15 PM
Statistical Analysis with Excel For Dummies, 2nd Edition
xviii
Chapter 20: Ten Things (Twelve, Actually) That Didn’t Fit
in Any Other Chapter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .375
Some Forecasting ........................................................................................375
A moving experience .........................................................................375
How to be a smoothie, exponentially ..............................................377
Graphing the Standard Error of the Mean ................................................379
Probabilities and Distributions ..................................................................383
PROB ....................................................................................................383
WEIBULL .............................................................................................383
Drawing Samples .........................................................................................384
Testing Independence: The True Use of CHITEST ..................................385
Logarithmica Esoterica ...............................................................................388
What is a logarithm? ..........................................................................388
What is e? ............................................................................................390
LOGNORMDIST ...................................................................................393
LOGINV ................................................................................................394
Array Function: LOGEST ...................................................................395
Array Function: GROWTH .................................................................398
When Your Data Live Elsewhere ...............................................................401
Appendix A: When Your Worksheet Is a Database ....... 405
Introducing Excel Databases ......................................................................405
The Satellites database .....................................................................405
The criteria range ..............................................................................407
The format of a database function ..................................................408
Counting and Retrieving ............................................................................409
DCOUNT and DCOUNTA ..................................................................409
DGET ....................................................................................................410
Arithmetic .....................................................................................................410
DMAX and DMIN.................................................................................411
DSUM ..................................................................................................411
DPRODUCT .........................................................................................411
Statistics .......................................................................................................412
DAVERAGE ..........................................................................................412
DVAR and DVARP...............................................................................412
DSTDEV and DSTDEVP ......................................................................413
According to Form .............................................................................413
Pivot Tables ..................................................................................................414
02 454060-ftoc.indd xviii02 454060-ftoc.indd xviii 4/21/09 7:14:15 PM4/21/09 7:14:15 PM