MS Excel - Example No. 10
1. Input data of both variables into a table (e.g. cells B3:B13 and C3:C13); for calculation of correlation coefficient in the cell B14 call menu Insert - Function - Statistical - CORREL:
2. Into Array1 in dialog window input cells used for variable ABE - B3:B13 and into Array2 input cells used for variable pH - C3:C13 (it is possible to mark them by means of the mouse), push the button OK to get result:
3. Result of the calculation: value of the correlation coefficient = 0.752233 (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 Y by means of context menu (right button of the mouse on the axis Y):
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 ABE and pH of urine the relation was found that is described by means of linear regression y=0.0041x + 7.3284 and correlation coefficient 0.752233.
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).