Question

Compute Regression Analysis for following relationship: The relationship between death rate X1 vs. doctor availability X2. Doctor availability as a Predictor X, then death rate as a Response variable Y. Get the Regression Output and scatter plot between the variables using data analysis toolpak in Excel. X1= Death rate per 1000 residents. X2= doctor availability per 100,000 residents.

X1   X2
8 78
9.3 68
7.5 70
8.9 96
10.2 74
8.3 111
8.8 77
8.8 168
10.7 82
11.7 89
8.5 149
8.3 60
8.2 96
7.9 83
10.3 130
7.4 145
9.6 112
9.3 131
10.6   80
9.7 130
11.6 140
8.1 154
9.8 118
7.4 94
9.4 119
11.2 153
9.1 116
10.5 97
11.9 176
8.4 75
5 134
9.8 161
9.8 111
10.8 114
10.1 142
10.9 238
9.2 78
8.3 196
7.3 125
9.4 82
9.4 125
9.8 129
3.6 84
8.4 183
10.8 119
10.1 180
9 82
10 71
11.3 118
11.3 121
12.8   68
10 112
6.7 109

Answer 1

solution : X1 = Y........death rate, X2=X............doctor availability

$\bar Y=\frac{\sum Y}{n}=\frac{493}{53}=9.31$

$\bar X=\frac{\sum X}{n}=\frac{6153}{53}=116.09$

compute the following

$dx=x-\bar X$ and   $dy=y-\bar Y$

regression equation of y on x =$Y-\bar Y=\frac{\sum dxdy}{\sum dx^{2}}(X-\bar X)$

$Y-9.31=\frac{379.17}{ 74640.53}(X-116.09)$

$Y=0.005X-9.9$

dxdy dy dx 1 8781.3138.09 49.8979 1450.85 2.59 59.91 155.1669 3589.21 2 9.3680.01 48.09 0.4809 2312.65 0.9141.09 37.3919 1688.39 3 7.570-1.8146.09 83.4229 2124.29 4.31 17.91 77.1921 320.77 4 8.996-0.4120.098.2369 403.61 0.49 44.91 2.00 2016.91 5 10.274 0.8942.09 37.4601 1771.57 5.09 2.494125.91 34 7 8.8 77- 0.51 -39.09 19.9359 1528.03 0.79 2.91 20.4689 671.33 8 8.8 168 -0.5151.91 -26.4741 2694.65 238 9 121.91 193.8369 14862.05 78 0.11 38.09 4.1899 1450.85 9 10.7821.39 34.09 -47.3851 1162.13 1.01 79.91 -80.7091 6385.61 10 11.78 .9 27.09 -64.7451 733.87 11 8.5 1490.8132.91 -26.6571 1083.07 8.91-179091 79.39 12 8.3601.0156.09 56.6509 3146.09 0.09 34.09 -3.0681 1162.13 13 8.296-1.1120.09 22.2999 403.61 8.91 0.8019 79.39 14 7.9 83 -1.41-33.09 46.6569 1094.95 0.49 12.91.29 166.67 15 10.3 130 0.99 13.91 13.7709 193.49 5.71-32.09 183.2339 1029.77 16 7.4 145 1.9128.9155.2181 835.79 0.91 66.91-60.8881 4476.95 17 9.6 1120.294.091.1861 16.73 18 9.3 1310.01 14.910.1491 222.31 0.79 63.91 50.4889 4084.49 19 10.6 80 236.09 -46.5561 1302.49 0.31 -34.09 10.5679 1162.13 20 9.7 1300.39 13.91 5.4249 193.49 0.69 45.09 31.11212033.11 11.6 140 23.91 54.7539 571.69 22 8.1 154 1.21 37.91-45.8711 1437.17 1.99 91 9.7709 24.11 3.49 48.09-167.834 2312.65 1.91 0.9359 24 7.4 941.91 -22.09 42.1919 487.97 26 11.2 15 1.8936.91 6799 1362.35 Mean 9.30566 116.0943 379.1707 74640.53 27 9.1 116-0.210.09 0.0189 28 10.597 1.19 19.09-22.7171 364.43 29 11.9 176 2.59 59.91 155.1669 3589.21

scatter plot 14. 12 10 death rate 4 50 100 150 200 250 doctor availibility