Become a stats superstar by using Excel to reveal the powerful secrets of statisticsMicrosoft Excel offers numerous possibilities for statistical analysis--and you don't have to be a math wizard to unlock them. In Statistical Analysis with Excel For Dummies, fully updated for the 2021 version of Excel, you'll hit the ground running with straightforward techniques and practical guidance to unlock the power of statistics in Excel.Bypass unnecessary jargon and skip right to mastering formulas, functions, charts, probabilities, distributions, and correlations. Written for professionals and students without a background in statistics or math, you'll learn to create, interpret, and translate statistics--and have fun doing it!In this book you'll find out how to:* Understand, describe, and summarize any kind of data, from sports stats to sales figures* Confidently draw conclusions from your analyses, make accurate predictions, and calculate correlations* Model the probabilities of future outcomes based on past data* Perform statistical analysis on any platform: Windows, Mac, or iPad* Access additional resources and practice templates through Dummies.comFor anyone who's ever wanted to unleash the full potential of statistical analysis in Excel--and impress your colleagues or classmates along the way--Statistical Analysis with Excel For Dummies walks you through the foundational concepts of analyzing statistics and the step-by-step methods you use to apply them.

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