Some time ago I wrote an article explaining linear regression. If you have no understanding about the method, I recommend that you read it.
Now I am going to build upon this article, using the same example as from it and to make linear regression with the Data Analysis tool pack in Excel. Pretty much, we will use the same data, as in the previous example and we will simply show how to achieve the same result with the Data Analysis tool pack in Excel. The Benefit? You get much more additional information for the model.
So we start, selecting the Data Ribbon in Excel. There we select “Data Analysis”. If this is not available, then you should install this Add-In.
Once this is done, you select “Regression” from the list:
And then the interesting and quick part starts. You simply set the “X” and the “Y” values in the new window, trying not to mistake them. The “Y” values are the one, that are dependent, the ones you are trying to project in the future. In our case “Y” are the variables for “Profit per day”. The variable “Week Number” is independent.
Once we press “OK” we get the following picture:
Here is exactly why we need this method for data analysis. In the previous one, we simply achieved a graph with a formula and squared R. In this method, we get a load of other statistical measures, including the squared R and the formula (highlighted in yellow).
Pretty much that is for the linear regression. The subject is interesting if you are keen on logical forecasting methods with Excel.