Statistical Analysis

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 write for years, and he is grateful for the opportunity.

Introduction   xi
Using Excel for Statistical Analysis   xi
   About You and About Excel   xii
   Clearing Up the Terms   xii
   Making Things Easier   xiii
   The Wrong Box?   xiv
   Wagging the Dog   xvi
What’s in This Book   xvi
1 About Variables and Values   1
Variables and Values   1
   Recording Data in Lists   2
Scales of Measurement   4
   Category Scales   5
   Numeric Scales   7
   Telling an Interval Value from a Text Value   8
Charting Numeric Variables in Excel   10
   Charting Two Variables   10
Understanding Frequency Distributions   12
   Using Frequency Distributions   15
   Building a Frequency Distribution from a Sample   18
   Building Simulated Frequency Distributions   26
2 How Values Cluster Together   29
Calculating the Mean   30
   Understanding Functions, Arguments, and Results   31
   Understanding Formulas, Results, and Formats   34
   Minimizing the Spread   36
Calculating the Median   41
   Choosing to Use the Median   41
Calculating the Mode   42
   Getting the Mode of Categories with a Formula   47
From Central Tendency to Variability   54
3 Variability: How Values Disperse   55
Measuring Variability with the Range   56
The Concept of a Standard Deviation   58
   Arranging for a Standard   59
   Thinking in Terms of Standard Deviations   60
Calculating the Standard Deviation and Variance   62
   Squaring the Deviations   65
   Population Parameters and Sample Statistics   66
   Dividing by N – 1   66
Bias in the Estimate   68
   Degrees of Freedom   69
Excel’s Variability Functions   70
   Standard Deviation Functions   70
   Variance Functions   71
4 How Variables Move Jointly: Correlation   73
Understanding Correlation   73
   The Correlation, Calculated   75
   Using the CORREL() Function   81
   Using the Analysis Tools   84
   Using the Correlation Tool   86
   Correlation Isn’t Causation   88
Using Correlation   90
   Removing the Effects of the Scale   91
   Using the Excel Function   93
   Getting the Predicted Values   95
   Getting the Regression Formula   96
Using TREND() for Multiple Regression   99
   Combining the Predictors   99
   Understanding “Best Combination”   100
   Understanding Shared Variance   104
   A Technical Note: Matrix Algebra and Multiple Regression in Excel   106
Moving on to Statistical Inference   107
5 How Variables Classify Jointly: Contingency Tables   109
Understanding One-Way Pivot Tables   109
   Running the Statistical Test   112
Making Assumptions   117
   Random Selection   118
   Independent Selections   119
   The Binomial Distribution Formula   120
   Using the BINOM   INV() Function   121
Understanding Two-Way Pivot Tables   127
   Probabilities and Independent Events   130
   Testing the Independence of Classifications   131
The Yule Simpson effect   137
Summarizing the Chi-Square Functions   140
   Using CHISQ   DIST()   140
   Using CHISQ   DIST   RT() and CHIDIST()   141
   Using CHISQ   INV()   143
   Using CHISQ   INV   RT() and CHIINV()   143
   Using CHISQ   TEST() and CHITEST()   144
   Using Mixed and Absolute References to Calculate Expected Frequencies   145
   Using the Pivot Table’s Index Display   146
6 Telling the Truth with Statistics   149
A Context for Inferential Statistics   150
   Establishing Internal Validity   151
   Threats to Internal Validity   152
Problems with Excel’s Documentation   156
The F-Test Two-Sample for Variances   157
   Why Run the Test?   158
   A Final Point   169
7 Using Excel with the Normal Distribution   171
About the Normal Distribution   171
   Characteristics of the Normal Distribution   171
   The Unit Normal Distribution   176
Excel Functions for the Normal Distribution   177
   The NORM   DIST() Function   177
   The NORM   INV() Function   180
Confidence Intervals and the Normal Distribution   182
   The Meaning of a Confidence Interval   183
   Constructing a Confidence Interval   184
   Excel Worksheet Functions That Calculate Confidence Intervals   187
   Using CONFIDENCE   NORM() and CONFIDENCE()   188
   Using CONFIDENCE   T()   191
   Using the Data Analysis Add-In for Confidence Intervals   192
   Confidence Intervals and Hypothesis Testing   194
The Central Limit Theorem   194
   Making Things Easier   196
   Making Things Better   198
8 Testing Differences Between Means: The Basics   199
Testing Means: The Rationale   200
   Using a z-Test   201
   Using the Standard Error of the Mean   204
   Creating the Charts   208
Using the t-Test Instead of the z-Test   216
   Defining the Decision Rule   218
   Understanding Statistical Power   222
9 Testing Differences Between Means: Further Issues   227
Using Excel’s T   DIST() and T   INV() Functions to Test Hypotheses   227
   Making Directional and Nondirectional Hypotheses   228
   Using Hypotheses to Guide Excel’s t-Distribution Functions   229
   Completing the Picture with T   DIST()   237
Using the T   TEST() Function   238
   Degrees of Freedom in Excel Functions   238
   Equal and Unequal Group Sizes   239
   The T   TEST() Syntax   242
Using the Data Analysis Add-in t-Tests   255
   Group Variances in t-Tests   255
   Visualizing Statistical Power   260
   When to Avoid t-Tests   261
