5 B 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 Date PatientID 9/23/12 inpatient 1 12/11/12 Inpatient 10 11/12/12 Inpatient 100 11/29/12 Inpatient 1000 8/18/12 Inpatient 1001 4/6/13 Inpatient 1002 12/29/12 Inpatient 1003 5/20/13 Inpatient 1004 5/30/13 Inpatient 1005 4/22/13 Inpatient 1006 7/2/12 Inpatient 1007 4/20/13 Inpatient 1008 2/6/13 Inpatient 1009 8/14/12 Inpatient 101 9/8/12 Inpatient 1010 9/9/12 Inpatient 1011 3/21/13 Inpatient 1012 11/12/12 Inpatient 1013 2/20/13 Inpatient 1014 11/5/12 Inpatient 1015 6/19/12 Inpatient 1016 5/10/13 Inpatient 1017 8/25/12 Inpatient 1018 5/1/13 Inpatient 1019 9/25/12 Inpatient 102 1/31/13 Inpatient 1020 11/12/12 Inpatient 1021 2/21/13 Inpatient 1022 4/30/13 Inpatient 1023 7/8/12 Inpatient 1024 3/4/13 Inpatient 1025 6/12/12 Inpatient 1026 10/15/12 Inpatient 1027 8/9/12 Inpatient 1028 7/24/12 Inpatient 1029 3/12/13 Inpatient 103 11/7/12 Inpatient 1030 11/20/12 Inpatient 1031 D E Height Weight BM Ind BMI Category 19.9 Normal weight 36.2 Obese 30.5 Obese 79 74 72 59 56 56 69 66 79 76 64 60 66 73 78 62 78 76 74 81 62 66 67 57 67 57 74 82 58 58 66 71 76 70 80 82 81 61 177 282 225 223 259 108 126 289 177 176 186 244 214 207 192 191 102 160 106 147 264 234 103 191 141 193 283 106 167 176 114 244 263 228 149 217 277 287 45.0 Obese 58.1 Obese. 24.2 Normal weight 18.6 Normal weight 46.6 Obese 19.9 Normal weight 21.4 Normal weight 31.9 Obese 47.6 Obese 34.5 Obese 27.3 Overweight 22.2 Normal weight 34.9 Obese 11.8 Underweight 19.5 Normal weight 13.6 Underweight 15.8 Underweight 48.3 Obese 37.8 Obese 16.1 Underweight 41.3 Obese 22.1 Normal weight 41.8 Obese 36.3 Obese 11.1 Underweight 34.9 Obese 36.8 Obese 18.4 Underweight 34.0 Obese 32.0 Obese 32.7 Obese 16.4 Underweight 22.7 Normal weight 29.7 Overweight 54.2 Obese PatientSattus Inpatient Inpatient Inpatient Inpatient Inpatient Inpatient Inpatient Inpatient Inpatient inpatient Inpatient Inpatient Inpatient Anpatient Inpatient Inpatient Inpatient inpatient Inpatient Inpatient Inpatient inpatient Inpatient Inpatient Inpatient Inpatient Inpatient Inpatient Inpatient Anpatient Inpatient Inpatient Inpatient Inpatient Inpatient Anpatient Inpatient Inpatient H BMI Categories Index Category Nie 0 Underweight 18.5 Normal weight 25 Overweight 30 Obese PatientID Inpatient 10 0 BM Index L 7. Using data validation, structured table references, and index-match, obtain a correct measurement (height, weight, or BMI) for any patient chosen from a list. Data validation will not accept a structured table reference directly, but you can put a structured table reference in data validation by using the indirect function with a text string that looks like a structured table reference. Use Indirect and text strings that look like structured table references for both the row match and column match. See PatientData! 18:39 in solution file. 8. Use COUNTIF to calculate the BMI categories in the BMI Cat column of tblPatients. Count all four BMI categories. See PatientData!I12:J15. 9. Create a small column chart for BMI categories and make it look like the one in the solution file shown in the PatientData worksheet. 10. Show summary statistics, height histogram and BMI histogram like the solution StatOverview worksheet. If you are working with a personal copy of Excel you might have to add-in the Analysis Tool Pack (File > Options > Add-ins. Use the bin range shown in the solution worksheet. 11. Show histogram for random heights-actual data from de-identified health care charts. Also show summary stats. 12. Create a pivot table and pivot chart for average weight by year and quarter as shown in the Pivot-height worksheet. Group date in rows before moving date to the report filter. A FA 13. Use Excel functions to return patient data for the patient chosen in PatientData!18. Use text functions to return data to B3 and B4 of the Text worksheet. Include units as shown in the sample solution. 14. Use concatenation to create the following sentence: [Inpatient/Outpatient] number [Number] has a [height/weight/BMI] reading of [measurement] [in/lbs/].