book

Statistical Analysis with Excel® For Dummies®, 2nd Edition

Name: Statistical Analysis with Excel® For Dummies®, 2nd Edition
Author: Joseph Schmuller
ISBN: 9780470454060

by Joseph Schmuller

June 2009

Beginner

500 pages

9h 40m

English

For Dummies

Read now

Unlock full access

Copyright
About the Author
Author's Acknowledgments
Publisher's Acknowledgments
Introduction
About This BookWhat You Can Safely SkipFoolish AssumptionsHow This Book Is OrganizedPart I: Statistics and Excel: A Marriage Made in HeavenPart II: Describing DataPart III: Drawing Conclusions from DataPart IV: Working with ProbabilityPart V: The Part of TensAppendix A: When Your Worksheet Is a DatabaseAppendix B: The Analysis of CovarianceAppendix C: Of Stems, Leaves, Boxes, Whiskers, and SmoothiesIcons Used in This BookWhere to Go from Here
I. Statistics and Excel: A Marriage Made in Heaven
1. Evaluating Data in the Real World
1.1. The Statistical (And Related) Notions You Just Have to Know1.1.1. Samples and populations1.1.2. Variables: Dependent and independent1.1.3. Types of data1.1.4. A little probability1.2. Inferential Statistics: Testing Hypotheses1.2.1. Null and alternative hypotheses1.2.2. Two types of error1.3. What's New in Excel?1.4. Some Things about Excel You Absolutely Have to Know1.4.1. Autofilling cells1.4.2. Referencing cells1.5. What's New in This Edition?
2. Understanding Excel's Statistical Capabilities
2.1. Getting Started2.2. Setting Up for Statistics2.2.1. Worksheet functions in Excel 20072.2.2. Quickly accessing statistical functions2.2.3. Array functions2.2.4. What's in a name? An array of possibilities2.2.5. Creating your own array formulas2.2.6. Using data analysis tools2.3. Accessing Commonly Used Functions
II. Describing Data
3. Show and Tell: Graphing Data
3.1. Why Use Graphs?3.2. Some Fundamentals3.3. Excel's Graphics Capabilities3.3.1. Inserting a chart3.4. Becoming a Columnist3.4.1. Stacking the columns3.4.2. One more thing3.5. Slicing the Pie3.5.1. Pulling the slices apart3.5.2. A word from the wise3.6. Drawing the Line3.7. Passing the Bar3.8. The Plot Thickens