10 Testing Differences Between Means: The Analysis of Variance   263
Why Not t-Tests?   263
The Logic of ANOVA   265
   Partitioning the Scores   265
   Comparing Variances   268
   The F Test   273
Using Excel’s Worksheet Functions for the F Distribution   277
   Using F   DIST() and F   DIST   RT()   277
   Using F   INV() and FINV()   278
   The F Distribution   279
Unequal Group Sizes   280
Multiple Comparison Procedures   282
   The Scheffé Procedure   284
   Planned Orthogonal Contrasts   289
11 Analysis of Variance: Further Issues   293
Factorial ANOVA   293
   Other Rationales for Multiple Factors   294
   Using the Two-Factor ANOVA Tool   297
The Meaning of Interaction   299
   The Statistical Significance of an Interaction   300
   Calculating the Interaction Effect   302
The Problem of Unequal Group Sizes   307
   Repeated Measures: The Two Factor Without Replication Tool   309
Excel’s Functions and Tools: Limitations and Solutions   310
   Mixed Models   312
   Power of the F Test   312
12 Experimental Design and ANOVA   315
Crossed Factors and Nested Factors   315
   Depicting the Design Accurately   317
   Nuisance Factors   317
Fixed Factors and Random Factors   318
   The Data Analysis Add-In’s ANOVA Tools   319
   Data Layout   320
Calculating the F Ratios   322
   Adapting the Data Analysis Tool for a Random Factor   322
   Designing the F Test   323
   The Mixed Model: Choosing the Denominator   325
   Adapting the Data Analysis Tool for a Nested Factor   326
   Data Layout for a Nested Design   327
   Getting the Sums of Squares   328
   Calculating the F Ratio for the Nesting Factor   329
13 Statistical Power   331
Controlling the Risk   331
   Directional and Nondirectional Hypotheses   332
   Changing the Sample Size   332
   Visualizing Statistical Power   333
   Quantifying Power   335
The Statistical Power of t-Tests   337
   Nondirectional Hypotheses   338
   Making a Directional Hypothesis   340
   Increasing the Size of the Samples   341
   The Dependent Groups t-Test   342
The Noncentrality Parameter in the F Distribution   344
   Variance Estimates   344
   The Noncentrality Parameter and the Probability Density Function   348
Calculating the Power of the F Test   350
   Calculating the Cumulative Density Function   350
   Using Power to Determine Sample Size   352
14 Multiple Regression Analysis and Effect Coding: The Basics   355
Multiple Regression and ANOVA   356
   Using Effect Coding   358
   Effect Coding: General Principles   358
   Other Types of Coding   359
Multiple Regression and Proportions of Variance   360
   Understanding the Segue from ANOVA to Regression   363
   The Meaning of Effect Coding   365
Assigning Effect Codes in Excel   368
Using Excel’s Regression Tool with Unequal Group Sizes   370
Effect Coding, Regression, and Factorial Designs in Excel   372
   Exerting Statistical Control with Semipartial Correlations   374
   Using a Squared Semipartial to Get the Correct Sum of Squares   376
Using Trend() to Replace Squared Semipartial Correlations   377
   Working With the Residuals   379
   Using Excel’s Absolute and Relative Addressing to Extend the Semipartials   381
15 Multiple Regression Analysis and Effect Coding: Further Issues   385
Solving Unbalanced Factorial Designs Using Multiple Regression   385
   Variables Are Uncorrelated in a Balanced Design   386
   Variables Are Correlated in an Unbalanced Design   388
   Order of Entry Is Irrelevant in the Balanced Design   388
   Order Entry Is Important in the Unbalanced Design   391
   About Fluctuating Proportions of Variance   393
Experimental Designs, Observational Studies, and Correlation   394
Using All the LINEST() Statistics   397
   Using the Regression Coefficients   398
   Using the Standard Errors   398
   Dealing with the Intercept   399
   Understanding LINEST()’s Third, Fourth, and Fifth Rows   400
   Getting the Regression Coefficients   406
   Getting the Sum of Squares Regression and Residual   410
   Calculating the Regression Diagnostics   412
   How LINEST() Handles Multicollinearity   416
   Forcing a Zero Constant   421
   The Excel 2007 Version   422
   A Negative R2?   425
Managing Unequal Group Sizes in a True Experiment   428
Managing Unequal Group Sizes in Observational Research   430
16 Analysis of Covariance: The Basics   433
The Purposes of ANCOVA   434
   Greater Power   434
   Bias Reduction   434
Using ANCOVA to Increase Statistical Power   435
   ANOVA Finds No Significant Mean Difference   436
   Adding a Covariate to the Analysis   437
Testing for a Common Regression Line   445
Removing Bias: A Different Outcome   447
17 Analysis of Covariance: Further Issues   453
Adjusting Means with LINEST() and Effect Coding   453
Effect Coding and Adjusted Group Means   458
Multiple Comparisons Following ANCOVA   461
   Using the Scheffé Method   462
   Using Planned Contrasts   466
The Analysis of Multiple Covariance   468
   The Decision to Use Multiple Covariates   469
   Two Covariates: An Example   470
Index   473