Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

O'Reilly logo
Regression Analysis Microsoft® Excel®

Book Description


This is today’s most complete guide to regression analysis with Microsoft® Excel for any business analytics or research task. Drawing on 25 years of advanced statistical experience, Microsoft MVP Conrad Carlberg shows how to use Excel’s regression-related worksheet functions to perform a wide spectrum of practical analyses.

Carlberg clearly explains all the theory you’ll need to avoid mistakes, understand what your regressions are really doing, and evaluate analyses performed by others. From simple correlations and t-tests through multiple analysis of covariance, Carlberg offers hands-on, step-by-step walkthroughs using meaningful examples.

He discusses the consequences of using each option and argument, points out idiosyncrasies and controversies associated with Excel’s regression functions, and shows how to use them reliably in fields ranging from medical research to financial analysis to operations.

You don’t need expensive software or a doctorate in statistics to work with regression analyses. Microsoft Excel has all the tools you need—and this book has all the knowledge!

  • Understand what regression analysis can and can’t do, and why

  • Master regression-based functions built into all recent versions of Excel

  • Work with correlation and simple regression

  • Make the most of Excel’s improved LINEST() function

  • Plan and perform multiple regression

  • Distinguish the assumptions that matter from the ones that don’t

  • Extend your analysis options by using regression instead of traditional analysis of variance

  • Add covariates to your analysis to reduce bias and increase statistical power

  • 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. Measuring Variation: How Values Differ
      1. How Variation Is Measured
      2. The Standard Deviation
      3. The Standard Error of the Mean
    12. 2. Correlation
      1. Measuring Correlation
      2. Calculating Correlation
      3. Correlation and Causation
      4. Restriction of Range
    13. 3. Simple Regression
      1. Predicting with Correlation and Standard Scores
      2. Predicting with Regression Coefficient and Intercept
      3. Shared Variance
      4. The TREND() Function
      5. Partial and Semipartial Correlations
    14. 4. Using the LINEST() Function
      1. Array-Entering LINEST()
      2. Comparing LINEST() to SLOPE() and INTERCEPT()
      3. The Standard Error of a Regression Coefficient
      4. The Squared Correlation, R2
      5. The Standard Error of Estimate
      6. Understanding LINEST()’s F-ratio
      7. The General Linear Model, ANOVA, and Regression Analysis
      8. Other Ancillary Statistics from LINEST()
    15. 5. Multiple Regression
      1. A Composite Predictor Variable
      2. Understanding the Trendline
      3. Mapping LINEST()’s Results to the Worksheet
      4. Building a Multiple Regression Analysis from the Ground Up
      5. Using the Standard Error of the Regression Coefficient
      6. Using the Models Comparison Approach to Evaluating Predictors
      7. Estimating Shrinkage in R2
    16. 6. Assumptions and Cautions Regarding Regression Analysis
      1. About Assumptions
      2. The Straw Man
      3. Coping with Nonlinear and Other Problem Distributions
      4. The Assumption of Equal Spread
      5. Unequal Variances and Sample Sizes
    17. 7. Using Regression to Test Differences Between Group Means
      1. Dummy Coding
      2. Effect Coding
      3. Orthogonal Coding
      4. Factorial Analysis
      5. Statistical Power, Type I and Type II Errors
      6. Coping with Unequal Cell Sizes
    18. 8 The Analysis of Covariance
      1. Contrasting the Results
      2. Structuring a Conventional ANCOVA
      3. Structuring an ANCOVA Using Regression
      4. Checking for a Common Regression Line
      5. Testing the Adjusted Means: Planned Orthogonal Coding in ANCOVA
      6. ANCOVA and Multiple Comparisons Using the Regression Approach
      7. Multiple Comparisons via Planned Nonorthogonal Contrasts
      8. Multiple Comparisons with Post Hoc Nonorthogonal Contrasts
    19. Index