VLC Home
  Course Home
   
--  DATA ANALYSIS USING MS EXCEL
- Exercises On Analysis Of Variance (Anova)
- Descriptive Statistics
- Regression Analysis
- t-Test
   
+ MS EXCEL Case Study
   
 
Self Test
Ask a Question
Download PDF File


Regression Analysis

Performs linear regression analysis by using the "least squares" method to fit a line through a set of observations. By using this tool to analyze how a single dependent variable is affected by the values of one or more independent variables .

Example

The following table gives for 25 progenies of cotton the data for mean fiber length of each progeny , the corresponding parent plant value and the mean value of the plot in which the parent was grown. It is found that both the parental value as well as the plot mean bear some relationship with the progeny mean. Express this relation in the form of a partial regression equation with progeny mean as the dependent variate.

Number  of Progenies

Progeny Mean (mm)

Y

Parental Plant Value

X1

Parental Plot Mean

X2

1

24.3

26

25.5

2

24.48

28.8

25.5

3

23.41

25.2

25.5

4

21.6

23.4

25

5

22.49

26.6

25

6

23.62

25.4

24.6

7

22.75

23.4

24.6

8

24.4

27.6

23.6

9

22.6

24.4

23.6

10

25.36

24

24.42

11

23.21

24.2

24.42

12

24.76

26

24.42

13

21.53

22.8

22.56

14

21.32

20.8

22.56

15

22.81

24.8

22.56

16

25.41

26.2

24.9

17

24.3

27.2

24.9

18

23.65

26.6

24.91

19

24.31

25

24.91

20

21.88

23.4

24.05

21

24.1

25.6

24.05

22

21.91

23

24.05

23

22.24

25.4

24.57

24

23.45

23.4

24.57

25

22.1

24.2

24.57

Analysing the Data
  • Enter the above data in a separate worksheet of the same workbook as did earlier.

  • On the Main Menu Click the Tool menu to get various options in the Tool menu.

  • Click the Data Analysis Option to get the different options of Analysis Tool Pack as shown in the previous exercise.

  • Click the Regression option from the displayed Analysis Tool Pack Options.

  • Click OK to get the Regression Analysis Tool Window as shown below

  • Input Y Range : Enter the range of dependent variable's data, that is, B2 :B26 The range must consist of a single column.

  • Input X Range : Enter the range of independent variables data, that is, C2:D26 The maximum number of input ranges is upto 16 variables.

  • Click in the Labels check box to select the first row or column of input range contains labels.

  • Click in the Confidence Level check box to include an additional level in the summary output table. In the box, enter the desired confidence level in addition to the default 95% level.

  • Output Range : Enter the range of cells or a cell that is A30 to keep the output on the worksheet. Allow at least seven columns for the summary output table, which includes an anova table, coefficients, standard error of y estimate, r2 values, number of observations, and standard error of coefficients.

  • Click in the Residuals check box to include residuals in the residuals output table.

  • Click in the Standardised Residuals check box to include standardised residuals in the residuals output table.

  • Click in the Residual Plots to generate a chart for each independent variable versus the residual.

  • Click in the Line Fit Plot to generate a chart for predicted values versus the observed values.

  • Click in the Normal Probability Plot to generate a chart plotting normal probability.