Joseph Schmuller - Statistical Analysis with Excel For Dummies

Statistical Analysis with Excel® For Dummies®, 5th Edition

Published by: John Wiley & Sons, Inc.,111 River Street, Hoboken, NJ 07030-5774, www.wiley.com

Copyright © 2022 by John Wiley & Sons, Inc., Hoboken, New Jersey

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 permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. 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, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. Excel is a registered trademark of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: WHILE THE PUBLISHER AND AUTHORS HAVE USED THEIR BEST EFFORTS IN PREPARING THIS WORK, THEY MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES REPRESENTATIVES, WRITTEN SALES MATERIALS OR PROMOTIONAL STATEMENTS FOR THIS WORK. THE FACT THAT AN ORGANIZATION, WEBSITE, OR PRODUCT IS REFERRED TO IN THIS WORK AS A CITATION AND/OR POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE PUBLISHER AND AUTHORS ENDORSE THE INFORMATION OR SERVICES THE ORGANIZATION, WEBSITE, OR PRODUCT MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING PROFESSIONAL SERVICES. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR YOUR SITUATION. YOU SHOULD CONSULT WITH A SPECIALIST WHERE APPROPRIATE. FURTHER, READERS SHOULD BE AWARE THAT WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ. NEITHER THE PUBLISHER NOR AUTHORS SHALL BE LIABLE FOR ANY LOSS OF PROFIT OR ANY OTHER COMMERCIAL DAMAGES, INCLUDING BUT NOT LIMITED TO SPECIAL, INCIDENTAL, CONSEQUENTIAL, OR OTHER DAMAGES.

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 https://hub.wiley.com/community/support/dummies .

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com . For more information about Wiley products, visit www.wiley.com .

Library of Congress Control Number: 2021950426

ISBN: 978-1-119-84454-9; 978-1-119-84455-6 (ebk); 978-1-119-84456-3 (ebk)

1 Cover

2 Title Page

3 Copyright

4 Introduction About This Book What’s New in This Edition What’s New in Excel (Microsoft 365) Foolish Assumptions Icons Used in This Book Where to Go from Here Beyond This Book

5 Part 1: Getting Started with Statistical Analysis with Excel: A Marriage Made in Heaven Chapter 1: Evaluating Data in the Real World The Statistical (and Related) Notions You Just Have to Know Inferential Statistics: Testing Hypotheses Some Excel Fundamentals Chapter 2: Understanding Excel's Statistical Capabilities Getting Started Setting Up for Statistics Creating Your Own Array Formulas Accessing Commonly Used Functions The New Analyze Data Tool Data from Pictures!

6 Part 2: Describing Data Chapter 3: Show-and-Tell: Graphing Data Why Use Graphs? Examining Some Fundamentals Gauging Excel's Graphics (Chartics?) Capabilities Becoming a Columnist Stacking the Columns Slicing the Pie Drawing the Line Adding a Spark Passing the Bar The Plot Thickens Finding Another Use for the Scatter Chart Chapter 4: Finding Your Center Means: The Lore of Averages Medians: Caught in the Middle Statistics à la Mode Chapter 5: Deviating from the Average Measuring Variation Back to the Roots: Standard Deviation Related Functions Chapter 6: Meeting Standards and Standings Catching Some Z’s Where Do You Stand? Chapter 7: Summarizing It All Counting Out The Long and Short of It Getting Esoteric Tuning In the Frequency Can You Give Me a Description? Be Quick About It! Instant Statistics Chapter 8: What's Normal? Hitting the Curve A Distinguished Member of the Family Graphing a Standard Normal Distribution

7 Part 3: Drawing Conclusions from Data Chapter 9: The Confidence Game: Estimation Understanding Sampling Distributions An EXTREMELY Important Idea: The Central Limit Theorem The Limits of Confidence Fit to a t Chapter 10: One-Sample Hypothesis Testing Hypotheses, Tests, and Errors Hypothesis Tests and Sampling Distributions Catching Some Z's Again t for One Visualizing a t-Distribution Testing a Variance Visualizing a Chi-Square Distribution Chapter 11: Two-Sample Hypothesis Testing Hypotheses Built for Two Sampling Distributions Revisited t for Two A Matched Set: Hypothesis Testing for Paired Samples Testing Two Variances Visualizing the F-Distribution Chapter 12: Testing More Than Two Samples Testing More than Two Another Kind of Hypothesis, Another Kind of Test ANOVA on the iPad ANOVA on the iPad: Another Way Repeated Measures ANOVA on the iPad Chapter 13: Slightly More Complicated Testing Cracking the Combinations Cracking the Combinations Again Two Kinds of Variables — at Once Using Excel with a Mixed Design Graphing the Results After the ANOVA Two-Factor ANOVA on the iPad Chapter 14: Regression: Linear and Multiple The Plot of Scatter Graphing a line Regression: What a Line! Worksheet Functions for Regression Data Analysis Tool: Regression Juggling Many Relationships at Once: Multiple Regression Excel Tools for Multiple Regression Regression Analysis on the iPad Chapter 15: Correlation: The Rise and Fall of Relationships Scatterplots Again Understanding Correlation Correlation and Regression Testing Hypotheses about Correlation Worksheet Functions for Correlation Data Analysis Tool: Correlation Data Analysis Tool: Covariance Using Excel to Test Hypotheses about Correlation Correlation Analysis on the iPad Chapter 16: It’s About Time A Series and Its Components A Moving Experience How to Be a Smoothie, Exponentially One-Click Forecasting Working with Time Series on the iPad Chapter 17: Nonparametric Statistics Independent Samples Matched Samples Correlation: Spearman’s r S A Heads-Up

8 Part 4: Probability Chapter 18: Introducing Probability What Is Probability? Compound Events Conditional Probability Large Sample Spaces Worksheet Functions Random Variables: Discrete and Continuous Probability Distributions and Density Functions The Binomial Distribution Worksheet Functions Hypothesis Testing with the Binomial Distribution The Hypergeometric Distribution Chapter 19: More on Probability Discovering Beta Poisson Working with Gamma Exponential Chapter 20: Using Probability: Modeling and Simulation Modeling a Distribution A Simulating Discussion Chapter 21: Estimating Probability: Logistic Regression Working Your Way Through Logistic Regression Mining with XLMiner