Table of Contents
Using Excel for Statistical Analysis
Telling an Interval Value from a Text Value
Charting Numeric Variables in Excel
Understanding Frequency Distributions
Building a Frequency Distribution from a Sample
Building Simulated Frequency Distributions
2. How Values Cluster Together
Understanding Functions, Arguments, and Results
Understanding Formulas, Results, and Formats
Getting the Mode of Categories with a Formula
From Central Tendency to Variability
3. Variability: How Values Disperse
Measuring Variability with the Range
The Concept of a Standard Deviation
Thinking in Terms of Standard Deviations
Calculating the Standard Deviation and Variance
Population Parameters and Sample Statistics
4. How Variables Move Jointly: Correlation
Removing the Effects of the Scale
Getting the Regression Formula
Using TREND() for Multiple Regression
Understanding “Best Combination”
A Technical Note: Matrix Algebra and Multiple Regression in Excel
Moving on to Statistical Inference
5. How Variables Classify Jointly: Contingency Tables
Understanding One-Way Pivot Tables
The Binomial Distribution Formula
Using the BINOM.INV() Function
Understanding Two-Way Pivot Tables
Probabilities and Independent Events
Testing the Independence of Classifications
Summarizing the Chi-Square Functions
6. Telling the Truth with Statistics
Problems with Excel’s Documentation
A Context for Inferential Statistics
Understanding Internal Validity
The F-Test Two-Sample for Variances
7. Using Excel with the Normal Distribution
Characteristics of the Normal Distribution
Excel Functions for the Normal Distribution
Confidence Intervals and the Normal Distribution
The Meaning of a Confidence Interval
Constructing a Confidence Interval
Excel Worksheet Functions That Calculate Confidence Intervals
Using CONFIDENCE.NORM() and CONFIDENCE()
Using the Data Analysis Add-in for Confidence Intervals
Confidence Intervals and Hypothesis Testing
8. Testing Differences Between Means: The Basics
Using the Standard Error of the Mean
Using the t-Test Instead of the z-Test
Understanding Statistical Power
9. Testing Differences Between Means: Further Issues
Using Excel’s T.DIST() and T.INV() Functions to Test Hypotheses
Making Directional and Nondirectional Hypotheses
Using Hypotheses to Guide Excel’s t-Distribution Functions
Completing the Picture with T.DIST()
Degrees of Freedom in Excel Functions
Using the Data Analysis Add-in t-Tests
10. Testing Differences Between Means: The Analysis of Variance
Using Excel’s F Worksheet Functions
Using F.DIST() and F.DIST.RT()
Multiple Comparison Procedures
11. Analysis of Variance: Further Issues
Other Rationales for Multiple Factors
Using the Two-Factor ANOVA Tool
The Statistical Significance of an Interaction
Calculating the Interaction Effect
The Problem of Unequal Group Sizes
Repeated Measures: The Two Factor Without Replication Tool
Excel’s Functions and Tools: Limitations and Solutions
12. Multiple Regression Analysis and Effect Coding: The Basics
Effect Coding: General Principles
Multiple Regression and Proportions of Variance
Understanding the Segue from ANOVA to Regression
Assigning Effect Codes in Excel
Using Excel’s Regression Tool with Unequal Group Sizes
Effect Coding, Regression, and Factorial Designs in Excel
Exerting Statistical Control with Semipartial Correlations
Using a Squared Semipartial to get the Correct Sum of Squares
Using TREND() to Replace Squared Semipartial Correlations
Using Excel’s Absolute and Relative Addressing to Extend the Semipartials
13. Multiple Regression Analysis: Further Issues
Solving Unbalanced Factorial Designs Using Multiple Regression
Variables Are Uncorrelated in a Balanced Design
Variables Are Correlated in an Unbalanced Design
Order of Entry Is Irrelevant in the Balanced Design
Order Entry Is Important in the Unbalanced Design
About Fluctuating Proportions of Variance
Experimental Designs, Observational Studies, and Correlation
Using All the LINEST() Statistics
Using the Regression Coefficients
Understanding LINEST()’s Third, Fourth, and Fifth Rows
Managing Unequal Group Sizes in a True Experiment
Managing Unequal Group Sizes in Observational Research
14. Analysis of Covariance: The Basics
Using ANCOVA to Increase Statistical Power
ANOVA Finds No Significant Mean Difference
Adding a Covariate to the Analysis
Testing for a Common Regression Line
Removing Bias: A Different Outcome
15. Analysis of Covariance: Further Issues
Adjusting Means with LINEST() and Effect Coding
Effect Coding and Adjusted Group Means
Multiple Comparisons Following ANCOVA
The Analysis of Multiple Covariance