PFA

PFA


Exce Q Linear Regression Excel Quiz: Linear Regression 1) Convert "smoker" into a dummy variableHINT: Use the Excel IFS function (e.g. smoker, yes=0, smoker, no=1) 2) Use linear regression to predict the effect on Charges of age, bmi, children, and smoker.HINT: Use the data analysis tool, then choose regression, Y data will be your charges (dependent variable), and the X data will be the other variables (independent variables) Output the linear regression results on this tab. Do not use any plots or residuals. 3) Interpret the Linear regression below the output. HINT: Review the link -Linear Regression in Excel within the module, go to the bottom of the sheet and read about how-to interpret regression analysis output Be sure to address the significance of the model as well as interpret the coefficients and their significances. Provide a short interpretation of the regression results agebmichildrensmokercharges 1927.90yes16884.924 1833.771no1725.5523 28333no4449.462 3322.7050no21984.47061 3228.880no3866.8552 3125.740no3756.6216 4633.441no8240.5896 3727.743no7281.5056 3729.832no6406.4107 6025.840no28923.13692 2526.220no2721.3208 6226.290yes27808.7251 2334.40no1826.843 5639.820no11090.7178 2742.130yes39611.7577 1924.61no1837.237 5230.781no10797.3362 2323.8450no2395.17155 5640.30no10602.385 3035.30yes36837.467 6036.0050no13228.84695 3032.41no4149.736 1834.10no1137.011 3431.921yes37701.8768 3728.0252no6203.90175 5927.723no14001.1338 6323.0850no14451.83515 5532.7752no12268.63225 2317.3851no2775.19215 3136.32yes38711 2235.60yes35585.576 1826.3150no2198.18985 1928.65no4687.797 6328.310no13770.0979 2836.41yes51194.55914 1920.4250no1625.43375 6232.9653no15612.19335 2620.80no2302.3 3536.671yes39774.2763 6039.90yes48173.361 2426.60no3046.062 3136.632no4949.7587 4121.781no6272.4772 3730.82no6313.759 3837.051no6079.6715 5537.30no20630.28351 1838.6652no3393.35635 2834.770no3556.9223 6024.530no12629.8967 3635.21yes38709.176 1835.6250no2211.13075 2133.632no3579.8287 48281yes23568.272 3634.430yes37742.5757 4028.693no8059.6791 5836.9552yes47496.49445 5831.8252no13607.36875 1831.682yes34303.1672 5322.881yes23244.7902 3437.3352no5989.52365 4327.363no8606.2174 2533.664no4504.6624 6424.71no30166.61817 2825.9351no4133.64165 2022.420yes14711.7438 1928.90no1743.214 6139.12no14235.072 4026.3151no6389.37785 4036.190no5920.1041 2823.983yes17663.1442 2724.750yes16577.7795 3128.55no6799.458 5328.13no11741.726 5832.011no11946.6259 4427.42no7726.854 5734.010no11356.6609 2929.591no3947.4131 2135.530no1532.4697 2239.8050no2755.02095 4132.9650no6571.02435 3126.8851no4441.21315 4538.2850no7935.29115 2237.621yes37165.1638 4841.234no11033.6617 3734.82yes39836.519 4522.8952yes21098.55405 5731.160yes43578.9394 5627.20no11073.176 4627.740no8026.6666 5526.980no11082.5772 2139.490no2026.9741 5324.7951no10942.13205 5929.833yes30184.9367 3534.772no5729.0053 6431.32yes47291.055 2837.621no3766.8838 5430.83no12105.32 5538.280no10226.2842 5619.950yes22412.6485 3819.30yes15820.699 4131.60no6186.127 3025.460no3645.0894 1830.1150no21344.8467 6129.923yes30942.1918 3427.51no5003.853 2028.0251yes17560.37975 1928.41no2331.519 2630.8752no3877.30425 2927.940no2867.1196 6335.090yes47055.5321 5433.631no10825.2537 5529.72no11881.358 3730.80no4646.759 2135.720no2404.7338 5232.2053no11488.31695 6028.5950no30259.99556 5849.060no11381.3254 2927.941yes19107.7796 4927.170no8601.3293 3723.372no6686.4313 4437.12no7740.337 1823.750no1705.6245 2028.9750no2257.47525 4431.351yes39556.4945 4733.9153no10115.00885 2628.7850no3385.39915 1928.30yes17081.08 5237.40no9634.538 3217.7652yes32734.1863 3834.72no6082.405 5926.5050no12815.44495 6122.040no13616.3586 5335.92no11163.568 1925.5550no1632.56445 2028.7850no2457.21115 2228.050no2155.6815 1934.10no1261.442 2225.1750no2045.68525 5431.93no27322.73386 22360no2166.732 3422.422no27375.90478 2632.491no3490.5491 3425.32yes18972.495 2929.7352no18157.876 3028.693yes20745.9891 2938.833no5138.2567 4630.4953yes40720.55105 5137.731no9877.6077 5337.431no10959.6947 1928.41no1842.519 3524.131no5125.2157 4829.70no7789.635 3237.1453no6334.34355 4223.370yes19964.7463 4025.461no7077.1894 4439.520no6948.7008 4824.420yes21223.6758 1825.1750yes15518.18025 3035.530yes36950.2567 5027.833no19749.38338 4226.60yes21348.706 1836.850yes36149.4835 5439.61no10450.552 3229.82no5152.134 3729.640no5028.1466 4728.2154no10407.08585 20375no4830.63 3233.1553no6128.79745 1931.8251no2719.27975 2718.9053no4827.90495 6341.470no13405.3903 4930.30no8116.68 1815.960no1694.7964 3534.81no5246.047 2433.3450no2855.43755 6337.70yes48824.45 3827.8352no6455.86265 5429.21no10436.096 4628.92no8823.279 4133.1553no8538.28845 5828.5950no11735.87905 1838.280no1631.8212 2219.953no4005.4225 4426.410no7419.4779 4430.692no7731.4271 3641.8953yes43753.33705 2629.922no3981.9768 3030.93no5325.651 4132.21no6775.961 2932.112no4922.9159 6131.570no12557.6053 3626.20no4883.866 2525.740no2137.6536 5626.61no12044.342 1834.430no1137.4697 1930.590no1639.5631 3932.80no5649.715 4528.62no8516.829 5118.050no9644.2525 6439.330no14901.5167 1932.110no2130.6759 4832.231no8871.1517 6024.0350no13012.20865 2736.080yes37133.8982 4622.30no7147.105 2828.881no4337.7352 5926.40no11743.299 3527.742yes20984.0936 6331.80no13880.949 4041.231no6610.1097 20331no1980.07 4030.8754no8162.71625 2428.52no3537.703 3426.731no5002.7827 4530.92no8520.026 4137.12no7371.772 5326.60no10355.641 2723.10no2483.736 2629.921no3392.9768 2423.210no25081.76784 3433.71no5012.471 5333.250no10564.8845 3230.83no5253.524 1934.80yes34779.615 4224.640yes19515.5416 5533.883no11987.1682 2838.060no2689.4954 5841.910no24227.33724 4131.6351no7358.17565 4725.462no9225.2564 4236.1951no7443.64305 5927.833no14001.2867 1917.80no1727.785 5927.51no12333.828 3924.512no6710.1919 4022.222yes19444.2658 1826.730no1615.7667 3138.392no4463.2051 1929.070yes17352.6803 4438.061no7152.6714 2336.672yes38511.6283 3322.1351no5354.07465 5526.81no35160.13457 4035.33no7196.867 6327.740yes29523.1656 5430.020no24476.47851 6038.060no12648.7034 2435.860no1986.9334 1920.91no1832.094 2928.9751no4040.55825 1817.292yes12829.4551 6332.22yes47305.305 5434.212yes44260.7499 2730.33no4260.744 5031.8250yes41097.16175 5525.3653no13047.33235 5633.630yes43921.1837 3840.150no5400.9805 5124.4154no11520.09985 1931.920yes33750.2918 5825.20no11837.16 2026.841yes17085.2676 5224.323yes24869.8368 1936.9550yes36219.40545 5338.063no20462.99766 4642.353yes46151.1245 4019.81yes17179.522 5932.3953no14590.63205 4530.21no7441.053 4925.841no9282.4806 1829.371no1719.4363 5034.22yes42856.838 4137.052no7265.7025 5027.4551no9617.66245 2527.550no2523.1695 4726.62no9715.841 1920.6152no2803.69785 2224.30no2150.469 5931.792no12928.7911 5121.561no9855.1314 4028.121yes22331.5668 5440.5653yes48549.17835 3027.6451no4237.12655 5532.3951no11879.10405 5231.20no9625.92 4626.621no7742.1098 4648.072no9432.9253 6326.220no14256.1928 5936.7651yes47896.79135 5226.43no25992.82104 2833.40no3172.018 2929.641no20277.80751 2545.542yes42112.2356 2228.820no2156.7518 2526.83no3906.127 1822.990no1704.5681 1927.70yes16297.846 4725.411yes21978.6769 3134.393yes38746.3551 4828.881no9249.4952 3627.553no6746.7425 5322.613yes24873.3849 5637.512no12265.5069 28332no4349.462 57382no12646.207 2933.3452no19442.3535 2827.52no20177.67113 3033.331no4151.0287 5834.8650no11944.59435 4133.062no7749.1564 5026.60no8444.474 1924.70no1737.376 4335.973yes42124.5153 4935.860no8124.4084 2731.40yes34838.873 5233.250no9722.7695 5032.2050no8835.26495 5432.7750no10435.06525 4427.6450no7421.19455 3237.3351no4667.60765 3425.271no4894.7533 2629.644no24671.66334 3430.80yes35491.64 5740.9450no11566.30055 2927.20no2866.091 4034.1051no6600.20595 2723.211no3561.8889 4536.482yes42760.5022 6433.81yes47928.03 5236.70no9144.565 6136.3851yes48517.56315 5227.360yes24393.6224 6131.160no13429.0354 5628.7850no11658.37915 4335.722no19144.57652 6434.50no13822.803 6025.740no12142.5786 6227.551no13937.6665 5032.31yes41919.097 4627.721no8232.6388 2427.60no18955.22017 6230.020no13352.0998 6027.550no13217.0945 6336.7650no13981.85035 4941.474no10977.2063 3429.263no6184.2994 3335.752no4889.9995 4633.3451no8334.45755 3629.921no5478.0368 1927.8350no1635.73365 5723.180no11830.6072 5025.60no8932.084 3027.70no3554.203 3335.2450no12404.8791 1838.280no14133.03775 4627.60no24603.04837 4643.893no8944.1151 4729.833no9620.3307 2341.910no1837.2819 1820.790no1607.5101 4832.32no10043.249 3530.51no4751.07 1921.70yes13844.506 2126.41no2597.779 2121.892no3180.5101 4930.781no9778.3472 5632.33no13430.265 4224.9852no8017.06115 4432.0152no8116.26885 1830.43no3481.868 6121.090no13415.0381 5722.230no12029.2867 4233.1551no7639.41745 2632.92yes36085.219 2033.330no1391.5287 2328.310yes18033.9679 3924.893yes21659.9301 2440.150yes38126.2465 6430.1153no16455.70785 6231.461no27000.98473 2717.9552yes15006.57945 5530.6850yes42303.69215 55330no20781.48892 3543.342no5846.9176 4422.1352no8302.53565 1934.40no1261.859 5839.050no11856.4115 5025.3652no30284.64294 2622.610no3176.8159 2430.213no4618.0799 4835.6254no10736.87075 1937.430no2138.0707 4831.4451no8964.06055 4931.351no9290.1395 4632.32no9411.005 4619.8550no7526.70645 4334.43no8522.003 2131.020no16586.49771 6425.62no14988.432 1838.170no1631.6683 5120.60no9264.797 4747.521no8083.9198 6432.9650no14692.66935 4932.33no10269.46 3120.40no3260.199 5238.382no11396.9002 3324.310no4185.0979 4723.61no8539.671 3821.123no6652.5288 3230.031no4074.4537 1917.480no1621.3402 4420.2351yes19594.80965 2617.1952yes14455.64405 2523.95no5080.096 1935.150no2134.9015 4335.641no7345.7266 5234.10no9140.951 3622.62yes18608.262 6439.161no14418.2804 6326.980yes28950.4692 6433.880yes46889.2612 6135.860yes46599.1084 4032.7751yes39125.33225 2530.590no2727.3951 4830.22no8968.33 4524.315no9788.8659 3827.2651no6555.07035 1829.1650no7323.734819 2116.8151no3167.45585 2730.43no18804.7524 1933.10no23082.95533 2920.2352no4906.40965 4226.90no5969.723 6030.50no12638.195 3128.5951no4243.59005 6033.113no13919.8229 2231.730no2254.7967 3528.93no5926.846 5246.755no12592.5345 2629.450no2897.3235 3132.681no4738.2682 3333.50yes37079.372 1843.010no1149.3959 5936.521no28287.89766 5626.6951yes26109.32905 4533.10no7345.084 6029.640no12730.9996 5625.650no11454.0215 4029.60no5910.944 3538.61no4762.329 3929.64no7512.267 3024.131no4032.2407 2423.40no1969.614 2029.7350no1769.53165 3246.532no4686.3887 5937.40no21797.0004 5530.142no11881.9696 5730.4950no11840.77505 5639.60no10601.412 40333no7682.67 4936.633no10381.4787 42300yes22144.032 6238.0952no15230.32405 5625.9350no11165.41765 1925.1750no1632.03625 3028.381yes19521.9682 6028.71no13224.693 5633.822no12643.3778 2824.321no23288.9284 1824.091no2201.0971 2732.670no2497.0383 1830.1150no2203.47185 1929.80no1744.465 4733.3450no20878.78443 5425.13yes25382.297 6128.311yes28868.6639 2428.50yes35147.52848 2535.6250no2534.39375 2136.850no1534.3045 2332.560no1824.2854 6341.3253no15555.18875 4937.512no9304.7019 1831.350no1622.1885 5139.51no9880.068 4834.33no9563.029 3131.0650no4347.02335 5421.473no12475.3513 1928.70no1253.936 4438.060yes48885.13561 5331.161no10461.9794 1932.90no1748.774 6125.080no24513.09126 1825.080no2196.4732 6143.40no12574.049 2125.74yes17942.106 2027.930no1967.0227 3123.62no4931.647 4528.72no8027.968 4423.982no8211.1002 6239.20no13470.86 2934.40yes36197.699 4326.030no6837.3687 5123.211yes22218.1149 1930.250yes32548.3405 3828.931no5974.3847 3730.8753no6796.86325 2231.351no2643.2685 2123.752no3077.0955 2425.270no3044.2133 5728.70no11455.28 5632.111no11763.0009 2733.660no2498.4144 5122.420no9361.3268 1930.40no1256.299 3928.31yes21082.16 5835.70no11362.755 2035.311no27724.28875 4530.4952no8413.46305 35311no5240.765 3130.8750no3857.75925 5027.360no25656.57526 3244.220no3994.1778 5133.9150no9866.30485 3837.730no5397.6167 4226.071yes38245.59327 1833.880no11482.63485 1930.592no24059.68019 5125.81no9861.025 4639.4251no8342.90875 1825.460no1708.0014 5742.131yes48675.5177 6231.730no14043.4767 5929.72no12925.886 3736.190no19214.70553 6440.480no13831.1152 3828.0251no6067.12675 3338.93no5972.378 4630.22no8825.086 4628.051no8233.0975 5331.350no27346.04207 34383no6196.448 2031.792no3056.3881 6336.30no13887.204 5447.410yes63770.42801 5430.210no10231.4999 4925.842yes23807.2406 2835.4350no3268.84665 5446.72no11538.421 2528.5950no3213.62205 4346.20yes45863.205 6330.80no13390.559 3228.930no3972.9247 6221.40no12957.118 5231.732no11187.6567 2541.3250no17878.90068 2823.82no3847.674 4633.441no8334.5896 3434.210no3935.1799 3534.1053yes39983.42595 1935.530no1646.4297 4619.952no9193.8385 5432.680no10923.9332 2730.50no2494.022 5044.771no9058.7303 1832.122no2801.2588 1930.4950no2128.43105 3840.5651no6373.55735 4130.592no7256.7231 4931.95no11552.904 4840.5652yes45702.02235 3129.10no3761.292 1837.291no2219.4451 3043.122no4753.6368 6236.861no31620.00106 5734.2952no13224.05705 5827.170no12222.8983 2226.840no1664.9996 3138.0951yes58571.07448 5230.21no9724.53 2523.4650no3206.49135 5925.461no12913.9924 1930.590no1639.5631 3945.432no6356.2707 3223.651no17626.23951 1920.70no1242.816 3328.271no4779.6023 2120.2353no3861.20965 3430.211yes43943.8761 6135.910no13635.6379 3830.691no5976.8311 58290no11842.442 4719.571no8428.0693 2031.132no2566.4707 2121.851yes15359.1045 4140.260no5709.1644 4633.7251no8823.98575 4229.482no7640.3092 3433.251no5594.8455 4332.62no7441.501 5237.5252no33471.97189 1839.160no1633.0444 5131.6350no9174.13565 5625.30no11070.535 6439.053no16085.1275 1928.310yes17468.9839 5134.10no9283.562 2725.1750no3558.62025 5923.6550yes25678.77845 2826.982no4435.0942 3037.82yes39241.442 4729.371no8547.6913 3834.82no6571.544 1833.1550no2207.69745 34193no6753.038 20330no1880.07 4736.631yes42969.8527 5628.5950no11658.11505 4925.62yes23306.547 1933