## Question

# 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

##
- 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.

**EX2019-ChallengeYourself-6-3**. The file will be renamed automatically to include your name.**Enable Editing**button in the Message Bar at the top of the workbook so you can modify the workbook.*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**.*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.

*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**.

- 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**.

*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.

Insert Draw Page Layout Insert AutoSum Recently Financial Logical Text Date & Function Used Time More Functions. Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. X fx Home F4 2 3 Sales Associate's Last Name 4 Ahjab 5 Ajab 6 Ahjab 7 Ahjab www 8 Ajab 9 Ahjab A 10 Anderson Hiddison 11 Anderson 12 Anderson 13 Anderson 14 Anderson 15 Anderson 16 Anderson 17 Anderson 18 Anderson 19 Anderson 20 Anderson 21 Anderson 22 Anderson 23 Anderson 24 Anderson 25 Anderson 26 Brown 27 Brown 28 Brown 29 Brown 30 Brown 31 Brown 32 Brown Sales Associate's Sales Associate's First Name ID Raminian Raminian Raminian Raminian Raminian Raminian Cynthia emane Cynthia Cynthia Cynthia Cynthia Cynthia Cynthia Cynthia Gearge George George Gearge George George Gearge George Teisha Taiha Tatisha Tatish Tarisha Tatisha Tatisha Summary C NE1-73647 NE1-217199 NE1-247543 NE1-235322 NE1-31 NE1-219129 SE2-90660 SE2-231417 SE2-189894 SE2-97721 3152550 SE2-153666 SE2-117771 SE2-81906 wwwwww SE2-115585 SW1-137004 SW1-149663 GUE GAR SW1-188010 SW1-150820 awa2306.20 SW1-125767 SW1-248636 SW1-186345 SE1-230271 SE1-119903 SE1-253055 SE2-132658 SE1-77149 SE1 157263 SE2-230063 Sales Data Formulas AI O Y Data Review Q . 0 Lookup & Math & Reference Trig Client 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 scans Palmetto Podiatrists, Inc. Piedmont Health Associates, Inc. SC Medical Associates Upstate Pharmacies, Inc. V D National Pharmaceutical Sales AZ Ambulatory Care CA Chiropractic Associates, Inc. Emergency Medicine Association. Joint Healthcare Associates NM Pharmacies, Inc. Southwest Dermatology Southwest Medical Associates Tri-state Pharmacies Bema Chiropractic Clinics, Inc. Gerontology Associates of MS Internal Medicine of MS LACardiologists, Inc. Louisiana Dermatologists, Inc. MS Women's Clinics, Inc. Physicians of MS, int View **** V Annual Sales 78,133 230,427 262,520 249,654 226,250 232,475 85,573 245,511 201,459 93,064 163,025 134,945 86,993 132,525 145,349 158,778 67.044 87,944 194,157 160,006 134,489 263,779 197,695 244,296 127,207 268,467 140,738 81,848 Define Name Create from Selection 166,841 244,074 Commission Rate G Commission Earned P, Trace Precedents H Trace Dependents Show Remove Arrows Formulas Checking Error Salary 55,000 51,000 60,000 50,000 I Total Earnings 55,000 NE1 THEA NF1 NE1 NE1 NE1 NE1 51,000 SE2 Co SE2 SE2 SE2 SEL SE2 SE2 SE2 SE2 60,000 SWI Region SW1 SW1 SWI SW1 SW1 SWI SW1 50.000 SE SE SE1 SE SE: SE SE K Calculate Now Calculation Calculate Sheet Options L M N B 0 Share Comments Check for Updates a R 100%

## Step by Step Solution

3.82 Ratings (38 Votes)

There are 3 Steps involved in it

### Step: 1

### Get Instant Access with AI-Powered Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

### Step: 2

### Step: 3

## Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started