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:316.) 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.) c. If necessary, apply the Accounting Number Format to cells B18:319. 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. e. If necessary, apply the General number format to cell B20. f. 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. h. If you receive errors, use Evaluate Formula to troubleshoot your formulas. Function Library Defined Names Formula Auditing Calculation A1 National Pharmaceutical Sales F G H 1 J K SW2 NW1 NE2 NW2 A B C D E 1 1 National Pharmaceutical Sales 2 3 Sales Summary Standard 4 Average Median Deviation 5 Annual Sales 6 Commission Earned 7 8 Sales Summary by Region 9 NE1 SE2 SW1 SE1 10 Total 11 Average 12 13 Enter the sales associate's last name and annual sales target in the cells below 14 If no choices are entered, the table below displays company-wide data 15 Sales Associate's Last Name Annual Sales 16 17 18 Average Commission Earned 19 Total Commission Earned Number of clients with Annual 20 Sales Reaching Target 21 22 23 AnnualSales ='Sales Data'!$E$4:$E$64 24 CommissionEarned ='Sales Data'!$G$4:$G$64 25 Earnings ="Sales Data'!$A$3:$1$64 26 Regions ='Sales Data'!$C$4:$C$64 27 28 29 30 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:316.) 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.) c. If necessary, apply the Accounting Number Format to cells B18:319. 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. e. If necessary, apply the General number format to cell B20. f. 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. h. If you receive errors, use Evaluate Formula to troubleshoot your formulas. Function Library Defined Names Formula Auditing Calculation A1 National Pharmaceutical Sales F G H 1 J K SW2 NW1 NE2 NW2 A B C D E 1 1 National Pharmaceutical Sales 2 3 Sales Summary Standard 4 Average Median Deviation 5 Annual Sales 6 Commission Earned 7 8 Sales Summary by Region 9 NE1 SE2 SW1 SE1 10 Total 11 Average 12 13 Enter the sales associate's last name and annual sales target in the cells below 14 If no choices are entered, the table below displays company-wide data 15 Sales Associate's Last Name Annual Sales 16 17 18 Average Commission Earned 19 Total Commission Earned Number of clients with Annual 20 Sales Reaching Target 21 22 23 AnnualSales ='Sales Data'!$E$4:$E$64 24 CommissionEarned ='Sales Data'!$G$4:$G$64 25 Earnings ="Sales Data'!$A$3:$1$64 26 Regions ='Sales Data'!$C$4:$C$64 27 28 29 30
