Statistical Analysis: Microsoft Excel 2016

Description

USE EXCEL’S STATISTICAL TOOLS TO TRANSFORMYOUR 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.

您将学习如何使用相关性和回归, 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

Downloads

Downloads

Download the Workbook Files(10.2 MB .zip)

Extras

Author's Site

Please visit the author's site atconradcarlberg.com/publications.html.

Sample Content

Online Sample Chapter

Cluster Analysis in Excel

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
的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
的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
的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
的Data Analysis Add-in’s Histogram .......................131
的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
的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
的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
的Unit Normal Distribution .......186
Excel Functions for the Normal Distribution ...................187
的NORM.DIST( ) Function ..........187
的NORM.INV( ) Function ...........190
Confidence Intervals and the Normal Distribution ..............192
的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
的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
的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
的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
的Logic of ANOVA ...........................301
Partitioning the Scores ................302
Comparing Variances ...................305
的F-Test ....................................309
Using Excel’s F Worksheet Functions .312
Using F.DIST() and F.DIST.RT() .....312
Using F.INV() and FINV() ..............314
的F-Distribution ........................315
Unequal Group Sizes ..........................316
Multiple Comparison Procedures .......318
的Scheffé Procedure .................320
Planned Orthogonal Contrasts .....324
12个方差分析:进一步问题…329
Factorial ANOVA .................................329
Other Rationales for Multiple Factors ....................330
Using the Two-Factor ANOVA Tool ..........................333
的Meaning of Interaction ...............335
的Statistical Significance of an Interaction ................336
Calculating the Interaction Effect 338
的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
的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
的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
的Statistical Power of t-Tests ..........382
Nondirectional Hypotheses ..........382
Making a Directional Hypothesis .385
Increasing the Size of the Samples .......................387
的Dependent Groups t-Test ......387
的Noncentrality Parameter in the F-Distribution ................389
Variance Estimates ......................389
的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
的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
的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
的Purposes of ANCOVA ...................480
Greater Power .............................480
减少偏见 ............................. 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协方差分析:进一步的问题……..........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
的Analysis of Multiple Covariance ...514
的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

Updates

Submit Errata