Joseph Schmuller

Statistical Analysis with Excel For Dummies

• Gebundenes Buch

Produktbeschreibung

* This book makes it easy to crunch numbers and interpret statistics with Excel, even for the math-challenged
* Using plain English and real-life examples, the author provides information that will help readers improve their performance on the job or in the classroom
* Covers formulas and functions, charts and PivotTables, samples and normal distributions, probabilities and related distributions, trends and correlations, as well as statistical terms like median vs. mean, margin of error, standard deviation, permutations, and correlations-all using Excel

Produktdetails

• Verlag: John Wiley & Sons
• Englisch
• ISBN-13: 9780764575945
• ISBN-10: 0764575945
• Artikelnr.: 12978374

Autorenporträt

Joseph Schmuller, PhD, is a Technical Architect at Blue Cross-Blue Shield of Florida and an adjunct professor at the University of North Florida.

Inhaltsangabe

- Introduction.

PART I: Statistics and Excel: A Marriage Made in Heaven.
- Chapter 1: Evaluating Data in the Real World.
- Chapter 2: Understanding Excel's Statistical Capabilities.

PART II: Describing Data.
- Chapter 3: Show and Tell: Graphing Data.
- Chapter 4: Finding Your Center.
- Chapter 5: Deviating from the Average.
- Chapter 6: Meeting Standards and Standings.
- Chapter 7: Summarizing It All.
- Chapter 8: What's Normal?

PART III: Drawing Conclusions from Data.
- Chapter 9: The Confidence Game: Estimation.
- Chapter 10: One-Sample Hypothesis Testing.
- Chapter 11: Two-Sample Hypothesis Testing.
- Chapter 12: Testing More than Two Samples.
- Chapter 13: Slightly More Complicated Testing.
- Chapter 14: Regression: Linear and Multiple.
- Chapter 15: Correlation: The Rise and Fall of Relationships.

PART IV: Working with Probability.
- Chapter 16: Introducing Probability.
- Chapter 17: More on Probability.
- Chapter 18: A Career in Modeling.

PART V: The Part of Tens.
- Chapter 19: Ten Statistical and Graphical Tips and Traps.
- Chapter 20: Ten (Or so) Things That Didn't Fit in Any Other Chapter.
- Appendix: When Your Worksheet is a Database.
- Index.

---

