21%

off

off

# Statistical Analysis : Microsoft Excel 2013

Free delivery worldwide

Available.
Dispatched from the UK in 3 business days

When will my order arrive?

## Description

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

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
- 13 Apr 2014
- Pearson Education (US)
- 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 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 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

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

show more