Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
Open the start file EX2019-ChallengeYourself-6-3. The file will be renamed automatically to include your name.  If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the workbook so you can modify the workbook.  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. On the Sales Data worksheet, calculate the commission rate and commission paid for each sales associate.  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 EX 6.1 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% <100,000 0%  Fill the formula down through cell F64. If necessary, format the range as a percentage, and center the values in the cells.  In cell G4, calculate the commission earned by multiplying the annual sales by the commission rate (E4*F4).  Add a rounding function to the formula in cell G4 to round the result to two decimal places.  Fill the formula down through cell G64. If necessary, format the range using the default Comma Style number format. In the Summary sheet, calculate average and median sales and commissions. Use the named ranges shown in cells A23:B26.  In cell B5, enter a formula to calculate the average annual sales for all sales associates. Use the named range AnnualSales.  In cell C5, enter a formula to calculate the median annual sales for all sales associates. Use the named range AnnualSales.  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.  Add a rounding function to the formula in cell D5 to round the result to two decimal places.  In cell B6, enter a formula to calculate the average commission. Use the named range CommissionEarned.  In cell C6, enter a formula to calculate the median commission. Use the named range CommissionEarned.  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.  Add a rounding function to the formula in cell D6 to round the result to two decimal places.  If necessary, format cells B5:D6 with the default Accounting Number Format. Calculate the total and average sales by region.  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.  Copy the formula to cells C10:I10.  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.  Add a rounding function to the formula in cell B11 to round the result to two decimal places.  Copy the formula to cells C11:I11.  If necessary, format cells B10:I11 with the default Accounting Number Format. The second part of the Summary sheet contains a sales associate lookup form, but the appropriate formulas have not been entered yet.  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.)  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.  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.  Test your formulas by entering Lopez as the sales associate's last name (cell A16) and >200000 as the sales target (cell B16).  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.  Save and close the workbook.

Open the start file EX2019-ChallengeYourself-6-3. The file will be renamed automatically to include your name.  If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the workbook so you can modify the workbook.  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. On the Sales Data worksheet, calculate the commission rate and commission paid for each sales associate.  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 EX 6.1 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% <100,000 0%  Fill the formula down through cell F64. If necessary, format the range as a percentage, and center the values in the cells.  In cell G4, calculate the commission earned by multiplying the annual sales by the commission rate (E4*F4).  Add a rounding function to the formula in cell G4 to round the result to two decimal places.  Fill the formula down through cell G64. If necessary, format the range using the default Comma Style number format. In the Summary sheet, calculate average and median sales and commissions. Use the named ranges shown in cells A23:B26.  In cell B5, enter a formula to calculate the average annual sales for all sales associates. Use the named range AnnualSales.  In cell C5, enter a formula to calculate the median annual sales for all sales associates. Use the named range AnnualSales.  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.  Add a rounding function to the formula in cell D5 to round the result to two decimal places.  In cell B6, enter a formula to calculate the average commission. Use the named range CommissionEarned.  In cell C6, enter a formula to calculate the median commission. Use the named range CommissionEarned.  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.  Add a rounding function to the formula in cell D6 to round the result to two decimal places.  If necessary, format cells B5:D6 with the default Accounting Number Format. Calculate the total and average sales by region.  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.  Copy the formula to cells C10:I10.  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.  Add a rounding function to the formula in cell B11 to round the result to two decimal places.  Copy the formula to cells C11:I11.  If necessary, format cells B10:I11 with the default Accounting Number Format. The second part of the Summary sheet contains a sales associate lookup form, but the appropriate formulas have not been entered yet.  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.)  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.  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.  Test your formulas by entering Lopez as the sales associate's last name (cell A16) and >200000 as the sales target (cell B16).  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.  Save and close the workbook.

Database System Concepts
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
See similar textbooks
icon
Related questions
Question
  1.  Open the start file EX2019-ChallengeYourself-6-3. The file will be renamed automatically to include your name.
  2.  If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the workbook so you can modify the workbook.
  3.  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.
  4. 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 EX 6.1 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%
      <100,000 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.
  5. 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.
  6. 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.
  7. 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.
  8.  Save and close the workbook.
