Statistical Analysis Using Excel 2007

Larry A. Pace, Ph.D., is an award-winning professor of statistics, management, and psychology. He is currently a professor of psychology in the College of Undergraduate Studies at Argosy University Online, where he teaches undergraduate and graduate courses in statistics and quantitative research methods. Previously, he was a professor of psychology and chair of the behavioral sciences department at Anderson University in Anderson, SC. Dr. Pace has taught behavioral and business statistics and research methods courses for Argosy University, Anderson University, Clemson University, Keiser University, Ashford University, Capella University, Austin Peay State University, Louisiana Tech University, and Rensselaer Polytechnic Institute. He has written 15 books and more than 80 articles, chapters, and reviews, in addition to hundreds of online tutorials and reviews.   He earned the Ph.D. in psychological measurement with a content major in industrial/organizational psychology from the University of Georgia. Dr. Pace has served on the faculties of Argosy University, Anderson University, Clemson University, Louisiana Tech University, Louisiana State University in Shreveport, the University of Tennessee, Rochester Institute of Technology, and Rensselaer Polytechnic Institute. He was a visiting scholar in Cornell University’s Johnson Graduate School of Management and the New York State School of Industrial and Labor Relations. He worked for Furman University as an instructional development consultant, for Xerox Corporation as an internal consultant and organizational effectiveness manager, and for a private consulting firm as an organizational development consultant. Among Dr. Pace’s consulting clients have been AT&T, Compaq Computer Corporation, International Paper, Lucent Technologies, and Xerox Corporation.  

Preface and Acknowledgements  iii

About the Author  v

Brief Contents  vi

Table of Contents  vi

 

1    A Statistician’s View of Excel 2007  6

About This Book  6

Using a Spreadsheet 6

The Excel User Interface  6

The Anatomy of a Worksheet 6

Working with Worksheet Cells  6

Working with Ranges and Names  6

Relative and Absolute References  6

Setting Up a Data Structure  6

Identifying Errors and Sizing Columns  6

Doing Math in Excel 6

A Date with a Helpful Function  6

Important Terms and a Look Ahead  6

Chapter 1 Exercises  6

 

2  Tables and Descriptive Statistics  6

Working with Structured Data  6

Built-In Functions and Tools  6

The Formula View   6

Summary Statistics Available From the AutoSum Tool 6

Setting up a Table  6

The Total Row   6

Customizing the Status Bar  6

Additional Statistical Functions  6

Installing the Analysis ToolPak  6

Example Data  6

Splitting a Worksheet 6

Freezing Panes  6

Using Functions for Descriptive Statistics  6

Descriptive Statistics in the Analysis ToolPak  6

Describing the Descriptive Statistics  6

A Note on Samples, Populations, and Terminology  6

Frequency Distributions  6

Simple Frequency Distributions  6

Grouped Frequency Distributions  6

Finding Percentiles and Percentile Ranks  6

Chapter 2 Exercises  6

 

3   Charts, Graphs, and More Tables  6

Pie Charts  6

Bar Charts  6

Histograms  6

Line Graphs  6

Scatterplots  6

Pivot Tables  6

Using the Pivot Table to Summarize Quantitative Data  6

The Charts Excel Does Not Readily Produce  6

Chapter 3 Exercises  6

 

4   Discrete Probability Distributions  6

The Binomial Probability Distribution  6

A Template for Binomial Probabilities  6

The Mean and Variance of a Binomial Distribution  6

Application of the Binomial Probability Distribution  6

Normal Approximation to the Binomial Distribution  6

The Poisson Probability Distribution  6

A Template for Poisson Probabilities  6

Application of the Poisson Probability Distribution  6

Chapter 4 Exercises  6

 

5    Working with z Scores and the Standard Normal Distribution  6

The z Score  6

Estimating Percentiles Using z Scores  6

Chebyshev’s Inequality and the Empirical Rule  6

The NORMSINV function  6

The Standard Normal Distribution  6

The Central Limit Theorem   6

Finding Areas under the Standard Normal Curve  6

Calculating z Scores with the STANDARDIZE Function  6

Finding the Area to the Left of a z Score  6

Finding the Area to the Right of a z Score  6

Determining the Area Between two z Scores  6

Finding the z Score for a Given Probability  6

