![use excel to calculate linear regression equation use excel to calculate linear regression equation](https://www.allbusinesstemplates.com/thumbs/6f9ee310-953d-4d4a-afaf-2f7ee04ce794_1.png)
So we have: year resale value, Price in thousands, Engine size, Horsepower, Wheelbase, Width, Length, Curb weight, Fuel capacity, Fuel efficiency, Power perf factor, and Sales in thousands. Step 1: Remove everything that is categorical. Steps follow to build the model in Excel: Now we are proposing a regression model on the features so that when next time a new car is launched, we can predict how much it will sell. Here we have features of different cars sold in the past. This method is known as ordinary least squares (OLS) method.Įxample: Let us consider a small Car Sales dataset provided by the Analytixlabs when I was studying there, with variables Manufacturer, Model, Vehicle type, year resale value, Price in thousands, Engine size, Horsepower, Wheel base, Width, Length, Curb weight, Fuel capacity, Fuel efficiency, Latest Launch, Power perf factor, Sales in thousands. We will partially differentiate the quantity □0 with respect to □0 and equate to zero, we will get □0. So we have to minimize the quantity with respect to □0, □1, □2, …. We should build an equation so that MSE is very low. The Error can be measured by the quantity MSE (Mean Squared Error). The whole instruments of linear regression is built around predicting the value or getting the values of □0, □1, □2, … □□ are variables that we used to predict □. Since we are using linear regression, the equation is a linear equation. It is a supervised learning task where output is a continuous value. So here, we are trying to use the relationship pattern to predict the value of a variable. If there is only one independent variable, it is a simple linear regression if there are multiple independent variables, it is a multiple linear regression. This technique is used to predict the value of an independent variable based on the value of other dependent variables. Before building a model, let us know a little about what is Linear Regression? So Linear regression is a statistical method used to explore the correlation between two continuous quantitative variables. With latest versions of Excel, it does not take more than a minute to build a model. Can we use excel to do the same? Yes we can use different excel Add-ins or tools to do this. You can also create a scatter plot of these residuals.In data science, we build regression models to see how well one variable can be predicted based on one or more variables. For example, the first data point equals 8500. The residuals show you how far away the actual data points are fom the predicted data points (using the equation). For example, if price equals $4 and Advertising equals $3000, you might be able to achieve a Quantity Sold of 8536.214 -835.722 * 4 + 0.592 * 3000 = 6970. You can also use these coefficients to do a forecast.
![use excel to calculate linear regression equation use excel to calculate linear regression equation](https://cdn.ablebits.com/_img-blog/regression/linear-regression-graph-excel.png)
For each unit increase in Advertising, Quantity Sold increases with 0.592 units.
![use excel to calculate linear regression equation use excel to calculate linear regression equation](https://i.ytimg.com/vi/QfBvEjBz_1s/maxresdefault.jpg)
In other words, for each unit increase in price, Quantity Sold decreases with 835.722 units. The regression line is: y = Quantity Sold = 8536.214 -835.722 * Price + 0.592 * Advertising. Most or all P-values should be below below 0.05. Delete a variable with a high P-value (greater than 0.05) and rerun the regression until Significance F drops below 0.05. If Significance F is greater than 0.05, it's probably better to stop using this set of independent variables. If this value is less than 0.05, you're OK. To check if your results are reliable (statistically significant), look at Significance F ( 0.001).