In cell O17: Use the VLOOKUP function to return the Deduction Rate 4 from the appropriate table at the top of the worksheet. Multiply this Deduction Rate 4 by the Gross Monthly Salary to calculate the Monthly Deduction. Copy the function down to the other cells in the column.

In cell O14 use the appropriate function to answer: How many employees have a Monthly Deduction greater than 900?

In cell P17: Calculate the Net Monthly Salary by subtracting the Monthly Deduction from the Gross Monthly Salary. Copy the formula to all cells in the column.

In cell P14: use the appropriate function to answer: What is the total for all employees?

In cell Q17: Use the VLOOKUP function to return Bonus Rate 1 from the appropriate table at the top of the worksheet. Multiply this Bonus Rate 1 by the Gross Monthly Salary to calculate the Rating Bonus. Copy the function down to the other cells in the column.

In cell Q14 use the appropriate function to answer: How many employees have a Rating Bonus greater than 100? Format answer as comma style, 0 decimal.

In cell R17: Use the VLOOKUP function to return Education Bonus 3 from the appropriate table at the top of the worksheet. Copy the function down to the other cells in the column.

In cell R14 use the appropriate function to answer: How many employees have an Education Bonus greater than 600?

In cell S17: Use the VLOOKUP function to return Department Bonus 2 from the appropriate table at the top of the worksheet. Copy the function down to the other cells in the column.

In cell S14 use the appropriate function to answer: How many employees have a Department Bonus greater than 900?

In cell T17 use the IF function to enter 1000 if BILINGUAL is YES otherwise enter 250. Copy the function to the other cells in the column.

In cell T14 use the appropriate function to calculate the total for all employees.

In U17 use NESTED IF functions to enter:

• the valuein AF2, if the PROVINCE is ON,
• the valuein AF3,if the PROVINCE is BC,
• the valuein AF4,if the PROVINCE is AB,
• the valuein AF5,if the PROVINCE is MB,
• otherwise the value in AF6.

Copy the nested functions to the rest of the cells in the column.

In U14, use the appropriate function to count how many values are greater than 700.Format answer as comma style,0decimal.

In V14 use the AVERAGEIFS function to calculate the average Gross Monthly Salary for Female employees with a Rating of 7 or more and an Education of 4 who are bilingual.Format answer as comma style, 0 decimal.

In W14 use the SUMIFS function to calculate the total Gross Monthly Salary for Male employees from QC who work in Department B and who are bilingual.Format answer as comma style

In cell X14 use the COUNTIFS function to determine how many employees are Bilingual, have a Rating of more than 2 and an Education of less than 2 who are from Department C.

In cell Y14 use the MATCH Function to answer:What is the Row #(in the range of Gross Monthly Salary data) that 2916 can be found?

In cell Z14 use the INDEX Function only to answer: What is the City of the employee with a Gross Monthly Salary of 2916?

In cell AA14 use the SUBTOTAL Function only to calculatethe maximum Gross Monthly Salary for all employees (Format as comma style,0decimal)

Filter the data to show employees with Education of 3 or more from Toronto who are bilingual.

The Subtotal value in AA14 should update to show the result for the filtered data.

Enter (type) this new Subtotal value into cell AB14 (the value should remain when the filters are cleared).

What is the new Subtotal value? (Format as comma style,0 decimal)

Clear the filters.