(PLEASE SHOW YOUR WORKING ).....this lab utilizes hypothetical data that was col
ID: 3319209 • Letter: #
Question
(PLEASE SHOW YOUR WORKING ).....this lab utilizes hypothetical data that was collected by the U.S. Census Bureau. When you have completed this assignment you will have some insight into how we can use statistics to assess patterns between variables on a large scale. In this lab we are considering a potential association between poverty rate and infant mortality rate in the United States. To answer this question we will be using poverty and infant mortality data from each state. In these data, poverty rate is expressed as a percentage of the population of each state that is below the poverty line. Infant mortality rate is measured as the number of deaths of children younger than one year of age per 1000 live births. Higher scores on both of these variables indicate higher levels of poverty or more infant deaths. ( please show the working on how it is done in excel)
State X: Poverty Rate Y: Infant Mortality (Deaths/1000 births) Alabama 17.5 7.61 Alaska 9.8 4.59 Arizona 21 5.27 Arkansas 16.8 8.61 California 13.5 5.76 Colorado 9.6 4.09 Connecticut 12.3 4.68 Delaware 15 7.28 Florida 13.2 5.16 Georgia 17.2 7.80 Hawaii 12.7 6.73 Idaho 11.9 6.53 Illinois 13.2 7.20 Indiana 10.5 7.00 Iowa 9.8 3.15 Kansas 12.5 7.44 Kentucky 19 7.93 Louisiana 20.2 9.56 Maine 11.9 7.40 Maryland 10.2 6.36 Massachusetts 12.3 5.14 Michigan 13.5 8.04 Minnesota 11 4.56 Mississippi 23.5 10.76 Missourri 12.7 7.42 Montana 15.4 4.76 Nebraska 12.2 4.33 Nevada 16.3 5.45 New Hampshire 8 6.75 New Jersey 12.1 3.59 New Mexico 22.7 5.35 New York 13.4 3.84 North Carolina 17.5 5.88 North Dakota 10.1 5.15 Ohio 14.8 8.33 Oklahoma 15 7.82 Oregon 14.2 5.96 Pennsylvania 11.3 7.45 Rhode Island 12.6 6.48 South Carolina 14.6 6.75 South Dakota 9.8 5.53 Tennessee 17.4 6.80 Texas 15.7 5.84 Utah 8.6 4.56 Vermont 9.2 3.35 Virginia 9.5 7.21 Washington 11 5.48 West Virginia 18.5 7.59 Wisconsin 12 6.24 Wyoming 12.9 3.58 X 685.6 Y 310.14 ( X)^2 ( Y)^2 X^2 Y^2 N 50 N 50 SS X by Formula SS Y by Formula SS X by Google SS Y by Google (XY) ( X)* ( Y) SP r by Formula r by Sheets Slope by Formula sd X sd Y r*(sdY/sdX) SP/(SS X) Slope by Google Intercept By Google By Formula Y Mean X Mean M(Y) - b*M(X) MS Regress/Error r^2 1-r^2 SS Regression df Regression MS Regression SS Residual df Residual MS Residual F Calculated Critical F (.05)Explanation / Answer
b1= nE(xy)-ExEy/nE(x2)-(Ex2)
=50*4395.162-(685.6*310.14)/50*10047.06-(685.6*685.6)
=0.22
b0=Ey-b1Ex/n
=310.14-(0.22*685.6)/50
=3.18
r (correlation)=n(Exy)-(Ex)(Ey)/(nEx2-(Ex)2)(nEy2-(Ey)2)
50*4395.162-(685.6*310.14)/ 50*10047.06-(685.6*685.6)* 50*2056.8462-(310.14*310.14)
=0.49
r^2=0.49^2=0.24
SSR=SST-SSE=133.11-101.67=31.44 and degrees of freedom for regression would be 1 because there is one independent variable
Excel Output is as follows: -
State X: Poverty Rate Y: Infant Mortality (Deaths/1000 births) x^2 y^2 xy (yi-y) (yi-y)^2 Predicted Values Residuals Residuals^2 Alabama 17.5 7.61 306.25 57.9121 133.175 1.4072 1.98021184 7.038373213 0.571626787 0.326757184 Alaska 9.8 4.59 96.04 21.0681 44.982 -1.6128 2.60112384 5.339874338 -0.749874338 0.562311522 Arizona 21 5.27 441 27.7729 110.67 -0.9328 0.87011584 7.810418156 -2.540418156 6.453724408 Arkansas 16.8 8.61 282.24 74.1321 144.648 2.4072 5.79461184 6.883964224 1.726035776 2.979199499 California 13.5 5.76 182.25 33.1776 77.76 -0.4428 0.19607184 6.156036135 -0.396036135 0.15684462 Colorado 9.6 4.09 92.16 16.7281 39.264 -2.1128 4.46392384 5.295757484 -1.205757484 1.45385111 Connecticut 12.3 4.68 151.29 21.9024 57.564 -1.5228 2.31891984 5.891335011 -1.211335011 1.46733251 Delaware 15 7.28 225 52.9984 109.2 1.0772 1.16035984 6.486912539 0.793087461 0.628987721 Florida 13.2 5.16 174.24 26.6256 68.112 -1.0428 1.08743184 6.089860854 -0.929860854 0.864641208 Georgia 17.2 7.8 295.84 60.84 134.16 1.5972 2.55104784 6.972197932 0.827802068 0.685256264 Hawaii 12.7 6.73 161.29 45.2929 85.471 0.5272 0.27793984 5.979568719 0.750431281 0.563147107 Idaho 11.9 6.53 141.61 42.6409 77.707 0.3272 0.10705984 5.803101304 0.726898696 0.528381715 Illinois 13.2 7.2 174.24 51.84 95.04 0.9972 0.99440784 6.089860854 1.110139146 1.232408923 Indiana 10.5 7 110.25 49 73.5 0.7972 0.63552784 5.494283326 1.505716674 2.267182701 Iowa 9.8 3.15 96.04 9.9225 30.87 -3.0528 9.31958784 5.339874338 -2.189874338 4.795549615 Kansas 12.5 7.44 156.25 55.3536 93 1.2372 1.53066384 5.935451865 1.504548135 2.263665089 Kentucky 19 7.93 361 62.8849 150.67 1.7272 2.98321984 7.369249617 0.560750383 0.314440992 Louisiana 20.2 9.56 408.04 91.3936 193.112 3.3572 11.27079184 7.63395074 1.92604926 3.70966575 Maine 11.9 7.4 141.61 54.76 88.06 1.1972 1.43328784 5.803101304 1.596898696 2.550085446 Maryland 10.2 6.36 104.04 40.4496 64.872 0.1572 0.02471184 5.428108046 0.931891954 0.868422615 Massachusetts 12.3 5.14 151.29 26.4196 63.222 -1.0628 1.12954384 5.891335011 -0.751335011 0.564504299 Michigan 13.5 8.04 182.25 64.6416 108.54 1.8372 3.37530384 6.156036135 1.883963865 3.549319845 Minnesota 11 4.56 121 20.7936 50.16 -1.6428 2.69879184 5.604575461 -1.044575461 1.091137894 Mississippi 23.5 10.76 552.25 115.7776 252.86 4.5572 20.76807184 8.36187883 2.39812117 5.750985147 Missourri 12.7 7.42 161.29 55.0564 94.234 1.2172 1.48157584 5.979568719 1.440431281 2.074842275 Montana 15.4 4.76 237.16 22.6576 73.304 -1.4428 2.08167184 6.575146247 -1.815146247 3.294755898 Nebraska 12.2 4.33 148.84 18.7489 52.826 -1.8728 3.50737984 5.869276585 -1.539276585 2.369372404 Nevada 16.3 5.45 265.69 29.7025 88.835 -0.7528 0.56670784 6.773672089 -1.323672089 1.7521078 New Hampshire 8 6.75 64 45.5625 54 0.5472 0.29942784 4.942822653 1.807177347 3.265889965 New Jersey 12.1 3.59 146.41 12.8881 43.439 -2.6128 6.82672384 5.847218158 -2.257218158 5.095033811 New Mexico 22.7 5.35 515.29 28.6225 121.445 -0.8528 0.72726784 8.185411414 -2.835411414 8.039557888 New York 13.4 3.84 179.56 14.7456 51.456 -2.3628 5.58282384 6.133977708 -2.293977708 5.262333724 North Carolina 17.5 5.88 306.25 34.5744 102.9 -0.3228 0.10419984 7.038373213 -1.158373213 1.3418285 North Dakota 10.1 5.15 102.01 26.5225 52.015 -1.0528 1.10838784 5.406049619 -0.256049619 0.065561407 Ohio 14.8 8.33 219.04 69.3889 123.284 2.1272 4.52497984 6.442795685 1.887204315 3.561540126 Oklahoma 15 7.82 225 61.1524 117.3 1.6172 2.61533584 6.486912539 1.333087461 1.777122178 Oregon 14.2 5.96 201.64 35.5216 84.632 -0.2428 0.05895184 6.310445124 -0.350445124 0.122811785 Pennsylvania 11.3 7.45 127.69 55.5025 84.185 1.2472 1.55550784 5.670750742 1.779249258 3.165727922 Rhode Island 12.6 6.48 158.76 41.9904 81.648 0.2772 0.07683984 5.957510292 0.522489708 0.272995495 South Carolina 14.6 6.75 213.16 45.5625 98.55 0.5472 0.29942784 6.398678831 0.351321169 0.123426564 South Dakota 9.8 5.53 96.04 30.5809 54.194 -0.6728 0.45265984 5.339874338 0.190125662 0.036147767 Tennessee 17.4 6.8 302.76 46.24 118.32 0.5972 0.35664784 7.016314786 -0.216314786 0.046792087 Texas 15.7 5.84 246.49 34.1056 91.688 -0.3628 0.13162384 6.641321528 -0.801321528 0.642116191 Utah 8.6 4.56 73.96 20.7936 39.216 -1.6428 2.69879184 5.075173214 -0.515173214 0.265403441 Vermont 9.2 3.35 84.64 11.2225 30.82 -2.8528 8.13846784 5.207523776 -1.857523776 3.450394579 Virginia 9.5 7.21 90.25 51.9841 68.495 1.0072 1.01445184 5.273699057 1.936300943 3.749261342 Washington 11 5.48 121 30.0304 60.28 -0.7228 0.52243984 5.604575461 -0.124575461 0.015519046 West Virginia 18.5 7.59 342.25 57.6081 140.415 1.3872 1.92432384 7.258957482 0.331042518 0.109589148 Wisconsin 12 6.24 144 38.9376 74.88 0.0372 0.00138384 5.825159731 0.414840269 0.172092449 Wyoming 12.9 3.58 166.41 12.8164 46.182 -2.6228 6.87907984 6.023685573 -2.443685573 5.971599181 Sum Sum Sum Sum Sum SST SSE 685.6 310.14 10047.06 2056.8462 4395.162 133.109808 101.6716262 Average 6.2028Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.