(A) Introducing regression
Video 1—Talking about regression on The Daily Show
In previous years we held class on the softball field, where each student was asked to hit three softballs as far as they could. The distance of each hit would then be measured, and some of these data are available for download here. This is a great data-set, containing over 600 observations from a wide variety of students.
Figure 1—Recalling the Softball Data
Suppose that one randomly-chosen student from our class was about to step to the plate and hit a softball, and we are asked to predict how far they will hit. We don't know which student this could be. It could be a male or female; it could be an experienced player or a foreigner who doesn't even know what baseball is. All we know is that this class is "like" previous classes, so the aforementioned data might be useful in predicting this person's hitting distance.
If I asked you to use the data to come up with one, single number for a prediction, you will likely take the average hitting distance from the data and use that as the prediction. That average is 138.76 feet. If we really do not know anything about the student who will hit, that's about a good of a prediction as you can get.
Now suppose that the student is chosen, and we are allowed to observe their gender and ask them how many years of experience they have playing on a baseball or softball team. Perhaps it will be a male with no experience or a female with ten. Should that new information useful for developing a new, better prediction, one tailored to that person's gender and experience? Absolutely, and the ideal method for doing so is here.
The first step in a regression is specifying an equation by which you predict a dependent variable with one or more independent or explanatory variables. The equation should always have an intercept. A good equation for predicting softball hitting distances is that below.
[Equation 1] predicted distance = a0 + a1(male) + a2(experience)
Now all we need are the values of the coefficients a0, a1, and a3. This is the easy part, for we can ask Excel to calculate them for us, as shown in Video 2 below.
How do you know Excel will calculate good values that generate accurate predictions. That I leave for future classes or articles. For now, just know that Excel will choose good coefficients that yield accurate—though certaintly imperfect—predictions.
Video 2—Regression Demonstration
As Video 1 demonstrates, the regression estimate is shown in Equation 2, below.
[Equation 2] predicted distance = 75.31 + 67.71(male) + 3.45(experience)
(B) Using the regression to calculate predictions
This equation can predict the hitting distance for either gender and for any years of ball experience. A female with no experience is expected to hit around 75.31 feet, which happens to equal the intercept because both male and experience equal zero, leaving nothing but the intercept in the equation. Compare this to a male with no experience, who hits about 143.02 feet. Notice the increase in distance when moving from a female to male (both with no experience) increases distance by exactly 67.71 (the coefficient for male). This means that any male is expected to hit 67.71 feet further than any female, if they both have the same ball experience. Finally, a male with five years of experience hits 160.27 feet.
[Equation 2: male = 0, experience = 0] Predicted Distance = 75.31 + 67.71(0) + 3.45(0) = 75.31
[Equation 3: male = 1, experience = 0] Predicted Distance = 75.31 + 67.71(1) + 3.45(0) = 143.02
[Equation 4: male = 1, experience = 5] Predicted Distance = 75.31 + 67.71(1) + 3.45(5) = 160.27
(C) The effect of an explanatory variable on the dependent variable
From this regression we can determine the impact of each explanatory variable (male and experience) on hitting distance. This is a two-step process: the first step will be mysterious for the present but the second step will be obvious. Consider first the impact of gender: how does hitting distance change when the hitter changes from a female to a male, holding all other variables constant? The phrase "holding all other variables constant" can be succinctly stated in latin as ceteris paribus, and I will use this phrase frequently. This means we compare a male relative to a female when they both have the same hitting distance.
- First, we ask whether the variable is statistically significant. A regression will never give you a coefficient of exactly 0, but often the real effect of the variable might be zero, and we need a rule to determine this is the case. While it will be elaborated upon later, for now, whenever the p-value for a coefficient is less than 0.05 we say the coefficient is not really zero, and should be taken seriously. As shown in the regression output below, this is the case for both male and experience, so we say they are statistically significant, and take whatever their coefficients are seriously.
- Given an explanatory variable is statistically significant, its coefficient signifies the change in the dependent variable should the explanatory variable increase by one unit. The coefficient for male is 67.71, meaning distance increases by 67.71 feet when male increases from 0 to 1. On average, males hit 67.71 feet further than females. The coefficient of 3.45 for experience shows that for every additional year of experience one's hitting distance will rise about 3.45 feet. Consequently, when experience rises from 0 to 5 in Table 1, distance rises by (3.45)(5) = 17.25.
Figure 2—Regression Output
from regression predicted distance = a0 + a1(male) + a2(experience)
(D) Graphing Regression Predictions
If one were interested in understanding the impact of experience on hitting distance they are likely to want a graph. The regression in Equation 2 can be used to plot how predicted hitting distance (y-axis) increases with experience (on the x-axis), but we must graw different lines for males and females because their predicted distances will differ. The graph must plot the distance for males only, females only, or both. Accounting for both genders is a more complete answer, and so this graph will contain two scenarios: one for males and one for females.
[Equation 5] predicted distance for males = 75.31 + 67.71(1) + 3.45(experience) = 143.02 + 3.45(experience)
[Equation 6] predicted distance for females = 75.31 + 67.71(0) + 3.45(experience) = 75.31 + 3.45(experience)
The graph then requires us to substitute various values for experience (I chose zero through twenty in increments of one) and calculate the corresponding predicted distance, and plotting the result.
Figure 3—Plotting Equations 5 & 6
(E) Using the SUMPRODUCT formula in Excel
The previous section used the following regression to plot the relationship between experience and hitting distances. One can plot the points by hand, but the SUMPRODUCT formula in Excel allows us to alter variable values and quickly develop new predictions. This section teaches you how to do this.
[Equation 7] predicted distance = 75.31 + 67.71(male) + 3.45(experience)
What we want is to be able to plug different values in for male and experience in Excel, and then to experiment with changes in the variables without having to recalculate the equation. The SUMPRODUCT formula is ideal for that, and an illustration of how the SUMPRODUCT formula works is shown below. This formula can be particularly useful in other applications too, so I would encourage you to understand why it works instead of just memorizing how to use it for regression.
Figure 4—Illustration of SUMPRODUCT Formula in Excel
All regressions give you a column of coefficients, like the ones below. What you need to know is copy and paste these coefficients below the regression output, create a new column, and below it use a SUMPRODUCT formula to multiply each coefficient by its variable's value (multiplying the intercept by 1) and sum the products. This is illustrated below, where I created a new table containing a SUMPRODUCT formula, predicting the hitting distance for a female with 12 years of experience. Notice that I can easily plug in different values for the two coefficients and Excel will automatically calculate the new prediction. That is a convenience we should exploit.
Figure 5—Using SUMPRODUCT With Regression Output
Exploring the effect of an explanatory variable
Suppose we want to know what happens if experience increases by five, or how many years of experience a female must acquire before she can hit as far as a male with no experience. I find these questions can be easily answered using two tables like that in Figure 2. The video below demonstrates this exercise.
