Statistical Analysis

Statistical Analysis : Microsoft Excel 2013

3.25 (8 ratings by Goodreads)

Free delivery worldwide

Available. Dispatched from the UK in 3 business days
When will my order arrive?


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
show more

Product details

  • Paperback | 512 pages
  • 175.26 x 228.6 x 27.94mm | 771.1g
  • Que Corporation,U.S.
  • Indianapolis, United States
  • English
  • black & white tables, figures
  • 0789753111
  • 9780789753113
  • 758,848

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 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 Scheffe 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 Scheffe 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
show more

About Conrad George Carlberg

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.
show more

Rating details

8 ratings
3.25 out of 5 stars
5 12% (1)
4 38% (3)
3 25% (2)
2 12% (1)
1 12% (1)
Book ratings by Goodreads
Goodreads is the world's largest site for readers with over 50 million reviews. We're featuring millions of their reader ratings on our book pages to help you find your new favourite book. Close X