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 xviWhat''s in This Book xvi 1 About Variables and Values 1 Variables and Values 1 Recording Data in Lists 2Scales of Measurement 4 Category Scales 5 Numeric Scales 7 Telling an Interval Value from a Text Value 8Charting Numeric Variables in Excel 10 Charting Two Variables 10Understanding 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 36Calculating the Median 41 Choosing to Use the Median 41Calculating the Mode 42 Getting the Mode of Categories with a Formula 47From Central Tendency to Variability 54 3 Variability: How Values Disperse 55 Measuring Variability with the Range 56The Concept of a Standard Deviation 58 Arranging for a Standard 59 Thinking in Terms of Standard Deviations 60Calculating the Standard Deviation and Variance 62 Squaring the Deviations 65 Population Parameters and Sample Statistics 66 Dividing by N - 1 66Bias in the Estimate 68 Degrees of Freedom 69Excel''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 88Using Correlation 90 Removing the Effects of the Scale 91 Using the Excel Function 93 Getting the Predicted Values 95 Getting the Regression Formula 96Using 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 106Moving on to Statistical Inference 107 5 How Variables Classify Jointly: Contingency Tables 109 Understanding One-Way Pivot Tables 109 Running the Statistical Test 112Making Assumptions 117 Random Selection 118 Independent Selections 119 The Binomial Distribution Formula 120 Using the BINOM INV() Function 121Understanding Two-Way Pivot Tables 127 Probabilities and Independent Events 130 Testing the Independence of Classifications 131The Yule Simpson effect 137Summarizing 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 152Problems with Excel''s Documentation 156The 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 176Excel Functions for the Normal Distribution 177 The NORM DIST() Function 177 The NORM INV() Function 180Confidence 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 194The 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 208Using 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() 237Using the T TEST() Function 238 Degrees of Freedom in Excel Functions 238 Equal and Unequal Group Sizes 239 The T TEST() Syntax 242Using 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? 263The Logic of ANOVA 265 Partitioning the Scores 265 Comparing Variances 268 The F Test 273Using 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 279Unequal Group Sizes 280Multiple 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 297The Meaning of Interaction 299 The Statistical Significance of an Interaction 300 Calculating the Interaction Effect 302The Problem of Unequal Group Sizes 307 Repeated Measures: The Two Factor Without Replication Tool 309Excel''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 317Fixed Factors and Random Factors 318 The Data Analysis Add-In''s ANOVA Tools 319 Data Layout 320Calculating 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 335The 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 342The Noncentrality Parameter in the F Distribution 344 Variance Estimates 344 The Noncentrality Parameter and the Probability Density Function 348Calculating 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 359Multiple Regression and Proportions of Variance 360 Understanding the Segue from ANOVA to Regression 363 The Meaning of Effect Coding 365Assigning Effect Codes in Excel 368Using Excel''s Regression Tool with Unequal Group Sizes 370Effect 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 376Using 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 393Experimental Designs, Observational Studies, and Correlation 394Using 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? 425Managing Unequal Group Sizes in a True Experiment 428Managing Unequal Group Sizes in Observational Research 430 16 Analysis of Covariance: The Basics 433 The Purposes of ANCOVA 434 Greater Power 434 Bias Reduction 434Using ANCOVA to Increase Statistical Power 435 ANOVA Finds No Significant Mean Difference 436 Adding a Covariate to the Analysis 437Testing for a Common Regression Line 445Removing Bias: A Different Outcome 447 17 Analysis of Covariance: Further Issues 453 Adjusting Means with LINEST() and Effect Coding 453Effect Coding and Adjusted Group Means 458Multiple Comparisons Following ANCOVA 461 Using the Scheffé Method 462 Using Planned Contrasts 466The Analysis of Multiple Covariance 468 The Decision to Use Multiple Covariates 469 Two Covariates: An Example 470 Index 473.