Regression Analysis Microsoft Excel

Conrad Carlberg (www.conradcarlberg.com) is a nationally recognized expert on Quantitative analysis and on data analysis and management applications such as Microsoft Excel, SAS, and Oracle. He holds a Ph.D. in statistics from the University of Colorado and is a many-time recipient of Microsoft's Excel MVP designation. Carlberg is a Southern California native. After college he moved to Colorado, where he worked for a succession of startups and attended graduate school. He spent two years in the Middle East, teaching computer science and dodging surly camels. After finishing graduate school, Carlberg worked at US West (a Baby Bell) in product management and at Motorola. In 1995 he started a small consulting business that provides design and analysis services to companies that want to guide their business decisions by means of quantitative analysis-approaches that today we group under the term "analytics." He enjoys writing about those techniques and, in particular, how to carry them out using the world's most popular numeric analysis application, Microsoft Excel.

Introduction................................... 1

1 Measuring Variation: How Values Differ.......................... 5

How Variation Is Measured...........................................5

  Sum of Deviations..........................................................6

  Summing Squared Deviations...............................................7

  From the Sum of Squares to the Variance................................10

  Using the VAR.P( ) and VAR.S( ) Functions....................................11

The Standard Deviation................................................14

The Standard Error of the Mean............................................15

  About z-Scores and z-Values.................................................18

  About t-Values.....................................................................23

2 Correlation.........................................29

Measuring Correlation...........................................................................29

  Expressing the Strength of a Correlation.....................30

  Determining a Correlations Direction...................................32

Calculating Correlation.......................................................34

  Step One: The Covariance..................................34

  Watching for Signs........................................................36

From the Covariance to the Correlation Coefficient..........................38

  Using the CORREL( ) Function...................................................41

     Understanding Bias in the Correlation............................41

  Checking for Linearity and Outliers in the Correlation ........................44

  Avoiding a Trap in Charting.............................48

Correlation and Causation..............................................53

  Direction of Cause........................................54

  A Third Variable................................................55

Restriction of Range..........................................................................55

3 Simple Regression.....................................59

Predicting with Correlation and Standard Scores.........................60

  Calculating the Predictions............................61

  Returning to the Original Metric............................63

  Generalizing the Predictions........................................64

Predicting with Regression Coefficient and Intercept.................................65

  The SLOPE( ) Function........................................................65

  The INTERCEPT( ) Function.....................69

  Charting the Predictions......................