Statistical Analysis: Microsoft Excel 2013

Use Excel 2013’s statistical tools to transform your data into knowledge Conrad Carlberg shows how to use Excel 2013 to perform core statistical tasks every business professional, student, and researcher should master. Using real-world examples, Carlberg helps you choose the right technique for each problem and get the most out of Excel’s statistical features, including recently introduced consistency functions. 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, this edition adds two more chapters on inferential statistics, covering crucial topics ranging from experimental design to the statistical power of F tests.   Becoming an expert with Excel statistics has never been easier! You’ll find crystal-clear instructions, insider insights, and complete step-by-step projects—all complemented by extensive web-based resources. Master Excel’s most useful descriptive and inferential statistical tools Tell the truth with statistics—and recognize when others don’t Accurately summarize sets of values 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 Use ANOVA to test differences between more than two means Explore statistical power by manipulating mean differences, standard errors, directionality, and alpha Take advantage of Recommended PivotTables, Quick Analysis, and other Excel 2013 shortcuts

Table of Contents:
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