4. Finding Your Center
4.1. Means: The Lore of Averages4.1.1. Calculating the mean4.1.2. AVERAGE and AVERAGEA4.1.3. AVERAGEIF and AVERAGEIFS4.1.4. TRIMMEAN4.1.5. Other means to an end4.1.5.1. Geometric mean4.1.5.2. Harmonic mean4.2. Medians: Caught in the Middle4.2.1. Finding the median4.2.2. MEDIAN4.3. Statistics À La Mode4.3.1. Finding the mode4.3.2. MODE
5. Deviating from the Average
5.1. Measuring Variation5.1.1. Averaging squared deviations: Variance and how to calculate it5.1.2. VARP and VARPA5.1.3. Sample variance5.1.4. VAR and VARA5.2. Back to the Roots: Standard Deviation5.2.1. Population standard deviation5.2.2. STDEVP and STDEVPA5.2.3. Sample standard deviation5.2.4. STDEV and STDEVA5.2.5. The missing functions: STDEVIF and STDEVIFS5.2.5.1. A little logic5.2.5.2. And now, back to the show5.3. Related Functions5.3.1. DEVSQ5.3.2. Average deviation5.3.3. AVEDEV
6. Meeting Standards and Standings
6.1. Catching Some Zs6.1.1. Characteristics of z-scores6.1.2. Bonds versus The Bambino6.1.3. Exam scores6.1.4. STANDARDIZE6.2. Where Do You Stand?6.2.1. RANK6.2.2. LARGE and SMALL6.2.3. PERCENTILE and PERCENTRANK6.2.4. Data analysis tool: Rank and Percentile
7. Summarizing It All
7.1. Counting Out7.1.1. COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS7.2. The Long and Short of It7.2.1. MAX, MAXA, MIN, and MINA7.3. Getting Esoteric7.3.1. SKEW7.3.2. KURT7.4. Tuning In the Frequency7.4.1. FREQUENCY7.4.2. Data analysis tool: Histogram7.5. Can You Give Me a Description?7.5.1. Data analysis tool: Descriptive Statistics7.6. Instant Statistics
8. What's Normal?
8.1. Hitting the Curve8.1.1. Digging deeper8.1.2. Parameters of a normal distribution8.1.3. NORMDIST8.1.4. NORMINV8.2. A Distinguished Member of the Family8.2.1. NORMSDIST8.2.2. NORMSINV
III. Drawing Conclusions from Data
9. The Confidence Game: Estimation
9.1. What is a Sampling Distribution?9.2. An EXTREMELY Important Idea: The Central Limit Theorem9.2.1. Simulating the Central Limit Theorem9.3. The Limits of Confidence9.3.1. Finding confidence limits for a mean9.3.2. CONFIDENCE9.4. Fit to a t9.4.1. TINV
10. One-Sample Hypothesis Testing
10.1. Hypotheses, Tests, and Errors10.1.1. Hypothesis tests and sampling distributions10.2. Catching Some Zs Again10.2.1. ZTEST10.3. t for One10.3.1. TDIST10.4. Testing a Variance10.4.1. CHIDIST10.4.2. CHIINV
11. Two-Sample Hypothesis Testing
11.1. Hypotheses Built for Two11.2. Sampling Distributions Revisited11.2.1. Applying the Central Limit Theorem11.2.2. Zs once more11.2.3. Data analysis tool: z-Test: Two Sample for Means11.3. t for Two11.3.1. Like peas in a pod: Equal variances11.3.2. Like p's and q's: Unequal variances11.3.3. TTEST11.3.4. Data Analysis Tools: t-test: Two Sample11.4. A Matched Set: Hypothesis Testing for Paired Samples11.4.1. TTEST for matched samples11.4.2. Data analysis tool: t-test: Paired Two Sample for Means11.5. Testing Two Variances11.5.1. Using F in conjunction with t11.5.2. FTEST11.5.3. FDIST11.5.4. FINV11.5.5. Data Analysis Tool: F-test Two Sample for Variances
12. Testing More Than Two Samples
12.1. Testing More Than Two12.1.1. A thorny problem12.1.2. A solution12.1.3. Meaningful relationships12.1.4. After the F-test12.1.4.1. Planned comparisons12.1.4.2. Unplanned comparisons12.1.5. Data analysis tool: Anova: Single Factor12.1.6. Comparing the means12.2. Another Kind of Hypothesis, Another Kind of Test12.2.1. Working with repeated measures ANOVA12.2.2. Getting trendy12.2.3. Data analysis tool: Anova: Two Factor Without Replication12.2.4. Analyzing trend
13. Slightly More Complicated Testing
13.1. Cracking the Combinations13.1.1. Breaking down the variances13.1.2. Data analysis tool: Anova: Two-Factor Without Replication13.2. Cracking the Combinations Again13.2.1. Rows and columns13.2.2. Interactions13.2.3. The analysis13.2.4. Data analysis tool: Anova: Two-Factor With Replication
14. Regression: Linear and Multiple
14.1. The Plot of Scatter14.2. Graphing Lines14.3. Regression: What a Line!14.3.1. Using regression for forecasting14.3.2. Variation around the regression line14.3.3. Testing hypotheses about regression14.3.3.1. Testing the fit14.3.3.2. Testing the slope14.3.3.3. Testing the intercept14.4. Worksheet Functions for Regression14.4.1. SLOPE, INTERCEPT, STEYX14.4.2. FORECAST14.4.3. Array function: TREND14.4.3.1. Predicting y's for the x's in your sample14.4.3.2. Predicting a new set of y's for a new set of x's14.4.4. Array function: LINEST14.5. Data Analysis Tool: Regression14.5.1. Tabled output14.5.2. Graphic output14.6. Juggling Many Relationships at Once: Multiple Regression14.7. Excel Tools for Multiple Regression14.7.1. TREND revisited14.7.2. LINEST revisited14.7.3. Regression data analysis tool revisited
15. Correlation: The Rise and Fall of Relationships
15.1. Scatterplots Again15.2. Understanding Correlation15.3. Correlation and Regression15.4. Testing Hypotheses About Correlation15.4.1. Is a correlation coefficient greater than zero?15.4.2. Do two correlation coefficients differ?15.5. Worksheet Functions for Correlation15.5.1. CORREL and PEARSON15.5.2. RSQ15.5.3. COVAR15.6. Data Analysis Tool: Correlation15.6.1. Tabled output15.6.1.1. Multiple correlation15.6.1.2. Partial correlation15.6.1.3. Semipartial correlation15.7. Data Analysis Tool: Covariance15.8. Testing Hypotheses About Correlation15.8.1. Worksheet Functions: FISHER, FISHERINV
IV. Working with Probability
16. Introducing Probability
16.1. What is Probability?16.1.1. Experiments, trials, events, and sample spaces16.1.2. Sample spaces and probability16.2. Compound Events16.2.1. Union and intersection16.2.2. Intersection again16.3. Conditional Probability16.3.1. Working with the probabilities16.3.2. The foundation of hypothesis testing16.4. Large Sample Spaces16.4.1. Permutations16.4.2. Combinations16.5. Worksheet Functions16.5.1. FACT16.5.2. PERMUT16.5.3. COMBIN16.6. Random Variables: Discrete and Continuous16.7. Probability Distributions and Density Functions16.8. The Binomial Distribution16.9. Worksheet Functions16.9.1. BINOMDIST16.9.2. NEGBINOMDIST16.10. Hypothesis Testing with the Binomial Distribution16.10.1. CRITBINOM16.10.2. More on hypothesis testing16.11. The Hypergeometric Distribution16.11.1. HYPERGEOMDIST
17. More on Probability
17.1. Beta17.1.1. BETADIST17.1.2. BETAINV17.2. Poisson17.2.1. POISSON17.3. Gamma17.3.1. GAMMADIST17.3.2. GAMMAINV17.4. Exponential17.4.1. EXPONDIST
18. A Career in Modeling
18.1. Modeling a Distribution18.1.1. Plunging into the Poisson distribution18.1.2. Using POISSON18.1.3. Testing the model's fit18.1.4. A word about CHITEST18.1.5. Playing ball with a model18.2. A Simulating Discussion18.2.1. Taking a chance: The Monte Carlo method18.2.2. Loading the dice18.2.2.1. Data analysis tool: Random Number Generation18.2.3. Simulating the Central Limit Theorem
V. The Part of Tens
19. Ten Statistical and Graphical Tips and Traps
19.1. Significant Doesn't Always Mean Important19.2. Trying to Not Reject a Null Hypothesis Has a Number of Implications19.3. Regression Isn't Always linear19.4. Extrapolating Beyond a Sample Scatterplot Is a Bad Idea19.5. Examine the Variability Around a Regression Line19.6. A Sample Can Be Too Large19.7. Consumers: Know Your Axes19.8. Graphing a Categorical Variable as Though It's a Quantitative Variable Is Just Wrong19.9. Whenever Appropriate, Include Variability in Your Graph19.10. Be Careful When Relating Statistics-Book Concepts to Excel
20. Ten Things (Twelve, Actually) That Didn't Fit in Any Other Chapter
20.1. Some Forecasting20.1.1. A moving experience20.1.2. How to be a smoothie, exponentially20.2. Graphing the Standard Error of the Mean20.3. Probabilities and Distributions20.3.1. PROB20.3.2. WEIBULL20.4. Drawing Samples20.5. Testing Independence: The True Use of CHITEST20.6. Logarithmica Esoterica20.6.1. What is a logarithm?20.6.2. What is e?20.6.3. LOGNORMDIST20.6.4. LOGINV20.6.5. Array Function: LOGEST20.6.6. Array Function: GROWTH20.6.6.1. Predicting y's for the x's in your sample20.6.6.2. Predicting a new set of y's for a new set of x's20.7. When Your Data Live Elsewhere
A. When Your Worksheet Is a Database
A.1. Introducing Excel DatabasesA.1.1. The Satellites databaseA.1.2. The criteria rangeA.1.3. The format of a database functionA.2. Counting and RetrievingA.2.1. DCOUNT and DCOUNTAA.2.1.1. Getting to "or"A.2.1.2. WildcardsA.2.2. DGETA.3. ArithmeticA.3.1. DMAX and DMINA.3.2. DSUMA.3.3. DPRODUCTA.4. StatisticsA.4.1. DAVERAGEA.4.2. DVAR and DVARPA.4.3. DSTDEV and DSTDEVPA.4.4. According to FormA.5. Pivot Tables
B. The Analysis of Covariance
B.1. Covariance: A Closer LookB.2. Why You Analyze CovarianceB.3. How You Analyze CovarianceB.4. ANCOVA in ExcelB.4.1. Method 1: ANOVAB.4.2. Method 2: RegressionB.4.3. After the ANCOVAB.5. And One More Thing
C. Of Stems, Leaves, Boxes, Whiskers, and Smoothies
C.1. Stem-and-LeafC.2. Boxes and WhiskersC.3. Data Smoothing

