R for Microsoft® Excel Users: Making the Transition for Statistical Analysis

 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

Table of Contents:
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