Single and Multiple Regression Analysis

Tags

, , , , ,

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.

Age (X)
Cholesterol Level (Y)
58 189
69 235
43 193
39 177
63 157
52 191
47 213
31 165
74 198
36 181

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.

137

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.

138

139

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.

140

141

142

The regression equation is Y = -788.45X1 + 0.394 X2 + 7576.03.

Advertisements