MS Excel - Example No. 12
1. Input data of both variables into a table (e.g. cells B2:B11 and C2:C11), calculate AVG and SD for samples:
2. For calculation of correlation coefficient in the cell B14 call menu Insert - Function - Statistical - CORREL:
3. Into Array1 in dialog window input cells used for variable body weight - B2:B11 and into Array2 input cells used for variable weight of egg - C2:C11 (it is possible to mark them by means of the mouse), push the button OK to get result: value of the correlation coefficient = 0.95266 (direct relation):
4. Graphical presentation of the relation including calculated linear regression: mark cells B3:C13 and call menu Chart Wizard - Chart type - XY Scattered:
5. Display preview of the chart in the dialog window 2/4:
6. Input Title of the chart and description of axes X, Y in dialog window 3/4:
7. Select a location of the chart: As object in: (default sheet):
8. After finishing of the chart it is possible to adjust scale of axis X by means of context menu (right button of the mouse on the axis X):
9. Smooth data (displayed points) by the line: context menu in any point of scattered diagram - option Add trendline - Type: Linear:
10. Calculation of appropriate equation of the linear regression: card Options - Display equation on chart:
11. Finish the graphical presentation of relation by button OK:
12. Conclusion: between body weight of layers and weights of their eggs the relation was found that is described by means of linear regression y=9.2971x + 20.127 and correlation coefficient 0.95266.
Note:
Both variables monitored in this example are equally valuable, then its relation is reciprocal (correlative relation) and it is possible to evaluate it also by means of the method, based on the mutual exchange of data sets (X« Y).