Question

Confirmed Cases, X Hospitalizations, Y 2605 482 1833 334 1532 321 1474 283 1465 431 1062 154 643 113 626 115 456 54 384 71 373 108 362 87 329 61 302 44 292 66 288 116 282 89 282 50 263 36 252 64 212 73 206 52 201 14 199 9 195 33 188 32 183 39 181 39 170 10 167 28 167 30 165 29 163 38 161 48 159 35 156 21 155 32 144 23 142 28 137 25 135 39 133 36 128 28 126 9 114 32 112 36 110 33 109 33 100 27 98 17 85 6 81 25 76 17 76 13 73 25 72 21 70 15 67 10 64 8 62 8 59 0 58 11 57 7 56 9 54 15 53 11 53 16 53 11 51 11 51 14 50 8 49 2 49 10 48 16 48 10 46 5 46 10 44 14 43 13 39 10 39 7 39 14 39 6 35 10 34 7 34 11 34 4 33 6 32 11 32 13 31 6 31 8 29 6 29 1 29 6 28 7 27 6 26 4 25 5 24 2 24 3 24 3 24 5 23 4 23 4 22 3 22 4 22 7 21 1 21 8 21 4 21 11 20 7 20 4 20 2 20 3 20 7 19 1 19 2 17 4 17 3 17 4 17 4 16 2 16 3 16 7 16 8 15 1 15 2 14 2 14 3 14 2 14 6 13 3 12 3 11 2 10 3 10 5 10 4 9 0 9 2 9 0 9 5 8 2 8 2 8 2 8 4 7 2 7 1 7 2 5 0 4 0 4 2 4 2 3 2 3 0 2 1 2 0 0 0 0 0

Use Data Analysis tool to regress Y on X.

Display the regression output on this sheet.

Interpret the coefficient on Confirmed Cases. Enter your answer in the next row.

Please use excel to complete this. can you send an excel file back

Answer 1

We have given two variables for the regression question.

The dependent variable is "Hospitalization" and the independent variable is "Confirmed cases",

So we will be regressing y on x

Excel is being used in this analysis

Path:-

Excel-->Data --> Data Analysis --> Regression -->Select the input (y&x),output range-->check on Labels-->Click OK

Regression output is displayed below:-

From above analysis we can see that the coefficient of the confirmed cases is 0.001758 and the intercept is 0.648184

Interpretation:-

For every 1 increase in confirmed cases there will be 0.001758 increase in hospitalization.

Here for 1 it really does not make any sense.

So,for 1000 increase in confirmed cases there will 1.7 2 hospitalizations.

This is also not satisfactory.The reason behind this would be the r-square value= 0.4845 i.e X is explaing 48.45 % of variation in the y.Which means that there may be several variable which are really important in predicting y.

From above plot we can see that the linear trend is not a good representative of the data.So one can go for the polynomial trend to check whether it increase the value of r-square or not.

Refer below:-

I tried to add the cubic effect of the regressor in the model which has effectively increased the R-square value.