Hopefully the above example has whetted your appetite to want to learn more.
I can’t make it any less scary sounding than that. To complete that final step will require some linear algebra which is just a scary word for matrix algebra. Then we can look at a regression with multiple variables in an un-idealized scenario and build a macro to run the regression. We can calculate it out step by step in Excel. To see how, I want to look at a single variable regression (single variable with an intercept) in an un-idealized situation (meaning we don’t actually know how many independent variables there are). Now, if you think about it, it’s pretty interesting that we can actually take a final score and its components and break them up to calculate the value of each component.
#EXCEL LINEAR REGRESSION MAC FREE#
With y meaning Final score, being Shots, being 3-pointers, and being Free throws. And the dependent variable will usually be y so we can substitute y in for Fs: Each variable will have a coefficient usually represented by the character beta. In the general case we like to use x for the independent variables with a subscript indicating what number they are in the position.
So we have explained how the final score operates and to calculate it all we need to do is observe the number of each type of score, neat! The result of that regression tells us that Baskets are worth 2 points, 3 Pointers are worth 3 points (shocking!), and Free throws are worth 1 point. Let’s see if we can estimate how much each of those type of scores are worth. So how about something a little more complicated? Basketball! There are three ways to score in basketball: regular baskets, three-point shots, and free throws from fouls. Since the coefficient is 1 we can simply remove it from the equation (anything multiplied by 1 is equal to itself) leaving us with the following equation:įinal Score is equal to number of goals, easy peasy!
#EXCEL LINEAR REGRESSION MAC FULL#
This brings us back full circle to the equation we started with, our assumption was that the coefficient on goals is equal to 1 and our regression analysis agrees: The coefficient on Goals is 1, as expected (I will get into all the other numbers in the future). We can be pretty sure that the coefficient is 1, we actually don’t need to run a regression here but let’s look at one anyway: We want to estimate that coefficient, if we can figure it out we can explain our dependent variable, final score. There we go, this implies that we believe the final score is a function of one variable (number of goals) times some coefficient (beta). Let’s change it so that it looks more like what you will see in real life: Got it, but that doesn’t help too much because this implies that we already can explain final score, we can tell anyone the final score if we simply observe the number of goals. For simplicity we shall stay with the soccer example, let’s make this look a little bit more mathy: 1 is the coefficient on number of goals, we know in this case that it is 1 because everyone knows the rules of soccer, every goal is worth 1 point, if this were football (the American kind) then each touchdown would be worth 6 points so the coefficient on that would be 6.
Number of goals is the independent variable, we have no control over it and it is given by observations. Now I feel confident enough to write down a simple equation:įinal score is the dependent variable, it is what we are trying to explain. Why multiply by 1? Just to build some intuition, it becomes inportant later. If each goal were worth 2 points, all you would have to do is multiply the number of goals by 2, but since in this game each goal is worth one you simply multiply each goal by 1 to get the final score. Observe how many times a team kicks the ball into the opposite goal and you will know their final score. But first, what is a regression?īefore showing the equation, because it may be slightly off putting for those who aren’t terribly fond of math and also because I want to show the intuition first, lets look at a very simple problem: what determines the final score of a soccer game? It’s an easy answer, the number of goals will tell us the final score. These are some of the reasons we run regressions, Excel has a built-in solver feature which does just that, but I would like to build some macros that can also perform regressions, sort of like deconstructing and rebuilding an engine to truly understand how it works. Maybe you have to explain some phenomenon and predict it, but you don’t have a clue how it works or why it does what it does. Maybe you need to do some forecasting, maybe you want to tease out a relationship between two (or more) variables.
So, you want to run a linear regression in Excel.
0 kommentar(er)