Introduction 1 About This Book 2 What's New in This Edition 2 What's New in
Excel (Microsoft 365) 3 Foolish Assumptions 3 Icons Used in This Book 4
Where to Go from Here 5 Beyond This Book 5 Part 1: Getting Started With
Statistical Analysis With 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 Some Excel Fundamentals 18 Autofilling cells 22
Referencing cells 25 Chapter 2: Understanding Excel's Statistical
Capabilities 29 Getting Started 30 Setting Up for Statistics 32 Worksheet
functions 32 Quickly accessing statistical functions 36 Array functions 38
What's in a name? An array of possibilities 41 Creating Your Own Array
Formulas 50 Using data analysis tools 51 Additional data analysis tool
packages 56 Accessing Commonly Used Functions 58 The New Analyze Data Tool
59 Data from Pictures! 60 Part 2: Describing Data 63 Chapter 3:
Show-and-Tell: Graphing Data 65 Why Use Graphs? 65 Examining Some
Fundamentals 67 Gauging Excel's Graphics (Chartics?) Capabilities 68
Becoming a Columnist 69 Stacking the Columns 73 Slicing the Pie 74 A word
from the wise 76 Drawing the Line 77 Adding a Spark 80 Passing the Bar 82
The Plot Thickens 84 Finding Another Use for the Scatter Chart 88 Chapter
4: Finding Your Center 91 Means: The Lore of Averages 91 Calculating the
mean 92 AVERAGE and AVERAGEA 93 AVERAGEIF and AVERAGEIFS 95 TRIMMEAN 99
Other means to an end 100 Medians: Caught in the Middle 102 Finding the
median 102 MEDIAN 103 Statistics à la Mode 104 Finding the mode 104
MODE.SNGL and MODE.MULT 104 Chapter 5: Deviating from the Average 107
Measuring Variation 108 Averaging squared deviations: Variance and how to
calculate it 108 VAR.P and VARPA 111 Sample variance 113 VAR.S and VARA 114
Back to the Roots: Standard Deviation 114 Population standard deviation 115
STDEV.P and STDEVPA 115 Sample standard deviation 116 STDEV.S and STDEVA
116 The missing functions: STDEVIF and STDEVIFS 117 Related Functions 121
DEVSQ 121 Average deviation 122 AVEDEV 123 Chapter 6: Meeting Standards and
Standings 125 Catching Some Z's 126 Characteristics of z-scores 126 Bonds
versus the Bambino 127 Exam scores 128 STANDARDIZE 128 Where Do You Stand?
131 RANK.EQ and RANK.AVG 131 LARGE and SMALL 133 PERCENTILE.INC and
PERCENTILE.EXC 134 PERCENTRANK.INC and PERCENTRANK.EXC 137 Data analysis
tool: Rank and Percentile 138 Chapter 7: Summarizing It All 141 Counting
Out 141 COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS 141 The Long and Short
of It 144 MAX, MAXA, MIN, and MINA 144 Getting Esoteric 145 SKEW and SKEW.P
146 KURT 148 Tuning In the Frequency 150 FREQUENCY 150 Data analysis tool:
Histogram 152 Can You Give Me a Description? 154 Data analysis tool:
Descriptive Statistics 154 Be Quick About It! 156 Instant Statistics 159
Chapter 8: What's Normal? 161 Hitting the Curve 161 Digging deeper 162
Parameters of a normal distribution 163 NORM.DIST 165 NORM.INV 167 A
Distinguished Member of the Family 168 NORM.S.DIST 169 NORM.S.INV 170 PHI
and GAUSS 170 Graphing a Standard Normal Distribution 171 Part 3: Drawing
Conclusions From Data 173 Chapter 9: The Confidence Game: Estimation 175
Understanding Sampling Distributions 176 An EXTREMELY Important Idea: The
Central Limit Theorem 177 (Approximately) simulating the Central Limit
Theorem 178 The Limits of Confidence 183 Finding confidence limits for a
mean 183 CONFIDENCE.NORM 186 Fit to a t 187 CONFIDENCE.T 188 Chapter 10:
One-Sample Hypothesis Testing 189 Hypotheses, Tests, and Errors 190
Hypothesis Tests and Sampling Distributions 191 Catching Some Z's Again 193
Z.TEST 196 t for One 197 T.DIST, T.DIST.RT, and T.DIST.2T 198 T.INV and
T.INV.2T 200 Visualizing a t-Distribution 201 Testing a Variance 203
CHISQ.DIST and CHISQ.DIST.RT 205 CHISQ.INV and CHISQ.INV.RT 206 Visualizing
a Chi-Square Distribution 208 Chapter 11: Two-Sample Hypothesis Testing 211
Hypotheses Built for Two 211 Sampling Distributions Revisited 212 Applying
the Central Limit Theorem 213 Z's once more 215 Data analysis tool: z-Test:
Two Sample for Means 216 t for Two 219 Like peas in a pod: Equal variances
220 Like p's and q's: Unequal variances 221 T.TEST 222 Data analysis tool:
t-Test: Two Sample 223 A Matched Set: Hypothesis Testing for Paired Samples
227 T.TEST for matched samples 228 Data analysis tool: t-Test: Paired Two
Sample for Means 230 t-tests on the iPad with StatPlus 232 Testing Two
Variances 235 Using F in conjunction with t 237 F.TEST 238 F.DIST and
F.DIST.RT 240 F.INV and F.INV.RT 241 Data analysis tool: F-test: Two Sample
for Variances 242 Visualizing the F-Distribution 244 Chapter 12: Testing
More Than Two Samples 247 Testing More than Two 247 A thorny problem 248 A
solution 249 Meaningful relationships 253 After the F-test 254 Data
analysis tool: Anova: Single Factor 258 Comparing the means 260 Another
Kind of Hypothesis, Another Kind of Test 262 Working with repeated measures
ANOVA 262 Getting trendy 264 Data analysis tool: Anova: Two-Factor Without
Replication 268 Analyzing trend 271 ANOVA on the iPad 272 ANOVA on the
iPad: Another Way 274 Repeated Measures ANOVA on the iPad 277 Chapter 13:
Slightly More Complicated Testing 281 Cracking the Combinations 281
Breaking down the variances 282 Data analysis tool: Anova: Two-Factor
Without Replication 284 Cracking the Combinations Again 286 Rows and
columns 286 Interactions 287 The analysis 288 Data analysis tool: Anova:
Two-Factor With Replication 289 Two Kinds of Variables -- at Once 292 Using
Excel with a Mixed Design 293 Graphing the Results 298 After the ANOVA 300
Two-Factor ANOVA on the iPad 300 Chapter 14: Regression: Linear and
Multiple 303 The Plot of Scatter 303 Graphing a line 305 Regression: What a
Line! 307 Using regression for forecasting 309 Variation around the
regression line 309 Testing hypotheses about regression 311 Worksheet
Functions for Regression 317 SLOPE, INTERCEPT, STEYX 318 FORECAST.LINEAR
319 Array function: TREND 319 Array function: LINEST 323 Data Analysis
Tool: Regression 325 Working with tabled output 327 Opting for graphical
output 329 Juggling Many Relationships at Once: Multiple Regression 330
Excel Tools for Multiple Regression 331 TREND revisited 331 LINEST
revisited 333 Regression data analysis tool revisited 336 Regression
Analysis on the iPad 338 Chapter 15: Correlation: The Rise and Fall of
Relationships 341 Scatterplots Again 341 Understanding Correlation 342
Correlation and Regression 345 Testing Hypotheses about Correlation 347 Is
a correlation coefficient greater than zero? 348 Do two correlation
coefficients differ? 349 Worksheet Functions for Correlation 350 CORREL and
PEARSON 350 RSQ 351 COVARIANCE.P and COVARIANCE.S 352 Data Analysis Tool:
Correlation 353 Tabled output 354 Multiple correlation 355 Partial
correlation 356 Semipartial correlation 357 Data Analysis Tool: Covariance
358 Using Excel to Test Hypotheses about Correlation 358 Worksheet
functions: FISHER, FISHERINV 359 Correlation Analysis on the iPad 360
Chapter 16: It's About Time 363 A Series and Its Components 363 A Moving
Experience 364 Lining up the trend 365 Data analysis tool: Moving Average
365 How to Be a Smoothie, Exponentially 368 One-Click Forecasting 369
Working with Time Series on the iPad 374 Chapter 17: Nonparametric
Statistics 379 Independent Samples 380 Two samples: Mann-Whitney U test 380
More than two samples: Kruskal-Wallis one-way ANOVA 382 Matched Samples 383
Two samples: Wilcoxon matched-pairs signed ranks 384 More than two samples:
Friedman two-way ANOVA 386 More than two samples: Cochran's Q 387
Correlation: Spearman's rS 389 A Heads-Up 391 Part 4: Probability 393
Chapter 18: Introducing Probability 395 What Is Probability? 395
Experiments, trials, events, and sample spaces 396 Sample spaces and
probability 396 Compound Events 397 Union and intersection 397
Intersection, again 398 Conditional Probability 399 Working with the
probabilities 400 The foundation of hypothesis testing 400 Large Sample
Spaces 400 Permutations 401 Combinations 402 Worksheet Functions 403 FACT
403 PERMUT and PERMUTIONA 403 COMBIN and COMBINA 404 Random Variables:
Discrete and Continuous 405 Probability Distributions and Density Functions
405 The Binomial Distribution 407 Worksheet Functions 409 BINOM.DIST and
BINOM.DIST.RANGE 409 NEGBINOM.DIST 411 Hypothesis Testing with the Binomial
Distribution 412 BINOM.INV 413 More on hypothesis testing 414 The
Hypergeometric Distribution 415 HYPGEOM.DIST 416 Chapter 19: More on
Probability 419 Discovering Beta 419 BETA.DIST 421 BETA.INV 423 Poisson 424
POISSON.DIST 425 Working with Gamma 427 The gamma function and GAMMA 427
The gamma distribution and GAMMA.DIST 428 GAMMA.INV 430 Exponential 431
EXPON.DIST 431 Chapter 20: Using Probability: Modeling and Simulation 433
Modeling a Distribution 434 Plunging into the Poisson distribution 434
Visualizing the Poisson distribution 435 Working with the Poisson
distribution 436 Using POISSON.DIST again 437 Testing the model's fit 437 A
word about CHISQ.TEST 440 Playing ball with a model 441 A Simulating
Discussion 444 Taking a chance: The Monte Carlo method 444 Loading the dice
444 Data analysis tool: Random Number Generation 445 Simulating the Central
limit Theorem 448 Simulating a business 452 Chapter 21: Estimating
Probability: Logistic Regression 457 Working Your Way Through Logistic
Regression 458 Mining with XLMiner 460 Part 5: The Part of Tens 465 Chapter
22: Ten (12, Actually) Statistical and Graphical Tips and Traps 467
Significant Doesn't Always Mean Important 467 Trying to Not Reject a Null
Hypothesis Has a Number of Implications 468 Regression Isn't Always Linear
468 Extrapolating Beyond a Sample Scatterplot Is a Bad Idea 469 Examine the
Variability Around a Regression Line 469 A Sample Can Be Too Large 470
Consumers: Know Your Axes 470 Graphing a Categorical Variable as a
Quantitative Variable Is Just Plain Wrong 471 Whenever Appropriate, Include
Variability in Your Graph 472 Be Careful When Relating Statistics Textbook
Concepts to Excel 472 It's Always a Good Idea to Use Named Ranges in Excel
472 Statistical Analysis with Excel on the iPad Is Pretty Good! 473 Chapter
23: Ten Topics (Thirteen, Actually) That Just Don't Fit Elsewhere 475
Graphing the Standard Error of the Mean 475 Probabilities and Distributions
479 PROB 479 WEIBULL.DIST 479 Drawing Samples 480 Testing Independence: The
True Use of CHISQ.TEST 481 Logarithmica Esoterica 484 What is a logarithm?
484 What is e? 486 LOGNORM.DIST 489 LOGNORM.INV 490 Array Function: LOGEST
491 Array Function: GROWTH 494 The logs of Gamma 497 Sorting Data 498 Part
6: Appendices 501 Appendix A: When Your Data Live Elsewhere 503 Appendix B:
Tips for Teachers (and Learners) 507 Augmenting Analyses Is a Good Thing
507 Understanding ANOVA 508 Revisiting regression 510 Simulating Data Is
Also a Good Thing 512 When All You Have Is a Graph 514 Appendix C: More on
Excel Graphics 515 Tasting the Bubbly 515 Taking Stock 516 Scratching the
Surface 518 On the Radar 519 Growing a Treemap and Bursting Some Sun 520
Building a Histogram 521 Ordering Columns: Pareto 522 Of Boxes and Whiskers
523 3D Maps 524 Filled Maps 527 Appendix D: The Analysis of Covariance 529
Covariance: A Closer Look 529 Why You Analyze Covariance 530 How You
Analyze Covariance 531 ANCOVA in Excel 532 Method 1: ANOVA 533 Method 2:
Regression 537 After the ANCOVA 540 And One More Thing 542 Index 545