Statistical Analysis with Excel For Dummies

Joseph Schmuller works on the Digital & Enterprise Architecture Team at Availity. He has taught statistics at the undergraduate and graduate levels. He has created and delivered courses for LinkedIn Learning, and he is the author of all previous editions of Statistical Analysis with Excel For Dummies.

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