Question

In: Statistics and Probability

Use Excel to develop a regression model for the Hospital Database to predict the number of...

Use Excel to develop a regression model for the Hospital Database to predict the number of Personnel by the number of Births. How many residuals are within 1 standard error? Write your answer as a whole number.

Personnel(y) Births(x)
792 312
1762 1077
2310 1027
328 355
181 168
1077 3810
742 735
131 1
1594 1733
233 257
241 169
203 430
325 0
676 2049
347 211
79 16
505 2648
1543 2450
755 1465
959 0
325 1993
954 2275
1091 1494
671 1313
300 451
753 1689
607 1583
929 2017
354 995
408 2045
1251 1686
386 503
144 126
2047 2026
1343 1412
1723 1517
96 0
529 0
3694 2719
1042 1074
1071 1421
1525 762
1983 3194
670 496
1653 1442
167 0
793 1107
841 2989
316 113
93 0
373 0
263 173
943 1064
605 759
596 1317
1165 1751
568 0
507 0
479 714
136 99
1456 2243
3486 3966
885 1308
243 0
1001 2514
3301 3714
337 126
1193 556
1161 1327
322 415
185 216
205 339
1224 1217
1704 2641
815 790
712 520
156 35
1769 1168
875 793
790 0
308 0
70 14
494 0
111 0
1618 0
244 0
525 776
472 451
94 0
297 145
847 1284
234 1
401 319
3928 2154
198 295
1231 496
545 589
663 806
820 701
2581 3968
1298 0
126 0
2534 3655
251 0
85 0
432 0
864 0
66 0
556 3063
347 169
239 66
973 827
439 570
1849 0
102 0
262 0
885 342
549 494
611 0
330 0
1471 0
75 0
262 286
328 235
377 339
575 398
1916 1275
2620 5699
571 1364
703 714
535 0
160 0
202 0
1330 779
370 0
3123 2202
2745 3346
815 1071
576 352
502 254
808 0
50 0
728 699
4087 2462
3012 3311
68 0
3090 4207
1358 0
576 339
284 130
145 91
2312 1143
1124 0
336 0
415 509
1779 1026
338 0
453 447
437 1161
261 0
609 922
647 562
61 78
2074 0
2232 2122
948 0
409 0
153 0
741 710
1625 1165
538 466
789 1106
395 376
956 0
362 637
144 0
229 352
396 447
2256 1227
731 963
1477 3038
102 0
106 0
939 868
392 1189
3516 2849
785 1728
607 2171
273 364
630 0
1379 2993
1108 0
583 1964
514 601
216 387
1593 1946
1055 545
399 0
834 838
104 51

Solutions

Expert Solution

Sol:

Install analysis toolpak and then

Go to data >Data analysis >Regression

select Y as Y X as X

click ok