Comparing Empirical and Theoretical Probabilities  6

Finding a Cutoff Score Using Excel 6

Chapter 5 Exercises  6

 

6   Confidence Intervals and Introduction to Hypothesis Testing  6

Defining a Confidence Interval for the Population Mean  6

A Graphical Representation of a Confidence Interval 6

Using Excel’s CONFIDENCE Function  6

Determining Required Sample Size  6

Confidence Intervals Using the t Distribution  6

Reporting a Confidence Interval 6

Confidence Intervals for Proportions  6

An Introduction to Hypothesis Testing  6

The One-Sample z Test 6

A Confidence Interval for the One-Sample z Test 6

The Null Hypothesis and the Alternative Hypothesis  6

Type I and Type II Errors  6

Statistical Significance and Effect Size  6

One- and Two-Tailed Tests  6

Chapter 6 Exercises  6

 

7  t Tests for Means  6

The One-Sample t Test 6

Example Data  6

Confidence Interval for a One-Sample t Test 6

Independent-Samples t Test 6

Example Data  6

The Independent-Samples t Test in the Analysis ToolPak  6

An Independent-Samples t Test Template  6

Confidence Interval for the Difference between Means  6

Paired-Samples t Test 6

Example Data  6

A Paired-Samples t test Template  6

Paired-Samples t Test in the Analysis ToolPak  6

Confidence Interval for the Paired-Samples t Test 6

Effect Size for t Tests  6

Chapter 7 Exercises  6

 

8   Analysis of Variance  6

Example Data  6

One-Way ANOVA in the Analysis ToolPak  6

Effect Size for the One-Way ANOVA  6

An ANOVA Worksheet Template  6

Multiple Comparisons in Excel 6

The Fisher LSD Test 6

The Tukey HSD Test 6

Bonferroni Corrections  6

Other Post Hoc Tests  6

A Brief Introduction to Two-Way ANOVA  6

A Worked-Out Example of Two-Way ANOVA  6

Two-Way ANOVA in the Analysis ToolPak  6

Effect Size for the Two-Way ANOVA  6

More Advanced ANOVA Designs  6

Chapter 8 Exercises  6

 

9   Repeated-Measures ANOVA   6

Example Data  6

Repeated-measures ANOVA in the Analysis ToolPak  6

Multiple Comparisons in the Repeated-Measures ANOVA  6

A Worksheet Template for the Repeated-Measures ANOVA  6

Chapter 9 Exercises  6

 

10   Correlation and Regression  6

Covariance  6

The Correlation Coefficient and the Coefficient of Determination  6

The Regression Equation  6

Example Data  6

Correlation, Slope, Intercept, and Forecasting  6

Regression Analysis in the Analysis ToolPak  6

A Brief Introduction to Time Series and Forecasting  6

Linear Trend  6

Nonlinear Trend  6

An Introduction to the General Linear Model 6

Chapter 10 Exercises  6

 

11   Chi-Square Tests  6

Chi-Square Goodness-of-Fit Tests  6

Chi-Square Test of Goodness of Fit for Equal Expected Frequencies  6

Chi-Square Goodness-of-Fit Test Template for Equal Expected Frequencies  6

Chi-Square Goodness-of-Fit Test with Unequal Expected Frequencies  6

Chi-Square Test of Independence  6

Calculating Expected Frequencies for Chi-Square Tests of Independence  6

Using a Pivot Table for Cross Tabulations  6

Effect Size for Chi-Square Tests  6

Chapter 11 Exercises  6

 

12  Additional Nonparametric Tests  6

The Mann-Whitney U Test 6

Rationale and Procedure  6

Example  6

The Wilcoxon Matched-Pairs Signed-Ranks Test 6

Rationale and Procedure  6

Example  6

The Kruskal-Wallis Test 6

Rationale and Procedure  6

Example  6

Pairwise Comparisons for the Kruskal-Wallis Test 6

The Friedman Test 6

Rationale and Procedure  6

Example  6

Chapter 12 Exercises  6

 

Appendix A—Statistical Functions in Excel 6

 

Appendix B—Writing Statistical Results in APA Format  6

 

Appendix C—Answers to Odd-Numbered Exercises  6

 

Appendix D—Critical Values of q (the Studentized Range Statistic) 6

 

References  6

 

Index  6