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

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

by Conrad Carlberg
Released November 2016
Publisher(s): Que
ISBN: 9780134571881

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

 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

  1. About This E-Book
  2. Title Page
  3. Copyright Page
  4. Contents at a Glance
  5. Contents
  6. About the Author
  7. Acknowledgments
  8. We Want to Hear from You!
  9. Reader Services
  10. Introduction
  11. 1. Making the Transition
    1. Adjusting Your Expectations
      1. Analyzing Data: The Packages
      2. Storing and Arranging Data: Data Frames
    2. The User Interface
    3. Special Characters
      1. Using the Tilde
      2. Using the Assignment Operator <-
    4. Obtaining R
    5. Contributed Packages
    6. Running Scripts
    7. Importing Data into R from Excel
    8. Exporting Data from R to Excel
      1. Exporting via a CSV File
      2. Using the Direct Export
  12. 2. Descriptive Statistics
    1. Descriptive Statistics in Excel
      1. Using the Descriptive Statistics Tool
      2. Understanding the Results
      3. Using the Excel Descriptive Statistics Tool on R’s Pizza File
    2. Using R’s DescTools Package
    3. Entering Some Useful Commands
      1. Controlling the Type of Notation
      2. The Reported Statistics
      3. Running the Desc Function on Nominal Variables
    4. Running Bivariate Analyses with Desc
      1. Two Numeric Variables
      2. Breaking Down a Numeric Variable by a Factor
    5. Analyzing One Factor by Another: The Contingency Table
      1. The Pearson Chi-square
      2. The Likelihood Ratio
      3. The Mantel-Haenszel Chi-square
      4. Estimating the Strength of the Relationships
  13. 3. Regression Analysis in Excel and R
    1. Worksheet Functions
      1. The CORREL( ) Function
      2. The COVARIANCE.P( ) Function
      3. The SLOPE( ) Function
      4. The INTERCEPT( ) Function
      5. The RSQ( ) Function
      6. The LINEST( ) Function
      7. The TREND( ) Function
    2. Functions for Statistical Inference
      1. The T.DIST Functions
      2. The F.DIST Functions
    3. Other Sources of Regression Analysis in Excel
      1. The Regression Tool
      2. Chart Trendlines
    4. Regression Analysis in R
      1. Correlation and Simple Regression
      2. Analyzing a Multiple Regression Model
      3. Models Comparison in R
  14. 4. Analysis of Variance and Covariance in Excel and R
    1. Single-Factor Analysis of Variance
      1. Using Excel’s Worksheet Functions
      2. Using the ANOVA: Single Factor Tool
      3. Using the Regression Approach to ANOVA
    2. Single-Factor ANOVA Using R
      1. Setting Up Your Data
      2. Arranging for the ANOVA Table
      3. The Single-Factor ANOVA with Missing Values
    3. The Factorial ANOVA
      1. Balanced Two-Factor Designs in Excel
      2. Balanced Two-Factor Designs and the ANOVA Tool
      3. Using Regression with Two-Factor ANOVA Designs
      4. Analyzing Balanced Factorial Designs with R
    4. Analyzing Unbalanced Two-Factor Designs in Excel and R
      1. Dealing with the Ambiguity
      2. Specifying the Effects
    5. Multiple Comparison Procedures in Excel and R
      1. Tukey’s HSD Method
      2. The Newman-Keuls Method
      3. Using Scheffé Procedure in Excel and R
    6. Analysis of Covariance in Excel and R
      1. ANCOVA Using Regression in Excel
      2. ANCOVA in R
  15. 5. Logistic Regression in Excel and R
    1. Problems with Linear Regression and Nominal Variables
      1. Problems with Probabilities
      2. Using Odds Instead of Probabilities
      3. Using the Logarithms of the Odds
    2. From the Log Odds to the Probabilities
      1. Recoding Text Variables
      2. Defining Names
      3. Calculating the Logits
      4. Calculating the Odds
      5. Calculating the Probabilities
      6. Getting the Log Likelihood
    3. Deploying Solver
      1. Installing Solver
      2. Using Solver for Logistic Regression
    4. Statistical Tests in Logistic Regression
      1. R2 and t in Logistic Regression
      2. The Likelihood Ratio Test
      3. Constraints and Degrees of Freedom
    5. Logistic Regression with R’s mlogit Package
      1. Running the mlogit Package
      2. Comparing Models with mlogit
    6. Using R’s glm Function
  16. 6. Principal Components Analysis
    1. Principal Components Using Excel
      1. Navigating the Dialog Box
      2. The Principal Components Worksheet: The R Matrix and Its Inverse
      3. The Principal Components Worksheet: Eigenvalues and Eigenvectors
      4. Variable Communalities
      5. The Factor Scores
    2. Rotated Factors in Excel
      1. Rotated Factor Coefficients and Scores
    3. Principal Components Analysis Using R
      1. Preparing the Data
      2. Calling the Function
      3. The Varimax Rotation in R
  17. Index
  18. Code Snippets

Product information

  • Title: R for Microsoft® Excel Users: Making the Transition for Statistical Analysis
  • Author(s): Conrad Carlberg
  • Release date: November 2016
  • Publisher(s): Que
  • ISBN: 9780134571881