we get

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.697463
R Square 0.486455
Adjusted R Square 0.483861
Standard Error 590.2581
Observations 200
ANOVA
df SS MS F Significance F
Regression 1 65345182 65345181.8 187.5554 1.8E-30
Residual 198 68984120 348404.6475
Total 199 1.34E+08
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0%
Intercept 390.6214 54.07602 7.223561437 1.07E-11 283.9826 497.2603 283.9826
Births(x) 0.538735 0.039338 13.69508763 1.8E-30 0.46116 0.61631 0.46116
RESIDUAL OUTPUT
Observation Predicted Personnel(y) Residuals Standard Residuals
1 558.7067 233.2933 0.396236224 159
2 970.8389 791.1611 1.34374504
3 943.9022 1366.098 2.320244573
4 581.8723 -253.872 -0.43118868
5 481.1289 -300.129 -0.509753009
6 2443.201 -1366.2 -2.320420653
7 786.5916 -44.5916 -0.075736471
8 391.1602 -260.16 -0.441868242
9 1324.249 269.7509 0.458157664
10 529.0763 -296.076 -0.502869896
11 481.6676 -240.668 -0.408761208
12 622.2775 -419.277 -0.712120491
13 390.6214 -65.6214 -0.111454531
14 1494.489 -818.489 -1.390160586
15 504.2945 -157.295 -0.267156368
16 399.2412 -320.241 -0.54391267
17 1817.191 -1312.19 -2.228687582
18 1710.522 -167.522 -0.284527197
19 1179.868 -424.868 -0.721615894
20 390.6214 568.3786 0.965360803
21 1464.32 -1139.32 -1.935074739
22 1616.243 -662.243 -1.124785206
23 1195.491 -104.491 -0.177473103
24 1097.98 -426.98 -0.725203511
25 633.5909 -333.591 -0.566586408
26 1300.545 -547.545 -0.929975621
27 1243.439 -636.439 -1.080957528
28 1477.25 -548.25 -0.931173116
29 926.6627 -572.663 -0.972637156
30 1492.334 -1084.33 -1.841684304
31 1298.929 -47.9285 -0.081404028
32 661.6051 -275.605 -0.468100632
33 458.502 -314.502 -0.534165013
34 1482.098 564.9016 0.959455403
35 1151.315 191.6849 0.325566552
36 1207.882 515.1177 0.874900046
37 390.6214 -294.621 -0.500398871
38 390.6214 138.3786 0.235028636
39 1855.442 1838.558 3.122693679
40 969.2227 72.77726 0.123608311
41 1156.164 -85.1638 -0.144646118
42 801.1374 723.8626 1.229442109
43 2111.341 -128.341 -0.217979972
44 657.834 12.16604 0.020663376
45 1167.477 485.5228 0.824634711
46 390.6214 -223.621 -0.379809141
47 987.001 -194.001 -0.329500384
48 2000.9 -1159.9 -1.970028737
49 451.4985 -135.498 -0.230136982
50 390.6214 -297.621 -0.505494211
51 390.6214 -17.6214 -0.02992908
52 483.8226 -220.823 -0.375055427
53 963.8354 -20.8354 -0.035387805
54 799.5212 -194.521 -0.330383999
55 1100.135 -504.135 -0.856247079
56 1333.946 -168.946 -0.286946283
57 390.6214 177.3786 0.301268065
58 390.6214 116.3786 0.197662805
59 775.2782 -296.278 -0.503212739
60 443.9562 -307.956 -0.523047247
61 1599.004 -143.004 -0.242884459
62 2527.244 958.7559 1.628395952
63 1095.287 -210.287 -0.357160812
64 390.6214 -147.621 -0.250727177
65 1745.001 -744.001 -1.263646225
66 2391.483 909.5171 1.544766463
67 458.502 -121.502 -0.206364762
68 690.1581 502.8419 0.854050345
69 1105.523 55.47733 0.094225292
70 614.1964 -292.196 -0.496280121
71 506.9882 -321.988 -0.546879829
72 573.2526 -368.253 -0.625457446
73 1046.262 177.7382 0.301878838
74 1813.42 -109.42 -0.185844662
75 816.222 -1.22202 -0.002075543
76 670.7636 41.2364 0.070037842
77 409.4772 -253.477 -0.430517499
78 1019.864 749.1362 1.272368017
79 817.8382 57.16177 0.097086233
80 390.6214 399.3786 0.678323277
81 390.6214 -82.6214 -0.140328128
82 398.1637 -328.164 -0.557368667
83 390.6214 103.3786 0.175582995
84 390.6214 -279.621 -0.474922167
85 390.6214 1227.379 2.084637309
86 390.6214 -146.621 -0.24902873
87 808.6797 -283.68 -0.481814966
88 633.5909 -161.591 -0.274453541
89 390.6214 -296.621 -0.503795764
90 468.738 -171.738 -0.291687878
91 1082.357 -235.357 -0.39974149
92 391.1602 -157.16 -0.266928211
93 562.4779 -161.478 -0.274261601
94 1551.056 2376.944 4.0371124
95 549.5482 -351.548 -0.597086018
96 657.834 573.166 0.973492086
97 707.9363 -162.936 -0.276738663
98 824.8418 -161.842 -0.274879674
99 768.2746 51.72538 0.087852817
100 2528.322 52.67841 0.089471484
101 390.6214 907.3786 1.541134301
102 390.6214 -264.621 -0.449445464
103 2359.698 174.3024 0.296043438
104 390.6214 -139.621 -0.237139601
105 390.6214 -305.621 -0.519081786
106 390.6214 41.37856 0.070279287
107 390.6214 473.3786 0.804008347
108 390.6214 -324.621 -0.551352278
109 2040.766 -1484.77 -2.521797039
110 481.6676 -134.668 -0.228725837
111 426.1779 -187.178 -0.317911799
112 836.1552 136.8448 0.232423599
113 697.7003 -258.7 -0.439388794
114 390.6214 1458.379 2.476978542
115 390.6214 -288.621 -0.490208189
116 390.6214 -128.621 -0.218456685
117 574.8688 310.1312 0.526741407
118 656.7565 -107.756 -0.183018674
119 390.6214 220.3786 0.374301282
120 390.6214 -60.6214 -0.102962296
121 390.6214 1080.379 1.834965615
122 390.6214 -315.621 -0.536066255
123 544.6996 -282.7 -0.480150303
124 517.2241 -189.224 -0.321387163
125 573.2526 -196.253 -0.33332458
126 605.0379 -30.0379 -0.05101784
127 1077.508 838.4915 1.424133358
128 3460.872 -840.872 -1.428175981
129 1125.456 -554.456 -0.941713846
130 775.2782 -72.2782 -0.122760634
131 390.6214 144.3786 0.245219318
132 390.6214 -230.621 -0.391698269
133 390.6214 -188.621 -0.320363499
134 810.2959 519.7041 0.882689749
135 390.6214 -20.6214 -0.03502442
136 1576.916 1546.084 2.625942039
137 2193.228 551.7715 0.937154642
138 967.6065 -152.607 -0.259194097
139 580.2561 -4.25613 -0.007228812
140 527.4601 -25.4601 -0.043242643
141 390.6214 417.3786 0.708895321
142 390.6214 -340.621 -0.578527428
143 767.1971 -39.1971 -0.066574272
144 1716.987 2370.013 4.025341561
145 2174.373 837.6273 1.422665407
146 390.6214 -322.621 -0.547955384
147 2657.079 432.9208 0.735292932
148 390.6214 967.3786 1.643041115
149 573.2526 2.747423 0.004666353
150 460.657 -176.657 -0.300042498
151 439.6463 -294.646 -0.500441124
152 1006.395 1305.605 2.217499999
153 390.6214 733.3786 1.245604541
154 390.6214 -54.6214 -0.092771615
155 664.8375 -249.838 -0.424335748
156 943.3635 835.6365 1.419284282
157 390.6214 -52.6214 -0.089374721
158 631.4359 -178.436 -0.303063982
159 1016.093 -579.093 -0.983558163
160 390.6214 -129.621 -0.220155132
161 887.335 -278.335 -0.472737273
162 693.3905 -46.3905 -0.078791738
163 432.6428 -371.643 -0.631215499
164 390.6214 1683.379 2.859129094
165 1533.817 698.1831 1.185826865
166 390.6214 557.3786 0.946677887
167 390.6214 18.37856 0.031215009
168 390.6214 -237.621 -0.403587397
169 773.1232 -32.1232 -0.054559601
170 1018.248 606.7524 1.030536702
171 641.6719 -103.672 -0.176081236
172 986.4623 -197.462 -0.335379159
173 593.1858 -198.186 -0.336608004
174 390.6214 565.3786 0.960265462
175 733.7956 -371.796 -0.631475053
176 390.6214 -246.621 -0.418873419
177 580.2561 -351.256 -0.596589885
178 631.4359 -235.436 -0.399875455
179 1051.649 1204.351 2.045525911
180 909.4232 -178.423 -0.30304227
181 2027.298 -550.298 -0.934652129
182 390.6214 -288.621 -0.490208189
183 390.6214 -284.621 -0.483414402
184 858.2433 80.75665 0.137160886
185 1031.177 -639.177 -1.085608627
186 1925.477 1590.523 2.701418487
187 1321.555 -536.555 -0.911310814
188 1560.215 -953.215 -1.618984965
189 586.7209 -313.721 -0.532838374
190 390.6214 239.3786 0.406571773
191 2003.055 -624.055 -1.059924356
192 390.6214 717.3786 1.218429391
193 1448.697 -865.697 -1.470340072
194 714.4011 -200.401 -0.340370669
195 599.1119 -383.112 -0.650695138
196 1439 154.0004 0.261561523
197 684.232 370.768 0.629729812
198 390.6214 8.37856 0.01423054
199 842.0813 -8.0813 -0.013725659
200 418.0969 -314.097 -0.53347694

