Register your product to gain access to bonus material or receive a coupon.
This eBook includes the following formats, accessible from your Account page after purchase:
EPUB The open industry format known for its reflowable content and usability on supported mobile devices.
PDF The popular standard, used most often with the free Acrobat® Reader® software.
This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.
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
Download supporting files for this book:
• Chapter 1 (227 KB .xlsx)
• Chapter 2 (611 KB .xlsx)
• Chapter 3 (321 KB .xlsx)
• Chapter 4 (371 KB .xlsx)
• Chapter 5 (138 KB .xlsx)
• Chapter 6 (57 KB .xlsx)
• PCA (57 KB .xlsm)
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 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