R for Microsoft (R) Excel Users

Introduction .................................. 1

1 Making the Transition ............................. 5

Adjusting Your Expectations .................................................6

Analyzing Data: The Packages .......................................................7

Storing and Arranging Data: Data Frames ............................................7

The User Interface .......................................................8

Special Characters ..............................................9

Using the Tilde .......................................................9

Using the Assignment Operator <? ................................11

Obtaining R .................................14

Contributed Packages .....................................16

Running Scripts.........................................18

Importing Data into R from Excel ................................19

Exporting Data from R to Excel ............................26

Exporting via a CSV File ...............................27

Using the Direct Export ......................................28

2 Descriptive Statistics ........................31

Descriptive Statistics in Excel .....................................32

Using the Descriptive Statistics Tool .........................33

Understanding the Results ...................................34

Using the Excel Descriptive Statistics Tool on R's Pizza File ...............................38

Using R's DescTools Package ...........................41

Entering Some Useful Commands ..........................................42

Controlling the Type of Notation ......................................43

The Reported Statistics ...................................................46

Running the Desc Function on Nominal Variables ........................55

Running Bivariate Analyses with Desc ................................56

Two Numeric Variables ..........................................57

Breaking Down a Numeric Variable by a Factor..............................63

Analyzing One Factor by Another: The Contingency Table ...............................72

The Pearson Chi-square ...............................76

The Likelihood Ratio .........................................79

The Mantel-Haenszel Chi-square ....................................80

Estimating the Strength of the Relationships .............................83

3 Regression Analysis in Excel and R .....................85

Worksheet Functions ..............................85

The CORREL( ) Function .................................86

The COVARIANCE.P( ) Function ......................................87

The SLOPE( ) Function ...............................................88

The INTERCEPT( ) Function ................................................91

The RSQ( ) Function .........................................93

The LINEST( ) Function ..................................95

The TREND( ) Function ..............................99

Functions for Statistical Inference ...............................100

The T.DIST Functions ..................................100

The F.DIST Functions....................................102

Other Sources of Regression Analysis in Excel ....................................104

The Regression Tool ...................................104

Chart Trendlines ..........................................108

Regression Analysis in R .....................110

Correlation and Simple Regression ..........................110

Analyzing a Multiple Regression Model ...............................114

Models Comparison in R ..........................................116

4 Analysis of Variance and Covariance in Excel and R ................121

Single-Factor Analysis of Variance ...............................122

Using Excel's Worksheet Functions .............................122

Using the ANOVA: Single Factor Tool ........................................124

Using the Regression Approach to ANOVA ..........................125

Single-Factor ANOVA Using R ...........................127

Setting Up Your Data...................................127

Arranging for the ANOVA Table ......................................129

The Single-Factor ANOVA with Missing Values ..........................131

The Factorial ANOVA .............................................................134

Balanced Two-Factor Designs in Excel .................................135

Balanced Two-Factor Designs and the ANOVA Tool .................137

Using Regression with Two-Factor ANOVA Designs ....................139

Analyzing Balanced Factorial Designs with R ..........................145

Analyzing Unbalanced Two-Factor Designs in Excel and R ................148

Dealing with the Ambiguity ................152

Specifying the Effects ........................157

Multiple Comparison Procedures in Excel and R ...........................158

Tukey's HSD Method .........................159

The Newman-Keuls Method .................................163

Using Scheffe Procedure in Excel and R............................166

Analysis of Covariance in Excel and R .........................170

ANCOVA Using Regression in Excel ..................170

ANCOVA in R ................173

5 Logistic Regression in Excel and R ..........179

Problems with Linear Regression and Nominal Variables .................180

Problems with Probabilities ....................181

Using Odds Instead of Probabilities ..........................184

Using the Logarithms of the Odds ................185

From the Log Odds to the Probabilities .............187

Recoding Text Variables ..................188

Defining Names ........................188

Calculating the Logits ...................189

Calculating the Odds .............................189

Calculating the Probabilities ................190

Getting the Log Likelihood ................190

Deploying Solver ................192

Installing Solver ..........................192

Using Solver for Logistic Regression......................193

Statistical Tests in Logistic Regression ................................196

R2 and t in Logistic Regression .........................196

The Likelihood Ratio Test ......................................198

Constraints and Degrees of Freedom ...........................201

Logistic Regression with R's mlogit Package ........................202

Running the mlogit Package ................................202

Comparing Models with mlogit .....................208

Using R's glm Function ...................208

6 Principal Components Analysis ........211

Principal Components Using Excel ................212

Navigating the Dialog Box ....................213

The Principal Components Worksheet: The R Matrix and Its Inverse......216

The Principal Components Worksheet: Eigenvalues and Eigenvectors....219

Variable Communalities .........................222

The Factor Scores ..............................222

Rotated Factors in Excel ..................................224

Rotated Factor Coefficients and Scores ...............................226

Principal Components Analysis Using R ......................................227

Preparing the Data ......................227

Calling the Function ................................229

The Varimax Rotation in R .............................232

TOC, 9780789757852, 10/21/2016