Home Insert Draw Page Layout Formulas Data Review View * Share O Comments fx Σ. e, Trace Precedents H Trace Dependents O E Calculate Now Define Name v AutoSum Recently Financial Logical Used Error E Remove Arrows v Formulas Checking Calculation Calculate Sheet Options Insert Text Date & Lookup & Math & Time More Fo Create fram Selection Show Function Reference Trig Functions O office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for Updates F4 fxr H K MI P 1 National Pharmaceutical Sales 2 Sales Associate's Sales Associate's Sales Associate's Commission Commission Last Name First Name ID Client Annual Sales Rate Earned Salary Total Earnings Region 4 Ahjab 5 Mjab 6 Ahjab 7 Ahjab A Ahjab 9 Anjab 10 Anderson 11 Anderson 12 Anderson Raminian NE1-73647 Garden State Pharmacies 78,133 55,000 55,000 NE1 MA OBGyn Assaciates MD Dental Assaciates New England Pharmacies, Inr New Fruland Physiciars Inc. PAModical Practices, Inc. Low County Medical Asseciates, Ins. Raminian NE1-217199 230,427 NF1 Raminian NE1 247543 262,620 NE1 Raminian NE1-235322 249,654 NE1 Raminian NE1-213262 226,250 NE1 Raminian NEI 219129 232,475 NEI SE2-B0G00 SE2-231417 Cynthie 85,573 51,000 51,000 SE2 Cynthia NC Medical Associses 245,511 SE2 Cynthia SEZ-189894 Crthopaedic Modical Associates 201,459 SE2 Cynthig Cynthia 13 Anderson SE2-87721 Palmetto Physicans 93,064 SE2 14 Anderson 15 Anderson SE2-153656 Palmetto Padiatrists, Inc. Piodmant Hoalth Asociatos, Inc. 163.025 SE2 Cynthia SE2-117771 134,945 SEZ SC Medical Associats Upstate Pharmacies, Inc AZ Ambulatory Care CA Chiropractic Accociates, inc Emergency Meditine Association 16 Anderson 17 Anderson Cynthia SE2-81900 86,895 SE2 Cynthia SE2-115585 132.625 SE2 18 Anderson 19 Anderson 20 Anderson Gearge SWI-137004 145,349 60,000 60,000 SW1 George SWi-14966 158,778 SWi Gearpe swi-82895 87,944 SW1 21 Anderson Gearge SW1-18801O Joint Healthcare Associates 194,157 SWi 22 Anderson 23 Anderson 24 Anderson 25 Anderson NM Pharmacies, Inc. Southwest Dermatology George SWi-150820 160,006 Swi George sw1-126767 134,489 SW1 Geare SW1-248635 Sauthwest Medical Assaciates 263,779 SWi George SWi-186345 Tri-state Pharmaties 197,695 SWi 26 Brown Tstisha SE1-230271 Bema Chirupratrtic Clinics, Inc. 244,296 50,000 50,000 SE1 SE 27 Brown 28 Brown Tacisha SE1-119903 Gerantology Associates of Mi Internal Macicineof MS LACardiolugists, Inc. 127,207 Tatisha SE1-253055 268,467 SE 29 Brown 30 Brown Tatisha SE1-132658 140,738 81.BAR SEI Tatisha SE1-77149 Lauisiana Dermatolagists, Inc. 81,848 SE! 31 Brown Tatisha SE-157203 MS Women's Clinics, Inc 166.841 SE! 32 Brown Tatisha SE1-230063 Physicians of MS, in 244,074 SEI Summary Sales Data 曲 国 四 100%
Transcribed Image Text:Home Insert Draw Page Layout Formulas Data Review View * Share O Comments fx Σ. e, Trace Precedents H Trace Dependents O E Calculate Now Define Name v AutoSum Recently Financial Logical Used Error E Remove Arrows v Formulas Checking Calculation Calculate Sheet Options Insert Text Date & Lookup & Math & Time More Fo Create fram Selection Show Function Reference Trig Functions O office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for Updates F4 fxr H K MI P 1 National Pharmaceutical Sales 2 Sales Associate's Sales Associate's Sales Associate's Commission Commission Last Name First Name ID Client Annual Sales Rate Earned Salary Total Earnings Region 4 Ahjab 5 Mjab 6 Ahjab 7 Ahjab A Ahjab 9 Anjab 10 Anderson 11 Anderson 12 Anderson Raminian NE1-73647 Garden State Pharmacies 78,133 55,000 55,000 NE1 MA OBGyn Assaciates MD Dental Assaciates New England Pharmacies, Inr New Fruland Physiciars Inc. PAModical Practices, Inc. Low County Medical Asseciates, Ins. Raminian NE1-217199 230,427 NF1 Raminian NE1 247543 262,620 NE1 Raminian NE1-235322 249,654 NE1 Raminian NE1-213262 226,250 NE1 Raminian NEI 219129 232,475 NEI SE2-B0G00 SE2-231417 Cynthie 85,573 51,000 51,000 SE2 Cynthia NC Medical Associses 245,511 SE2 Cynthia SEZ-189894 Crthopaedic Modical Associates 201,459 SE2 Cynthig Cynthia 13 Anderson SE2-87721 Palmetto Physicans 93,064 SE2 14 Anderson 15 Anderson SE2-153656 Palmetto Padiatrists, Inc. Piodmant Hoalth Asociatos, Inc. 163.025 SE2 Cynthia SE2-117771 134,945 SEZ SC Medical Associats Upstate Pharmacies, Inc AZ Ambulatory Care CA Chiropractic Accociates, inc Emergency Meditine Association 16 Anderson 17 Anderson Cynthia SE2-81900 86,895 SE2 Cynthia SE2-115585 132.625 SE2 18 Anderson 19 Anderson 20 Anderson Gearge SWI-137004 145,349 60,000 60,000 SW1 George SWi-14966 158,778 SWi Gearpe swi-82895 87,944 SW1 21 Anderson Gearge SW1-18801O Joint Healthcare Associates 194,157 SWi 22 Anderson 23 Anderson 24 Anderson 25 Anderson NM Pharmacies, Inc. Southwest Dermatology George SWi-150820 160,006 Swi George sw1-126767 134,489 SW1 Geare SW1-248635 Sauthwest Medical Assaciates 263,779 SWi George SWi-186345 Tri-state Pharmaties 197,695 SWi 26 Brown Tstisha SE1-230271 Bema Chirupratrtic Clinics, Inc. 244,296 50,000 50,000 SE1 SE 27 Brown 28 Brown Tacisha SE1-119903 Gerantology Associates of Mi Internal Macicineof MS LACardiolugists, Inc. 127,207 Tatisha SE1-253055 268,467 SE 29 Brown 30 Brown Tatisha SE1-132658 140,738 81.BAR SEI Tatisha SE1-77149 Lauisiana Dermatolagists, Inc. 81,848 SE! 31 Brown Tatisha SE-157203 MS Women's Clinics, Inc 166.841 SE! 32 Brown Tatisha SE1-230063 Physicians of MS, in 244,074 SEI Summary Sales Data 曲 国 四 100%
Home Insert Draw Page Layout Formulas Data Review View * Share O Comments fx A (4) O Define Name v B. Trace Precadents E Calculate Now La Trace Dependents More Functions Calculation Calculate Shoet Options Insert AutoSum Recantly Financial Logical Used Text Date & Lookup & Math & Reference EE Create fram Selection Show E Remove Arrowe Formulas Checking Error Function Time Trig O Office Update To keep up-to-date with security updates, fixes, arıd irmprovements, choose Check for Upcdates. Check for Updates A1 fx National Pharmaceutical Sales G K L M N P R 1 National Pharmaceutical Sales Sales Summary Standard 4 Average Median Deviation 5 Annual Sales 6 Commission Earnec Sales Summary by Region NE1 SE? swi SE1 SW2 NW1 NE2 NW2 10 Total 11 Auerage 12 13 Enter the safes associate's iast nume and annual soles torget in the celis below 14 no chaices are entered, the table below dispayscompany-wide dota 15 Sales Associate's Last Name Annual Sales 16 17 18 Average Commission Farned 19 Total Commiszion Earned Number of Clients with Annusl Sales 20 Reaching Target 21 22 23 AnnualSeles 24 CommisionEarned 25 Earnings 26 Regions -Seles Duta'ISE54:SES64 - Sales Data'I$G$4:$G$64 =Szles Data'IŞA$3 SIS64 =Seles Data'ISC$4$C$64 27 28 29 30 31 32 33 Summary Sales Data +
Transcribed Image Text:Home Insert Draw Page Layout Formulas Data Review View * Share O Comments fx A (4) O Define Name v B. Trace Precadents E Calculate Now La Trace Dependents More Functions Calculation Calculate Shoet Options Insert AutoSum Recantly Financial Logical Used Text Date & Lookup & Math & Reference EE Create fram Selection Show E Remove Arrowe Formulas Checking Error Function Time Trig O Office Update To keep up-to-date with security updates, fixes, arıd irmprovements, choose Check for Upcdates. Check for Updates A1 fx National Pharmaceutical Sales G K L M N P R 1 National Pharmaceutical Sales Sales Summary Standard 4 Average Median Deviation 5 Annual Sales 6 Commission Earnec Sales Summary by Region NE1 SE? swi SE1 SW2 NW1 NE2 NW2 10 Total 11 Auerage 12 13 Enter the safes associate's iast nume and annual soles torget in the celis below 14 no chaices are entered, the table below dispayscompany-wide dota 15 Sales Associate's Last Name Annual Sales 16 17 18 Average Commission Farned 19 Total Commiszion Earned Number of Clients with Annusl Sales 20 Reaching Target 21 22 23 AnnualSeles 24 CommisionEarned 25 Earnings 26 Regions -Seles Duta'ISE54:SES64 - Sales Data'I$G$4:$G$64 =Szles Data'IŞA$3 SIS64 =Seles Data'ISC$4$C$64 27 28 29 30 31 32 33 Summary Sales Data +
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 7 steps with 9 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Follow-up Questions
Read through expert solutions to related follow-up questions below.
Follow-up Question

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.

Solution
Bartleby Expert
SEE SOLUTION
Knowledge Booster
Data Binding
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS