5. In the Summary sheet, calculate average and median sales and commissions. Use the named ranges shown in cells A23:326. a. In cell B5, enter a formula to calculate the average annual sales for all sales associates. Use the named range AnnualSales. b.In cell C5, enter a formula to calculate the median annual sales for all sales associates. Use the named range AnnualSales. c. 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. d. Add a rounding function to the formula in cell D5 to round the result to two decimal places. e. In cell B6, enter a formula to calculate the average commission. Use the named range CommissionEarned. f. In cell C6, enter a formula to calculate the median commission. Use the named range CommissionEarned. g. 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 Commission Earned. h. Add a rounding function to the formula in cell D6 to round the result to two decimal places. i. O if necessary, format cells B5:06 with the default Accounting Number Format. 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 5. In the Summary sheet, calculate average and median sales and commissions. Use the named ranges shown in cells A23:326. a. In cell B5, enter a formula to calculate the average annual sales for all sales associates. Use the named range AnnualSales. b.In cell C5, enter a formula to calculate the median annual sales for all sales associates. Use the named range AnnualSales. c. 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. d. Add a rounding function to the formula in cell D5 to round the result to two decimal places. e. In cell B6, enter a formula to calculate the average commission. Use the named range CommissionEarned. f. In cell C6, enter a formula to calculate the median commission. Use the named range CommissionEarned. g. 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 Commission Earned. h. Add a rounding function to the formula in cell D6 to round the result to two decimal places. i. O if necessary, format cells B5:06 with the default Accounting Number Format. 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
