Lab 3 Project - Excel Ch 6

1. Open the start file
2. 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.
3. On the Sales Data worksheet, calculate the commission rate and commission paid for each sales associate.
   1. Beginning in cell F4, enter a formula using the IFS function to calculate the commission rate based on the following table. Hint: Do not include comma separators in numerical values in formulas. Excel interprets commas as function argument separators.

      Table displays calculations for commission rates based on annual sales.

      IF ANNUAL SALES ARE:	THEN THE COMMISSION RATE IS:
      >=225,000	6%
      >=175,000	4%
      >=100,000	2%
      	0%

   2. Fill the formula down through cell F64. If necessary, format the range as a percentage, and center the values in the cells.
   3. In cell G4, calculate the commission earned by multiplying the annual sales by the commission rate (E4*F4).
   4. Add a rounding function to the formula in cell G4 to round the result to two decimal places.
   5. Fill the formula down through cell G64. If necessary, format the range using the default Comma Style number format.
4. In the Summary sheet, calculate average and median sales and commissions. Use the named ranges shown in cells A23:B26.
   1. In cell B5, enter a formula to calculate the average annual sales for all sales associates. Use the named range AnnualSales.
   2. In cell C5, enter a formula to calculate the median annual sales for all sales associates. Use the named range AnnualSales.
   3. In cell D5, enter a formula to calculate the standard deviation for sales. This dataset is a sample of the larger population. Use the named range AnnualSales.
   4. Add a rounding function to the formula in cell D5 to round the result to two decimal places.
   5. In cell B6, enter a formula to calculate the average commission. Use the named range CommissionEarned.
   6. In cell C6, enter a formula to calculate the median commission. Use the named range CommissionEarned.
   7. In cell D6, enter a formula to calculate the standard deviation for commissions. This dataset is a sample of the larger population. Use the named range CommissionEarned.
   8. Add a rounding function to the formula in cell D6 to round the result to two decimal places.
   9. If necessary, format cells B5:D6 with the default Accounting Number Format.
5. Calculate the total and average sales by region.
   1. In cell B10, use a SUMIF formula to calculate the sum of AnnualSales where the value in the Regions named range is equal to the region listed in cell B9.
   2. Copy the formula to cells C10:I10.
   3. In cell B11, use an AVERAGEIF formula to calculate the average of AnnualSales where the value in the Regions named range is equal to the region listed in cell B9.
   4. Add a rounding function to the formula in cell B11 to round the result to two decimal places.
   5. Copy the formula to cells C11:I11.
   6. If necessary, format cells B10:I11 with the default Accounting Number Format.
6. The second part of the Summary sheet contains a sales associate lookup form, but the appropriate formulas have not been entered yet.
   1. 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.)
   2. 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.)
   3. If necessary, apply the Accounting Number Format to cells B18:B19.
   4. 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.
   5. If necessary, apply the General number format to cell B20.
   6. Test your formulas by entering Lopez as the sales associate's last name (cell A16) and >200000 as the sales target (cell B16).
   7. Add an IFERROR function to the formula in cell B18 to display the error text Associate not found.
   8. If you receive errors, use Evaluate Formula to troubleshoot your formulas.

