Statistical Analysis: Microsoft Excel 2016

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