. How many residuals are within 1 standard error? Write your answer as a whole numbe

residual output take -1 and+1 standard error

count whose standard resiudals between z =-1 and +1

use excel formula

=COUNTIFS(D25:D224,">=-1", D25:D224,"<=1")

=159

ENTER ANSWER

159

orchestra answered 2 years ago
Next > < Previous

Related Solutions

Use Excel to develop a regression model for the Hospital Database to predict the number of...
Use Excel to develop a regression model for the Hospital Database to predict the number of Personnel by the number of Births. How many residuals are within 1 standard error? Write your answer as a whole number. Personnel Births 792 312 1762 1077 2310 1027 328 355 181 168 1077 3810 742 735 131 1 1594 1733 233 257 241 169 203 430 325 0 676 2049 347 211 79 16 505 2648 1543 2450 755 1465 959 0 325...
Use Excel to develop a regression model for the Hospital Database (using the “Excel Databases.xls” file...
Use Excel to develop a regression model for the Hospital Database (using the “Excel Databases.xls” file on Blackboard) to predict the number of Personnel by the number of Births. Perform a test of the overall model, what is the value of the test statistic? Write your answer as a number, round your answer to 2 decimal places. SUMMARY OUTPUT Regression Statistics Multiple R 0.697463374 R Square 0.486455158 Adjusted R Square 0.483861497 Standard Error 590.2581194 Observations 200 ANOVA df SS MS...
Use Excel to develop a multiple regression model to predict Cost of Materials by Number of...
Use Excel to develop a multiple regression model to predict Cost of Materials by Number of Employees, New Capital Expenditures, Value Added by Manufacture, and End-of-Year Inventories. Locate the observed value that is in Industrial Group 12 and has 7 employees. Based on the model and the multiple regression output, what is the corresponding residual of this observation? Write your answer as a number, round to 2 decimal places. SIC Code No. Emp. No. Prod. Wkrs. Value Added by Mfg....
Use Excel to develop a regression model for the Consumer Food Database (using the “Excel Databases.xls”...
Use Excel to develop a regression model for the Consumer Food Database (using the “Excel Databases.xls” file) to predict Annual Food Spending by Annual Household Income. Assume a 5% level of significance. (file here: https://drive.google.com/file/d/13uDUXwoSRZHEUtjMUedu2yjR_4lrLepC/view?usp=sharing ) Must complete all the parts to this problem: PART 1: Perform a simple linear regression in Excel to predict Annual Food Spending by Annual Household Income and output the results. Include the Regression Statistics, ANOVA, and table of Coefficients for each model. PART 2:...
1) Use Excel to develop a regression model for the Consumer Food Database (using the “Excel...
1) Use Excel to develop a regression model for the Consumer Food Database (using the “Excel Databases.xls” file on Blackboard) to predict Annual Food Spending by Annual Household Income for those living in the Metro area only.    Suppose a household in the metro area has an annual income of $60,000. Predict how much they spend on food per year. Write your answer as a number (do not include the $ sign or comma) and round to 2 decimal places....
Use the Manufacturing database from “Excel Databases.xls” on Blackboard. Use Excel to develop a multiple regression...
Use the Manufacturing database from “Excel Databases.xls” on Blackboard. Use Excel to develop a multiple regression model to predict Cost of Materials by Number of Employees, New Capital Expenditures, Value Added by Manufacture, and End-of-Year Inventories. Locate the observed value that is in Industrial Group 12 and has 7 employees. Based on the model and the multiple regression output, what is the corresponding residual of this observation? Write your answer as a number, round to 2 decimal places. **Answer should...
An electronics company is looking to develop a regression model to predict the number of units...
An electronics company is looking to develop a regression model to predict the number of units sold for a special running watch. Data is provided below: Sales (units) Price ($) Advertising ($) Holiday 500 100 50 Yes 480 120 40 Yes 485 110 45 No 510 103 55 Yes 490 108 40 No 488 109 30 No 496 106 45 Yes Compile a spreadsheet for the data and determine the predicted number of units sold if the watch is sold...
An electronics company is looking to develop a regression model to predict the number of units...
An electronics company is looking to develop a regression model to predict the number of units sold for a special running watch. Data is provided below: Sales (units) Price ($) Advertising ($) Holiday 500 100 50 Yes 480 120 40 Yes 485 110 45 No 510 103 55 Yes 490 108 40 No 488 109 30 No 496 106 45 Yes Compile an excel spreadsheet for the above data and determine the regression equation Answer (a) X3 = 1 if...
Use the following data to develop a multiple regression model to predict from and . Discuss...
Use the following data to develop a multiple regression model to predict from and . Discuss the output, including comments about the overall strength of the model, the significance of the regression coefficients, and other indicators of model fit. y x1 x2 198 29 1.64 214 71 2.81 211 54 2.22 219 73 2.70 184 67 1.57 167 32 1.63 201 47 1.99 204 43 2.14 190 60 2.04 222 32 2.93 197 34 2.15 Appendix A Statistical Tables *(Round...
Use the International Stock Market database from “Excel Databases.xls” on Blackboard. Use Excel to develop a...
Use the International Stock Market database from “Excel Databases.xls” on Blackboard. Use Excel to develop a multiple regression model to predict the DJIA by the Nasdaq, the S&P 500, the Nikkei, the Hang Seng, the FTSE 100, and the IPC. Performing a stepwise regression analysis at a 5% level of significance, which independent variable is the best single predictor of the DJIA? This is Step 1 of the stepwise regression. Nasdaq S&P 500 Nikkei Hang Seng FTSE 100 IPC https://drive.google.com/file/d/19TI3HId0greXS0nkmDuoITv1IMPF_TUK/view?usp=sharing...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT