2. On the Sales worksheet, enter a date function in cell C8 to calculate the number of years the first representative has worked for your company. Copy the function to the range C9:C20. 7 3 7 On the Sales worksheet, enter a nested function in cell J8 of the Bonus column to display the bonus amount. If the employee sold $200,000 or more (cell J2) AND is International (cell 14), he or she ears a 5% (cell J4) bonus on his or her total annual sales; otherwise, the representative eams 3% (cell J3) of his or her total annual sales. Use relative and mixed (or absolute) references correctly in the nested function. Copy the function from J8 to the range J9:J20 4 7 Enter a nested lookup function in cell E4 that uses the cells E2 and E3 to return a specific sales record. For example, using the current data, you want the function to return the third quarter sales for Erica. 5 Click the Database worksheet tab and enter conditions in the Criteria Range for Domestic sales reps that made 240000 or more in sales. 4 6 Perform an advanced filter based on the criteria range. Set the filter to copy the new data to the range A22:G22 8 7 In cell J7, enter a database function to calculate the number of reps meeting the criteria. 7 8 In cell J8, enter a database function to calculate the highest total sales for records meeting the criteria 7 9 7 10 4 In cell J9, enter a database function to calculate the average sales for records meeting the criteria Click the Addition worksheet tab, and then insert a formula in cell E2 to calculate the loan amount based on the loan parameters. In cell E5, enter a function to calculate the monthly payment. Modify the function to ensure that the result is a positive number. In cell E6, enter a function to calculate the total interest paid after five payments. Modify the function to ensure that the result is a positive number 11 5 5 12 7