1. In the range N6:O8, Bao wants to list key findings from the data in the AccountReps table. In cell O6, enter a formula using the DAVERAGE function to average the sales of the Destra medical device. Use a range reference to the AccountReps table (range A10:I29) as the database, "Total Sales" as the field, and the range Q5:Q6 as the criteria.
2. In cell O7, enter a formula using the SUMIF function that totals the sales for the Destra medical device. Use a range reference to the Device values (range E11:E29) as the range, cell Q6 as the criteria, and a range reference to the Total Sales values (range F11:F29) as the sum_range.
3. 

Transcribed Image Text:

2 3 4 5 Client ID 6 7 8 9 10 Client ID 11 12 13 14 15 16 17 18 19 A 20 21 22 23 24 38 39 Client Name 2340 Brevard Healthcare 2244 Bailey Healthcare 2198 Cox Family Care Client Name 2911 East Grove Pediatric 2326 St. John's Hospital 2141 Capital Clinic 2355 Ridgeway Hospital 2855 Parkview Medical 3068 Donovan Medical Center 2287 Agora Hospital 2077 Northwest Hospital 25 26 27 28 29 30 Total 31 32 33 34 Client ID 35 36 37 2701 Associated Healthcare 3014 Midwest Clinic 2275 Gulf Medical Center 2612 Advanced Medical Center 2310 LaMont Medical Clinic 2219 Mercy West 2203 First Choice Medical 3115 Dermatology Associates CHent Name 2340 Brevard Healthcare 2198 Cox Family Care 2911 East Grove Pediatric 2275 Gulf Medical Center 19 C Account Rep Medical Monitoring Devices Gonzalez Gonzalez Hayati Hayati Hirko Hirko Kruse Kruse Kruse Malick Malick Silva Silva Soto Soto Wilkins Wilkins Account Rep Area Flowers Flowers Criteria Range Area Device SE Account Rep Flowers Gonzalez Gonzalez Silva Account Representatives SE SE SE SE MW MW NE NE NW E NW NW MW MW SE SE NY NW SW SW Area SE SE SE SE F Extract Range Total Sales >=100000 Device Total Sales DataSigns $ 1,05,500 Destra $ $ $ 58,700 1,58,500 1,40,500 AnesPro Vivo III 02 Monitor $ $ Destra $ Critiscope $ Vivo II Vivo II 1,20,100 47,750 2,20,500 1,27,500 1,07,500 $ $ Vivo II $ Vivo II $ 02 Monitor $ Destra $ Critiscope $ Destra $ $ Vivo III Vivo III $ 02 Monitor $ AnesPro $ 57,500 28,500 98,750 64,500 1,20,750 68,770 66,800 30,650 56,300 52.500 $ 17,31,570 Device Total Sales DataSigns $ $ 1,05,500 AnesPro $ 1,58,500 Vivo III $ 1,40,500 Critiscope 1,20,750 $ G Eval Score Eval Score H Tier Eval Score 6.0 Silver 7.0 Silver 7.1 Tier 6.5 Silver 5.0 Bronze 7.5 Silver 7.0 Silver 6.5 Silver $ 7.0 Silver $ 9.0 Platinum $ 8.0 Gold $ 9.5 Platinum $ 9.0 Platinum $ 8.5 Gold 5.5 Bronze 7.0 Silver 8.0 Gold 8.0 Gold 5.0 Bronze 4.5 Bronze Tier 6.5 Silver 7.5 Silver 7.0 Silver 8.0 Gold Rep Bonus Rep Bonus $ S $ $ $ $ $ S $ $ $ $ I $ $ $ $ - . - . 11,025 5,375 2,875 - - . . 19,275, Rep Bonus $ S - - - J K Bonus Tiers Score Tier 0 Bronze Silver Gold Platinum 6 8 L 9 M N Key Findings 0 Statistic Result Average Destra sales: $ 59,930 Total Destra sales: $2,39,720 4 Destra reps: P Q Criterion Device Destra 2 3 4 5 Client ID 6 7 8 9 10 Client ID 11 12 13 14 15 16 17 18 19 A 20 21 22 23 24 38 39 Client Name 2340 Brevard Healthcare 2244 Bailey Healthcare 2198 Cox Family Care Client Name 2911 East Grove Pediatric 2326 St. John's Hospital 2141 Capital Clinic 2355 Ridgeway Hospital 2855 Parkview Medical 3068 Donovan Medical Center 2287 Agora Hospital 2077 Northwest Hospital 25 26 27 28 29 30 Total 31 32 33 34 Client ID 35 36 37 2701 Associated Healthcare 3014 Midwest Clinic 2275 Gulf Medical Center 2612 Advanced Medical Center 2310 LaMont Medical Clinic 2219 Mercy West 2203 First Choice Medical 3115 Dermatology Associates CHent Name 2340 Brevard Healthcare 2198 Cox Family Care 2911 East Grove Pediatric 2275 Gulf Medical Center 19 C Account Rep Medical Monitoring Devices Gonzalez Gonzalez Hayati Hayati Hirko Hirko Kruse Kruse Kruse Malick Malick Silva Silva Soto Soto Wilkins Wilkins Account Rep Area Flowers Flowers Criteria Range Area Device SE Account Rep Flowers Gonzalez Gonzalez Silva Account Representatives SE SE SE SE MW MW NE NE NW E NW NW MW MW SE SE NY NW SW SW Area SE SE SE SE F Extract Range Total Sales >=100000 Device Total Sales DataSigns $ 1,05,500 Destra $ $ $ 58,700 1,58,500 1,40,500 AnesPro Vivo III 02 Monitor $ $ Destra $ Critiscope $ Vivo II Vivo II 1,20,100 47,750 2,20,500 1,27,500 1,07,500 $ $ Vivo II $ Vivo II $ 02 Monitor $ Destra $ Critiscope $ Destra $ $ Vivo III Vivo III $ 02 Monitor $ AnesPro $ 57,500 28,500 98,750 64,500 1,20,750 68,770 66,800 30,650 56,300 52.500 $ 17,31,570 Device Total Sales DataSigns $ $ 1,05,500 AnesPro $ 1,58,500 Vivo III $ 1,40,500 Critiscope 1,20,750 $ G Eval Score Eval Score H Tier Eval Score 6.0 Silver 7.0 Silver 7.1 Tier 6.5 Silver 5.0 Bronze 7.5 Silver 7.0 Silver 6.5 Silver $ 7.0 Silver $ 9.0 Platinum $ 8.0 Gold $ 9.5 Platinum $ 9.0 Platinum $ 8.5 Gold 5.5 Bronze 7.0 Silver 8.0 Gold 8.0 Gold 5.0 Bronze 4.5 Bronze Tier 6.5 Silver 7.5 Silver 7.0 Silver 8.0 Gold Rep Bonus Rep Bonus $ S $ $ $ $ $ S $ $ $ $ I $ $ $ $ - . - . 11,025 5,375 2,875 - - . . 19,275, Rep Bonus $ S - - - J K Bonus Tiers Score Tier 0 Bronze Silver Gold Platinum 6 8 L 9 M N Key Findings 0 Statistic Result Average Destra sales: $ 59,930 Total Destra sales: $2,39,720 4 Destra reps: P Q Criterion Device Destra