In statistical modeling, Regression Analysis is a statistical process for estimating the relationships among variables. It includes many techniques for modeling and analyzing several variables. The main focus is on the relationship between one dependent variables (Yn) and one or more independent variables (X).
Regression Analysis is applied for two variables (1 Independent and 1 Dependent) or more (N Independents and 1 Dependent). Hence the analysis changes for both sets of variables.
Let us understand how to solve both types of regression using MS Excel.
SINGLE REGRESSION ANALYSIS
Consider the below set of data.
||Cholesterol Level (Y)
Assuming that Age is an Independent Variable (X) and Cholesterol Level as Dependent Variable (Y), we have to find the regression of Cholesterol Level on Age. Let us assume a confidence level of 95% (This can be changed according to the nature of data in Excel).
Enter the above data in MS Excel and click on DATA > DATA ANALYSIS. You will get the below dialogue box where you have to select REGRESSION > OK.
Input Y Range by selecting the cells under Cholesterol Level, X Range by selecting the cells under Age. Tick the boxes as shown in below figure and click OK.
The summary output along with the relevant graphs will be shown in the Excel.
So the regression equation is Y = 0.691X + 155.153. Using this equation, we can predict the cholesterol limit (Y) at any age (X) using 95% confidence level.
MULTIPLE REGRESSION ANALYSIS
Consider the following example. The question being asked here is that is there any relation between Quantity Sold (Y) of a particular product, its Price (X1) and the amount spent on advertising (X2). Here we can see that there are two independent variables and 1 dependent variable.
|Quantity of Goods Sold (Y)
||Price of the Item (X1)
||Advertising Cost (X2)
|4000||$ 3.00||$ 2,500.00|
|3500||$ 5.00||$ 3,200.00|
|6000||$ 2.00||$ 400.00|
|5500||$ 4.00||$ 5,900.00|
|3200||$ 7.00||$ 6,700.00|
|9800||$ 1.00||$ 2,300.00|
|7600||$ 5.00||$ 3,500.00|
|6900||$ 3.00||$ 1,700.00|
|2800||$ 6.00||$ 490.00|
|4600||$ 4.50||$ 870.00|
|5250||$ 9.00||$ 7,500.00|
|2500||$ 3.50||$ 2,300.00|
Follow the same procedure as that of Single Regression Analysis as shown below.
The regression equation is Y = -788.45X1 + 0.394 X2 + 7576.03.