7. The second part of the Summary sheet contains a sales associate lookup form, but the appropriate formulas have not been entered yet. a. In cell B18, enter a formula to calculate the average commission earned for the sales associate last name entered in cell A16. (Hint: Use a database function. Use the named range Earnings as the Database argument and the column label Commission Earned as the Field argument. The criteria range should be A15:B16.) b. In cell B19, enter a formula to calculate the total commission earned for that same sales associate. (Hint: Use a different database function. The function arguments are the same as those you used in step 7a.) If necessary, apply the Accounting Number Format to cells B18:B19. d. In cell B20, enter a formula to calculate the number of clients the sales associate had with annual sales reaching the target set in cell B16. (Hint: This time, use the column label Client as the Field argument. The criteria range should be A15:B16.) Remember, the values in the Client field are text values. Use the appropriate function. If necessary, apply the General number format to cell B20. 1.Test your formulas by entering Lopez as the sales associate's last name (cell A16) and >200000 as the sales target (cell B16). g. Add an IFERROR function to the formula in cell B18 to display the error text Associate not found. If you receive errors, use Evaluate Formula to troubleshoot your formulas. Reset Progre C D E Sales Summary Standard 4 5 Annual Sales Average Median Deviation $ 175,981.25 5 185,695.00 $ 59,440.78 6 Commission Earned $ 6.906.35 $ 7A27.80 $ 5,644.57 7 8 Sales Summary by Region 9 NE1 SE2 10 Total $ 11 Average 12 $ 1,279,559.00 $ 213,259.83 $ SW1 1,143,097.00 $ 142,887.13 $ SE1 1,342,197.00 $ 167,774.63 $ 13 Enter the sales associate's last name and annual sales torget in the cells below 14 if no choices are entered, the table below displays company-wide dato 15 Sales Associate's Last Name Annual Sales 16 17 18 Average Commission Earned 19 Total Commission Earned Number of Clients with Annual Sales 20 Reaching Target 21 22 23 AnnualSales 24 CommissionEarned 25 Earnings 26 Regions 27 Summary Sales Data + Sales Data'ISES4 S564 Sales Data'15654 SG564 Sales Data'!$A$3:$1564 Sales Data'15C54 SC564 SW2 NW2 NW1 NE2 1,434,727.00 S 1,494,419.00 $ 1,372,847.00 $ 1,101,148.00 5 1,566,862.00 179,340.88 $ 166,046.56 $ 196,121.00 $ 157,306.86 $ 195.157.75 2 National Pharmaceutical Sales 3 Sales Associate's Sales Associate's Sales Associate's Last Name 4 Ahjab Raminian First Name ID Client Annual Sales Commission Commission Earned Rate Salary Total Earnings Region Ahjab Raminian NE1-73647 NE1-217199 Garden State Pharmacies MAOBGyn Associates 78,133 ON 55,000 127,086 NE1 230,427 6% 13,825.62 NE1 6 Ahjab Raminian NE1-247543 MD Dental Associates 262,620 6% 15,757.30 NE 7 Ahjab Raminian NEI-235322 New England Pharmacies, Inc. 249,654 6% 14,979.24 NES Ahjab Raminian NE1-213262 New England Physicians, Inc. 226,250 6% 13,575.00 NES 9 Ahjab Raminian NE1-219129 PA Medical Practices, Inc. 232,475 6% 13,948.50 NE 10 Anderson Cynthia SE2-80660 Low County Medical Associates, Inc. 85,573 0% 51,000 82,401 582 11 Anderson Cynthia SE2-231417 NC Medical Associates 245,511 6% 14,730.66 SE2 12 Anderson Cynthia SE2-189894 Orthopaedic Medical Associates 201,459 4% 8,058.36 582 13 Anderson Cynthia SE2-877211 Palmetto Physicans 93,064 0% SE2 14 Anderson Cynthia SE2-153666 Palmetto Podiatrists, Inc. 163,025 2% 3,260.50 SE2 15 Anderson Cynthia SE2-117771 Piedmont Health Associates, Inc. 134,945 2% 2,698.90 512 16 Anderson Cynthia SE2-81906 SC Medical Associates 86,895 0% SE2 17 Anderson Cynthia SE2-115585 Upstate Pharmacies, Inc. 132,625 2% 18 Anderson George SW1-137004 AZ Ambulatory Care 145,349 2% 19 Anderson George SW1-149663 CA Chiropractic Associates, Inc. 158,778 2% 2,652.50 2,906.98 3,175,56 SE2 60,000 95,565 SWI SW1 20 Anderson George SW1-82895 Emergency Medicine Association 87,944 0% SW1 21 Anderson George SW1-183010 Joint Healthcare Associates 94.157 4% 7,766.28 SWI 22 Anderson George SW1-150820 NM Pharmacies, Inc. 160,006 2% 3,200.12 SW1 23 Anderson George SW1-126767 Southwest Dermatology 134,489 2% 2,689.78 SW1 24 Anderson George SW1-248636 Southwest Medical Associates 263,779 6% 15,826.74 SW1 25 Anderson George SW1-186345 Tri-state Pharmacies 197,695 4% 7,907.80 SWI 26 Brown Tatisha SE1-230271 Bama Chiropractic Clinics, Inc. 244,296 6% 14,657.76 50,000 107,331 1 27 Brown Tatisha SE1-119903 Gerontology Associates of MS 127,207 2% 2,544.14 SE1 28 Brown Tatisha SE1-253055 Internal Medicine of MS 268,467 6% 16.108.02 SE1 ees conce &Cardininists Inc. 140,738 2% 2,814.76 SE1