. On the Sales Data worksheet, enter a formula in cell J4 to find the sales associate's region by extracting the first three characters of the sales associate's ID in cell C4. Use cell references where appropriate. Fill the formula down through cell J64. B D E F G H 1 National Pharmaceutical Sales Sales Summary Annual Sales Commission Earned Standard Deviation 59,440.78 4,640.15 Average 175,981.25 $ 5,972.69 $ $ $ Median 185,695.00 $ 7,427.80 $ Sales Summary by Region SW1 SE1 NE1 SE2 SW2 NW1 NE NW2 0 Total Average Enter the sales associate's last name and annual sales target in the cells below if no choices are entered, the table below displays company-wide data Sales Associate's Last Name Annual Sales Average Commission Earned Total Commission Earned Number of Clients with Annual Sales Reaching Target Annual Sales Commission Earned Earnings Regions ='Sales Data'!$E$4:$E$64 ="Sales Data'!$G$4:$G$64 ='Sales Data'l$A$3:$1$64 ='Sales Data'!$C$4:$C$64 National maceutical Sales Sales Associate's Sales Associate's Sales Associate's Last Name First Name Commission Rate Commission Earned ID Client Annual Sales Salary Total Earnings Region 55,000 108,309 Raminian Raminian Raminian Raminian Raminian Raminian Cynthia Cynthia 9,217.08 15,757.20 9,986.16 9,050.00 9,299.00 51,000 77,491 9,820.44 8,058.36 Cynthia 3,260.50 2,698.90 Ahjab - Ahjab - Ahjab Ahjab - Ahjab Ahjab o Anderson 1 Anderson 2 Anderson 3 Anderson 4 Anderson 5 Anderson 6 Anderson 7 Anderson B Anderson 9 Anderson Anderson 1 Anderson 2 Anderson 3 Anderson 4 Anderson 5 Anderson 6 Brown 7 Brown B Brown 9 Brown Cynthia Cynthia Cynthia Cynthia Cynthia George 2,652.50 2,906.98 3,175.56 60,000 95,565 George NE1-73647 NE1-217199 NE1-247543 NE1-235322 NE1-213262 NE1-219129 SE2-80660 SE2-231417 SE2-189894 SE2-87721 SE2-153666 SE2-117771 SE2-81906 SE2-115585 SW1-137004 SW1-149663 SW1-82895 SW1-183010 SW1-150820 SW1-126767 SW1-248636 SW1-186345 SE1-230271 SE1-119903 SE1-253055 SE1-132658 SE1-77149 SE1-157263 SE1-230063 SE1-151999 SW2-214933 SW2-94637 SW2-92179 SW2-194117 SW2-140911 SW2-202653 SW2-175034 Garden State Pharmacies MA OBGyn Associates MD Dental Associates New England Pharmacies, Inc. New England Physicians, Inc. PA Medical Practices, Inc. Low County Medical Associates, Inc. NC Medical Associates Orthopaedic Medical Associates Palmetto Physicans Palmetto Podiatrists, Inc. Piedmont Health Associates, Inc. SC Medical Associates Upstate Pharmacies, Inc. AZ Ambulatory Care CA Chiropractic Associates, Inc. Emergency Medicine Association Joint Healthcare Associates NM Pharmacies, Inc. Southwest Dermatology Southwest Medical Associates Tri-state Pharmacies Bama Chiropractic Clinics, Inc. Gerontology Associates of MS Internal Medicine of MS LA Cardiologists, Inc. Louisiana Dermatologists, Inc. MS Women's Clinics, Inc. Physicians of MS, Inc. Women's Medicine of FL, Inc. Ambulatory Care of TX, Inc. CO Pharmacies, Inc. EMTS of Co, Inc. Lone Star Medicine Nephrology Associates, Inc. NV Emergency Medical Associates, Inc. OK Clinics George George George George George George Tatisha Tatisha Tatisha Tatisha Tatisha Tatisha Tatisha Tatisha Jan 78,133 0% 230,427 4% 262,620 6% 249,654 4% 226,250 4% 232,475 4% 85,573 0% 245,511 4% 201,459 4% 93,064 0% 163,025 2% 134,945 2% 86,895 0% 132,625 2% 145,349 2% 158,778 2% 87,944 0% 194,157 4% 160,006 2% 134,489 2% 263,779 6% 197,695 4% 244,296 4% 127,207 2% 268,467 6% 140,738 2% 81,848 0% 166,841 2% 244,074 4% 161,256 2% 228,023 4% 99,402 0% 97,794 0% 205,940 4% 149,494 296 214,995 4% 185,695 4% 7,766.28 3,200.12 2,689.78 15,826.74 7,907.80 9,771.84 2,544.14 16,108.02 2,814.76 50,000 97,564 Brown 3,336.82 9,762.96 3,225.12 9,120.92 62,000 107,990 1 Brown 2 Brown 3 Brown 4 Johansen 5 Johansen 6 Johansen 7 Johansen 8 Johansen 9 Johansen o Johansen Jan Jan Jan Jan Jan 8.237.60 2,989.88 8,599.80 7,427.80 Jan 2,814.76 3,336.82 9,762.96 3,225.12 9,120.92 62,000 107,990 Tatisha Tatisha Tatisha Tatisha Tatisha Jan Jan Jan Jan Jan Jan Jan 8,237.60 2,989.88 8,599.80 7,427.80 9,614,48 55,000 111,986 7,507.68 8,807.12 Brown Brown Brown e Brown B Brown Johansen Johansen Johansen Johansen Johansen Johansen Johansen Johansen Johansen Lopez Lopez 5 Lopez 5 Lopez - Lopez 3 Lopez Lopez Quarles Quarles e Quarles B Quarles Quarles 5 Quarles Quarles Schmidt Schmidt Schmidt Schmidt 1 Schmidt e Schmidt Schmidt Schmidt SE1-132658 SE1-77149 SE1-157263 SE1-230063 SE1-151999 SW2-214933 SW2-94637 SW2-92179 SW2-194117 SW2-140911 SW2-202653 SW2-175034 SW2-226564 SW2-68539 NW1-176917 NW1-207538 NW1-73903 NW1-173347 NW1-198233 NW1-218623 NW1-245472 NE2-69555 NE2-175858 NE2-150501 NE2-123129 NE2-192148 NE2-149537 NE2-177202 NW2-210844 NW2-213043 NW2-186063 NW2-149341 NW2-234369 NW2-146676 NW2-255982 NW2-80590 LA Cardiologists, Inc. Louisiana Dermatologists, Inc. MS Women's Clinics, Inc. Physicians of MS, Inc. Women's Medicine of FL, Inc. Ambulatory Care of TX, Inc. CO Pharmacies, Inc. EMTS of CO, Inc. Lone Star Medicine Nephrology Associates, Inc. NV Emergency Medical Associates, Inc. OK Clinics Pharmacies of Nevada, Inc. TEX-MEX Pharmacies, Inc. OBGyn Associates of Montana, Inc. Oregon Opthamology, Inc. Orthodontial, Inc. Seattle Psychiatric Associates, Inc. Seattle Psychologists, Inc. Utah Urology Associates, Inc. Washington Physicians, Inc. Baltimore Bariatrics, Inc. Connecticut Pulmonary Associates, Inc. DC Dialysis Delaware Dental Associates Maine Medical Associates, Inc. Maryland Pharmacies, Inc. Rhode Island Gastroenterology, Inc. Bellevue Bariatics, Inc. Boise Oncology Casper Cardiology, Inc. Cheyenne Cardiologists, Inc. ID Internal Medicine Associates Laramie EMTS, Inc. WY OBGyn, Inc. Wyoming Health Care, Inc. 140,738 2% 81,848 0% 166,841 2% 244,074 4% 161,256 2% 228,023 4% 99,402 0% 97,794 0% 205,940 4% 149,494 2% 214,995 4% 185,695 4% 240,362 4% 72,714 0% 187,692 4% 220,178 4% 78,405 0% 183,905 4% 210,306 4% 231,938 49 260,423 6% 73,792 0% 186,569 4% 159,668 2% 130,628 2% 203,851 4% 158,645 2% 187,995 4% 223,686 496 226,019 4% 197,395 4% 158,437 2% 248,644 4% 155,610 2% 271,572 6% 85,499 0% Jan Jan Julieta Julieta Julieta Julieta Julieta Julieta Julieta Marques Marques Marques Marques Marques Marques Marques Matthew Matthew Matthew Matthew Matthew Matthew Matthew Matthew 7,356,20 8,412.24 9,277.52 15,625.38 58,000 90,115 7,462.76 3,193.36 2,612.56 8,154.04 3,172.90 7,519.80 8,947.44 9,040.76 7,895.80 3,168.74 9,945.76 3,112.20 16,294.32 55,000 113,405