# R for Microsoft® Excel Users: Making the Transition for Statistical Analysis, 1st edition

## Overview

Microsoft Excel can perform many statistical analyses, but thousands of business users and analysts are now reaching its limits. R, in contrast, can perform virtually any imaginable analysis—if you can get over its learning curve. In R for Microsoft® Excel Users, Conrad Carlberg shows exactly how to get the most from both programs.

Drawing on his immense experience helping organizations apply statistical methods, Carlberg reviews how to perform key tasks in Excel, and then guides you through reaching the same outcome in R—including which packages to install and how to access them. Carlberg offers expert advice on when and how to use Excel, when and how to use R instead, and the strengths and weaknesses of each tool.

Writing in clear, understandable English, Carlberg combines essential statistical theory with hands-on examples reflecting real-world challenges. By the time you’ve finished, you’ll be comfortable using R to solve a wide spectrum of problems—including many you just couldn’t handle with Excel.

• Smoothly transition to R and its radically different user interface

• Leverage the R community’s immense library of packages

• Efficiently move data between Excel and R

• Use R’s DescTools for descriptive statistics, including bivariate analyses

• Perform regression analysis and statistical inference in R and Excel

• Analyze variance and covariance, including single-factor and factorial ANOVA

• Use R’s mlogit package and glm function for Solver-style logistic regression

• Analyze time series and principal components with R and Excel

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

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

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

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

## For teachers

All the material you need to teach your courses.

Discover teaching material