Share Comments P Trace Precedents fx Y Y fi Calculate Now ' Trace Dependents Show Error Remove Arrows Formulas Checking Calculation Calculate Sheet Options Check for Updates H J K L M N o P R Salary Total Earnings Region 55,000 . Home Insert Draw Page Layout Formulas Data Review View A 0 A Datine Nama Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More F Create from Selection Function Used Time Reference Trig Functions Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. F4 B G 1 National Pharmaceutical Sales 2 Sales Associate's Sales Associate's Sales Associate's Commission Commission 3 Last Name First Name ID Client Annual Sales Rate Earned 4 Ahjab Faminian NE1-73042 Garden State Pharmacies 78,133 0122 5 Anjab Raminian NE1-217199 MA Oyn Associates 230,427 E Anlab Raminian NE1 247543 MD Dental Associates 262.620 7 Ahjab Faminian NE1-235322 New England Pharmacies, in 249,654 New England Physicians Inc 226,250 9 Ahlab Raminian NEL 210129 PA Medical Practices, Inc. 232,475 10 Anderson Cynthia SE2-90660 Low County Medical Associates, ins. 85,573 11 Anderson Cynthia SE2-231417 NC Medical Associates 245,511 12 Anderson Cynthia SE2-189894 Orthopaedic Medical Associates 201,459 13 Anderson Cynthia radion SE2-97721 Palmetto Physcans 93,064 14 Anderson Cynthia SE2-153666 Palmeta Podiatrists, Inc. 163,025 15 Anderson Cynthia SE2-117771 Piedmont Health Associates, Inc 134,945 16 Anderson Cynthia SE2-91905 SC Medical Associats 86,893 een 17 Anderson Cynthia SE2-115585 Upstate Pharmacies Inc 132.625 18 Anderson George SW1-137004 AZ Ambulacory Care 145,349 19 Anderson George SW1-14966) CA Chiropractic Aszociates, Inc. 158,778 20 Anderson Genre SW1-82895 Emergency Medicine Association 87,944 21 Anderson . George SW1-183010 Joint Healthcare Associates 194,157 22 Anderson George SW1-150920 NM Pharmacies, Inc. 160,006 ud anos 23 Anderson George SW1-126767 Southwest Dermatology 134,489 24 Anderson George SW1-248635 Sauthwest Medical Assaciates 263,779 25 Anderson George SW1-286345 Tristate Pharmacies 197,695 26 BOAT Totisha SE1-230271 Burma Chirupractic Clinics, Inc. 244,296 27 RON Tarisha SE-119903 Gerantology Associates of MS 127,207 29 Broar Tatisha SES 252055 Internal Medicine of MS 268,467 29 Boer Totisha SE1-132658 LACardiologists, Inc. 140,738 30 BIO Tarisha SE1-77149 Louisiana Dermatologists, Inc. 81,648 31 BOT Tatisha SE 157262 Ms Women's Clinies, Inc. 166,841 32 BOAT Trisha SE1-230063 Physicians of MS, Inc. 244,074 Summary Sales Data + 51,000 55,000 NL1 NF1 NE1 NE1 NE NF1 NE1 51,000 SE2 SE2 SE2 SE2 SE2 SE2 SE2 SE2 60,000 swi SW1 SW1 SWI SW1 SW1 SWI SW1 50,000 SE: SE: SE SE SE! 60,000 50,000 SES SE 1 100% Home Insert Draw Page Layout Formulas Data Review View Share m. Comments D Comments A fx C E Trace Precedents Fa Trace Dependents SA Show Error Remove Arrow Formulas Checking calowate Now Calculate Now Text Insert Function Auto5um Recently Financial Logical Used Date & Lookup & Math & Time Reference Trig More Functions Croata tram Salection Calculation calculate Sheet Options Check for Updates Office Update To keep up-to-date with security upciates, fixes, and improvements, choose Check for Updates. fr National Pharmaceutical Sales A1 D E F G H L M N OP a R National Pharmaceutical Sales 1 2 3 Sales Summary Standard Devision Average Median 4 s Annual Sales 6 Commission Erne 7 Sales Summary by Repon 551 SW2 NW1 NE2 NW2 9 NE1 SE2 SW1 10 Total 11 Aerage 12 13 Ender the sales associades ist nume and annual sales target in the cells below 14 no choices are entered the table below displays company widedora 15 Sales Associate's Last Name Annud Sales 16 17 18 Average Commission Farned 19 Total Commision Earned Number of clients with Annual Sales 20 Reaching Target 21 22 23 AnnualSales - Sales Data's E$4:E$64 24 ComitianEarned -Sales Data'l$$$4$$ 54 25 Earnings - Sales Data'!$A$3 $I$64 26 Regions -Sales Data's $4 SC564 27 22 29 30 32 33 Summary Sales Data +