Use regression analysis when you want to predict a value when you have independent data. For instance you want to use regression analysis to predict sales, costs, weight, size or any value that will provide meaningful information. The main tools you will need is a spreadsheet program like Microsoft Excel or OpenOffice Calc. In this article we will gain an understanding of the LINEST function to help develop the regression analysis.
Video of the Day
Collect the data that you will want to predict. The first data you will want is the predicted value or Dependent Variable. The dependent variable can be any sort of predicted value that will be beneficial to your regression analysis. For instance you may want to predict sales for an operating year, weight of a given population, or size of growth of bacteria. The next data is the independent variables. The independent variables are the data points that you know that result with your predicted variable. For instance your dependent variables could be height, age, income level, years, quality score and so on. The value of regression analysis is that it can be any measurable value.
Open your spreadsheet program and place your data values in a table. In this regression analysis example we will be predicting the Trade-In Value of a car. Our Dependant Variable is the car Trade-In Value. The Independent Variables are car miles, age of the car in years, and a numerical condition score (1=bad, 5=excellent). Tabulate the data in an nice organized table. This will be important for the next step when we introduce the LINEST function.
The LINEST function allows to formulate a regression analysis table. First we will need highlight an empty area that is 5 rows x 3 columns. In our example we highlight from cell I6 to G3. While the area is still highlighted type into the cell G3 the function "=LINEST(E3:E12,B3:D12,1,1)". Then to execute the function we will use a special key stroke of SHIFT+CTRL+ENTER. This will establish our array of the LINEST regression analysis table.
The results of the LINEST regression analysis table give us values of our Linear Regression equation. The first row of the LINEST table give use the coefficients of our linear equation. The linear regression analysis equation is formulated as Y = B1_x1 + B2_x2 + ... + B(n)*x(n) where n is any number of dependent variables.
In our example the linear regression equation is Trade-In Value = -0.09(Miles) +24.35(Years) + 1688.6*(Condition Score).
There are other ways to do regression analysis. This article introduces the concept of linear regression. You may want to explore Multiple Regression and Nonlinear Regression.