sample. Assignment 6 3 Use the STDEV.S function in cell 17 to calculate the sample standard deviation of the height of the men in the sample. 7 5 Use the CONFIDENDE.NORM function in cell 18 to calculate the 95% confidence interval for the height of the men. 8 3 Use the AVERAGE function in cell J6 to calculate the average BMI of the men in the sample. 9 3 Use the STDEV.S function in cell J7 to calculate the sample standard deviation of the BMI of the men in the sample. 10 5 Use the CONFIDENDE.NORM function in cell J8 to calculate the 95% confidence interval for the BMI of the men. 11 4 Use the CORREL function in cell J11 to determine if there is a statistical relationship between the height and weight of the men in the sample. Use the FORECAST.LINEAR function (or the FORECAST function if you are not using Excel 12 5 2016) in cell J15 to predict Jim's weight from his height. Reference Jim's height in cell J14 and appropriate columns from the sample data table. In cells J18 and J19, use the information you calculated about the confidence interval for 13 weight to determine the lower and upper bounds for the 95% confidence interval for your 6 prediction of Jim's weight in cell J15. Be sure to reference the confidence interval calculation rather than hard coding it in your calculation. Total: 50Insert Draw Page Layout Formulas Data Review View Assignment SH action Task Submit View leet Guide Report 4 X V fx =CONFIDENCE. NORM(0.05,H7,H5) A B C D E F G - H I K L 174 M 194.04 N 71.30 O 26.83 P 175 122.90 68.82 18.24 176 181.26 71.09 25.22 177 129.15 67.55 19.90 178 167.34 67.43 25.87 179 211.36 70.25 30.11 180 218.92 70.25 31.19 181 200.71 68.00 30.5 182 122.90 67.80 18.79 183 124.36 68.25 18.77 184 179.55 71.17 24.92 185 197.01 70.89 27.56 186 174.77 68.90 25.88 187 169.85 71.18 23.56 188 208.79 59.33 30.53 92 189 143.54 71.09 19.96 190 153.48 69.72 22.20 94 191 157.98 71.12 21.96 95 192 152.88 69.41 22.31 96 193 173.23 68.64 25.85 197 194 168.29 71.18 23.35 198 195 190.75 70.31 27.13 199 196 161.66 68.05 24.54 200 197 205.68 69.23 30.17 201 198 192.53 69.58 27.96 202 199 186.49 68.69 27.78 203 200 139.13 69.95 19.99 204 BMI Baseball + -me Insert Draw Page Layout Formulas Data Review View Assignment struction Task Submit View Sheet Guide Report 18 X V fx =CONFIDENCE.NORM(0.05, H7,H5) A B C D E F G J K L M Survey of Men's Weight and Height Summary Statistics Subject Weight Height BMI 1 185.22 72.74 24.61 Weight Height BMI 2 173.09 69.03 25.53 Count 200.00 173.24 70.21 24.71 Average 173.67 70.15 24.78 155.81 68.76 23.17 Standard* 32.45 1.86 4.34 162.83 70.94 22.75 95% CI** 4.50 141.69 67.50 21.86 10 194.85 76.92 23.15 Correlation 8 201.81 70.74 28.35 Correlation coefficient -> 9 136.98 63.36 23.99 10 156.22 70.40 22.16 Forecast*** 11 194.71 70.61 27.46 Jim's Height (in inches) -> 70 12 125.78 72.47 16.84 Predict Jim's Weight -> 16 13 183.75 70.82 25.76 17 14 128.15 71.02 17.86 Forecast Confidence Interval 18 15 202.77 70.00 29.09 Lower Weight Bound -> 19 16 154.39 70.06 22.11 Upper Weight Bound -> 20 17 139.91 70.35 19.87 21 18 200.67 72.92 26.53 *Note: the STDEV.S function is not available in Excel 2007. Ifyou 22 19 206.44 72.7 27.41 are using Excel 2007, use the STDEV funtion instead. 23 20 250.26 79.44 27.88 24 21 153.79 70.09 22.01 ** Note: the CONFIDENCE. NORM function is not available in Excel 25 22 219.13 72.38 29.41 2007. If you are using Excel 2007, use the CONFIDENCE function 26 23 181.56 70.93 25.37 instead. 27 24 166.69 70.58 23.53 28 25 207.97 68.89 30.80 *** Note: the FORECAST. LINEAR function is not available prior to BMI Baseball +Task # Points Task Description 1 2 Use the COUNT function in cell H5 to calculate the number of men in the sample. Use C4:C203 as the argument for your function. 2 3 Use the AVERAGE function in cell H6 to calculate the average weight of the men in the sample. 3 3 Use the STDEV.S function in cell H7 to calculate the sample standard deviation of the weight of the men in the sample. 4 5 Use the CONFIDENCE.NORM function in cell H8 to calculate the 95% confidence interval for the weight of the men. 5 3 Use the AVERAGE function in cell 16 to calculate the average height of the men in the sample. 6 3 Use the STDEV.S function in cell 17 to calculate the sample standard deviation of the height of the men in the sample. 5 Use the CONFIDENDE.NORM function in cell 18 to calculate the 95% confidence interval for the height of the men. 8 3 Use the AVERAGE function in cell 36 to calculate the average BMI of the men in the sample. 9 3 Use the STDEV.S function in cell 37 to calculate the sample standard deviation of the BMI of the men in the sample. 5 Use the CONFIDENDE.NORM function in cell J8 to calculate the 95% confidence interval for 10 the BMI of the men. n cell J11 to determine if there is a statistical relationship