USE EXCEL’S STATISTICAL TOOLS TO TRANSFORM YOUR DATA INTO KNOWLEDGE
Nationally recognized Excel expert Conrad Carlberg shows you how to use Excel 2016 to perform core statistical tasks every business professional, student, and researcher should master. Using real-world examples and downloadable workbooks, Carlberg helps you choose the right technique for each problem and get the most out of Excel’s statistical features. Along the way, he clarifies confusing statistical terminology and helps you avoid common mistakes.
You’ll learn how to use correlation and regression, analyze variance and covariance, and test statistical hypotheses using the normal, binomial, t, and F distributions. To help you make accurate inferences based on samples from a population, Carlberg offers insightful coverage of crucial topics ranging from experimental design to the statistical power of F tests. Updated for Excel 2016, this guide covers both modern consistency functions and legacy compatibility functions.
Becoming an expert with Excel statistics has never been easier! In this book, you’ll find crystal-clear instructions, insider insights, and complete step-by-step guidance.
- Master Excel’s most useful descriptive and inferential statistical tools
- Understand how values cluster together or disperse, and how variables move or classify jointly
- Tell the truth with statistics—and recognize when others don’t
- Infer a population’s characteristics from a sample’s frequency distribution
- Explore correlation and regression to learn how variables move in tandem
- Use Excel consistency functions such as STDEV.S( ) and STDEV.P( )
- Test differences between two means using z tests, t tests, and Excel’s Data Analysis Add-in
- Identify skewed distributions using Excel’s new built-in box-and-whisker plots and histograms
- Evaluate statistical power and control risk
- Explore how randomized block and split plot designs alter the derivation of F-ratios
- Use coded multiple regression analysis to perform ANOVA with unbalanced factorial designs
- Analyze covariance with ANCOVA, and properly use multiple covariance
- Take advantage of Recommended PivotTables, Quick Analysis, and other Excel 2016 shortcuts
Table of Contents:
Introduction 1
Using Excel for Statistical Analysis .........1
About You and About Excel ...........................2
Clearing Up the Terms .....................3
Making Things Easier .......................3
The Wrong Box? ..............................4
Wagging the Dog ............................6
What’s in This Book ...............................6
1 About Variables and Values ................. 9
Variables and Values ..............................9
Recording Data in Lists ..................10
Making Use of Lists ........................11
Scales of Measurement ........................13
Category Scales ..............................13
Numeric Scales ..............................15
Telling an Interval Value from a Text Value ................16
Charting Numeric Variables in Excel ....18
Charting Two Variables ..................18
Understanding Frequency Distributions .................21
Using Frequency Distributions .......23
Building a Frequency Distribution from a Sample ...........26
Building Simulated Frequency Distributions .....................34
2 How Values Cluster Together ..............37
Calculating the Mean ...........................38
Understanding Functions, Arguments, and Results ............39
Understanding Formulas, Results, and Formats ................42
Minimizing the Spread ..................44
Calculating the Median ........................49
Choosing to Use the Median ..........50
Static or Robust? ............................51
Calculating the Mode ...........................52
Getting the Mode of Categories with a Formula ..........56
From Central Tendency to Variability ...63
3 Variability: How Values Disperse .........65
Measuring Variability with the Range .66
Sample Size and the Range ...........67
Variations on the Range ................69
The Concept of a Standard Deviation ...70
Arranging for a Standard ...............71
Thinking in Terms of Standard Deviations ..............72
Calculating the Standard Deviation and Variance ..........74
Squaring the Deviations ................77
Population Parameters and Sample Statistics ..........78
Dividing by N ??1 ..........................79
Bias in the Estimate and Degrees of Freedom ...............81
Excel’s Variability Functions .................82
Standard Deviation Functions ........82
Variance Functions ........................83
4 How Variables Move Jointly: Correlation ..........85
Understanding Correlation ..................85
The Correlation, Calculated ............87
Using the CORREL() Function .........93
Using the Analysis Tools ................96
Using the Correlation Tool .............98
Correlation Isn’t Causation...........101
Using Correlation ...............................102
Removing the Effects of the Scale ...............103
Using the Excel Function..............106
Getting the Predicted Values .......107
Getting the Regression Formula ..109
Using TREND() for Multiple Regression ...............111
Combining the Predictors ............111
Understanding “Best Combination” ...........112
Understanding Shared Variance ..116
A Technical Note: Matrix Algebra and Multiple Regression in Excel ........ 118
5 Charting Statistics .............121
Characteristics of Excel Charts ............122
Chart Axes ...................................122
Date Variables on Category Axes .123
Other Numeric Variables on a Category Axis ...........125
Histogram Charts ...............................127
Using a Pivot Table to Count the Records ................127
Using Advanced Filter and FREQUENCY() .....................129
The Data Analysis Add-in’s Histogram .......................131
The Built-in Histogram ................132
Data Series Addresses ..................133
Box-and-Whisker Plots ......................134
Managing Outliers .......................137
Diagnosing Asymmetry ...............137
Comparing Distributions ..............138
6 How Variables Classify Jointly: Contingency Tables ... 139
Understanding One-Way Pivot Tables .....................139
Running the Statistical Test .........143
Making Assumptions .........................148
Random Selection ........................148
Independent Selections ...............150
The Binomial Distribution Formula .............................150
Using the BINOM.INV() Function .152
Understanding Two-Way Pivot Tables ..........................158
Probabilities and Independent Events .....................161
Testing the Independence of Classifications ..............163
About Logistic Regression............168
The Yule Simpson Effect ....................169
Summarizing the Chi-Square Functions .......................171
Using CHISQ.DIST() ......................171
Using CHISQ.DIST.RT() and CHIDIST() ........................173
Using CHISQ.INV() ........................174
Using CHISQ.INV.RT() and CHIINV() .......................175
Using CHISQ.TEST() and CHITEST() ...........................176
Using Mixed and Absolute References to Calculate Expected Frequencies ..............177
Using the Pivot Table’s Index Display .......................178
7 Using Excel with the Normal Distribution .................181
About the Normal Distribution ..........181
Characteristics of the Normal Distribution ...................181
The Unit Normal Distribution .......186
Excel Functions for the Normal Distribution ...................187
The NORM.DIST( ) Function ..........187
The NORM.INV( ) Function ...........190
Confidence Intervals and the Normal Distribution ..............192
The Meaning of a Confidence Interval .....................................193
Constructing a Confidence Interval ............................194
Excel Worksheet Functions That Calculate Confidence Intervals ...............198
Using CONFIDENCE.NORM( ) and CONFIDENCE( ) ......................198
Using CONFIDENCE.T( ) .................201
Using the Data Analysis Add-In for Confidence Intervals .........202
Confidence Intervals and Hypothesis Testing .................204
The Central Limit Theorem ................205
Dealing with a Pivot Table Idiosyncrasy ................206
Making Things Easier ...................207
Making Things Better ..................209
8 Telling the Truth with Statistics ........211
A Context for Inferential Statistics .....212
Establishing Internal Validity .......213
Threats to Internal Validity ..........214
Problems with Excel’s Documentation ...................218
The F-Test Two-Sample for Variances 219
Why Run the Test? .......................220
Reproducibility ..................................232
A Final Point ......................................234
9 Testing Differences Between Means: The Basics ............235
Testing Means: The Rationale ............236
Using a z-Test ..............................237
Using the Standard Error of the Mean ...................240
Creating the Charts ......................244
Using the t-Test Instead of the z-Test 252
Defining the Decision Rule ...........254
Understanding Statistical Power .258
10 Testing Differences Between Means: Further Issues ..........263
Using Excel’s T.DIST() and T.INV() Functions to Test Hypotheses ...263
Making Directional and Nondirectional Hypotheses ................264
Using Hypotheses to Guide Excel’s t-Distribution Functions ....265
Completing the Picture with T.DIST() .....................273
Using the T.TEST() Function ...............275
Degrees of Freedom in Excel Functions....................275
Equal and Unequal Group Sizes ...276
The T.TEST() Syntax .....................278
Using the Data Analysis Add-in t-Tests .................291
Group Variances in t-Tests ...........291
Visualizing Statistical Power ........297
When to Avoid t-Tests .................298
11 Testing Differences Between Means: The Analysis of Variance ........299
Why Not t-Tests? ...............................299
The Logic of ANOVA ...........................301
Partitioning the Scores ................302
Comparing Variances ...................305
The F-Test ....................................309
Using Excel’s F Worksheet Functions .312
Using F.DIST() and F.DIST.RT() .....312
Using F.INV() and FINV() ..............314
The F-Distribution ........................315
Unequal Group Sizes ..........................316
Multiple Comparison Procedures .......318
The Scheffé Procedure .................320
Planned Orthogonal Contrasts .....324
12 Analysis of Variance: Further Issues...329
Factorial ANOVA .................................329
Other Rationales for Multiple Factors ....................330
Using the Two-Factor ANOVA Tool ..........................333
The Meaning of Interaction ...............335
The Statistical Significance of an Interaction ................336
Calculating the Interaction Effect 338
The Problem of Unequal Group Sizes .342
Repeated Measures: The Two Factor Without Replication Tool .............345
Excel’s Functions and Tools: Limitations and Solutions ..................346
Mixed Models ..............................347
Power of the F-Test .....................348
13 Experimental Design and ANOVA.......349
Crossed Factors and Nested Factors ...349
Depicting the Design Accurately ..351
Nuisance Factors ..........................352
Fixed Factors and Random Factors.....352
The Data Analysis Add-In’s ANOVA Tools ...........354
Data Layout .................................356
Calculating the F Ratios .....................357
Adapting the Data Analysis Tool for a Random Factor .............357
Designing the F-Test ....................358
The Mixed Model: Choosing the Denominator .........................359
Adapting the Data Analysis Tool for a Nested Factor ...............361
Data Layout for a Nested Design..362
Getting the Sums of Squares .......363
Calculating the F Ratio for the Nesting Factor ................363
Randomized Block Designs ................364
Interaction Between Factors and Blocks .......................366
Tukey’s Test for Nonadditivity .....368
Increasing Statistical Power .........369
Blocks as Fixed or Random ..........370
Split-Plot Factorial Designs ................371
Assembling a Split-Plot Factorial Design ....................371
Analysis of the Split-Plot Factorial Design .......................372
14 Statistical Power ..................377
Controlling the Risk ...........................377
Directional and Nondirectional Hypotheses ...........378
Changing the Sample Size ...........378
Visualizing Statistical Power ........378
The Statistical Power of t-Tests ..........382
Nondirectional Hypotheses ..........382
Making a Directional Hypothesis .385
Increasing the Size of the Samples .......................387
The Dependent Groups t-Test ......387
The Noncentrality Parameter in the F-Distribution ................389
Variance Estimates ......................389
The Noncentrality Parameter and the Probability Density Function ............393
Calculating the Power of the F-Test ...395
Calculating the Cumulative Density Function ..........396
Using Power to Determine Sample Size ...............397
15 Multiple Regression Analysis and Effect Coding: The Basics ............401
Multiple Regression and ANOVA ........402
Using Effect Coding .....................404
Effect Coding: General Principles .404
Other Types of Coding .................406
Multiple Regression and Proportions of Variance ..........................406
Understanding the Segue from ANOVA to Regression .............409
The Meaning of Effect Coding ......411
Assigning Effect Codes in Excel ..........414
Using Excel’s Regression Tool with Unequal Group Sizes ...............416
Effect Coding, Regression, and Factorial Designs in Excel ..............418
Exerting Statistical Control with Semipartial Correlations .......420
Using a Squared Semipartial to Get the Correct Sum of Squares ................421
Using TREND() to Replace Squared Semipartial Correlations .........422
Working with the Residuals .........424
Using Excel’s Absolute and Relative Addressing to Extend the Semipartials ..............426
16 Multiple Regression Analysis and Effect Coding: Further Issues .......431
Solving Unbalanced Factorial Designs Using Multiple Regression .431
Variables Are Uncorrelated in a Balanced Design ....................433
Variables Are Correlated in an Unbalanced Design ..................434
Order of Entry Is Irrelevant in the Balanced Design..................435
Order Entry Is Important in the Unbalanced Design ................437
Proportions of Variance Can Fluctuate .....................................439
Experimental Designs, Observational Studies, and Correlation ......440
Using All the LINEST() Statistics .........443
Looking Inside LINEST() .....................450
Understanding How LINEST() Calculates Its Results .................450
Getting the Regression Coefficients .........................................452
Getting the Sum of Squares Regression and Residual..............456
Calculating the Regression Diagnostics ...................................458
Understanding How LINEST() Handles Multicollinearity ..........462
Forcing a Zero Constant ...............466
The Excel 2007 Version ................467
A Negative R2? .............................470
Managing Unequal Group Sizes in a True Experiment ...................474
Managing Unequal Group Sizes in Observational Research ...........476
17 Analysis of Covariance: The Basics .....479
The Purposes of ANCOVA ...................480
Greater Power .............................480
Bias Reduction .............................480
Using ANCOVA to Increase Statistical Power ..................................481
ANOVA Finds No Significant Mean Difference ..........................482
Adding a Covariate to the Analysis ..........................................483
Testing for a Common Regression Line ..........................................490
Removing Bias: A Different Outcome .493
18 Analysis of Covariance: Further Issues ..................499
Adjusting Means with LINEST() and Effect Coding .........................499
Effect Coding and Adjusted Group Means ......................................504
Multiple Comparisons Following ANCOVA .....................................507
Using the Scheffé Method ...........507
Using Planned Contrasts ..............512
The Analysis of Multiple Covariance ...514
The Decision to Use Multiple Covariates ..................................514
Two Covariates: An Example ........515
When Not to Use ANCOVA .................517
Intact Groups ...............................517
Extrapolation ...............................519
TOC, 9780789759054, 11/2/2017
About the Author :
Conrad Carlberg started writing about Excel, and its use in quantitative analysis, before workbooks had worksheets. As a graduate student, he had the great good fortune to learn something about statistics from the wonderfully gifted Gene Glass. He remembers much of that and has learned more since. This is a book he has wanted to rewrite for years, and he is grateful for the opportunity.
