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

About

Features

Students will:

• Discover when and how to choose Excel for analytics, when and how to use R, and when and how to use them together
• Get accurate, actionable answers from sales data, biomedical measurements, online surveys, political polling, and other enormous collections of data
• Master advanced Excel and R techniques, and apply them to real business and research problems



Description

• Copyright 2017
• Dimensions: 7" x 9-1/8"
• Pages: 272
• Edition: 1st

• Book
• ISBN-10: 0-7897-5785-0
• ISBN-13: 978-0-7897-5785-2

 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



Downloads

Downloads

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)



Extras

Author's Site

For more from Conrad Carlberg, please visit his site at conradcarlberg.com.



Sample Content

Online Sample Chapter

Descriptive Statistics

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

    



Updates

Submit Errata