Content preview from Statistical Analysis with Excel® For Dummies®, 2nd Edition

Chapter 13. Slightly More Complicated Testing

In This Chapter

Working with two variables
Working with replications
Understanding interactions

In Chapter 11, I show you how to test hypotheses with two samples. In Chapter 12, I show you how to test hypotheses when you have more than two samples. The common thread through both chapters is that one independent variable (also called a factor) is involved.

Many times, you have to test the effects of more than one factor. In this Chapter, I show how to analyze two factors within the same set of data. Several types of situations are possible, and I describe Excel data analysis tools that deal with each one.

Cracking the Combinations

FarKlempt Robotics, Inc., manufactures battery-powered robots. They want to test three rechargeable batteries for these robots on a set of three tasks — climbing, walking, and assembling. Which combination of battery and task results in the longest battery life?

They test a sample of nine robots. They randomly assign each robot one battery and one type of task. FarKlempt tracks the number of days each robot works before recharging. The data are in Table 13-1.

Table 13.1. FarKlempt Robots: Number of Days before Recharging in Three Tasks with Three Batteries

Task	Battery 1	Battery 2	Battery 3	Average
Climbing	12	15	20	15.67
Walking	14	16	19	16.33
Assembling	11	14	18	14.33
Average	12.33	15.00	19.00	15.44

This calls for two hypothesis tests:

H₀: μ_Battery1 = μ_Battery2 = μ_Battery3

H₁: Not H₀

and

H₀: μ_Climbing = μ_Walking = μ_Assembling

H₁: Not H₀

In both tests, ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780470454060

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Statistical Analysis with Excel® For Dummies®, 2nd Edition

by Joseph Schmuller

Chapter 13. Slightly More Complicated Testing

Cracking the Combinations

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.