1.In cell N14, enter a function to calculate how many employees have a Gross Monthly Salary less than 5,000. Format as comma style, 0 decimal

Answer :

2. In cell O17: Use the VLOOKUP function to return the Deduction Rate 2 from the appropriate table at the top of the worksheet. Multiply this Deduction Rate 2 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: What is the total for all employees?Blank 1Format answer as comma style, 0 decimal.

Answer:

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: How many employees have a Net Monthly Salary greater than 8000?

Answer:

3. In cell Q17: Use the VLOOKUP function to return Bonus Rate 4 from the appropriate table at the top of the worksheet. Multiply this Bonus Rate 4 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 less than 220? Format answer as comma style, 0 decimal.

Answer:

5. 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 800? Format answer as comma style, 0 decimal.

Answer:

6. In cell S17: Use the VLOOKUP function to return Department Bonus 1 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 less than 700? Format answer as comma style, 0 decimal.

Answer:

7. In cell T17 use the IF function to enter 0 if EDUCATION is 1 otherwise enter 125. Copy the function to the other cells in the column.

In cell T14 use the appropriate function to calculate the total for all employees. Format as comma style 0 decimal

Answer:

8.U17 use NESTED IF functions to enter:

• the value 300, if the CITY is North York,
• the value 250, if the CITY is Scarborough,
• the value 325, if the CITY is Markham,
• the value 400, if the CITY is Thornhill,
• otherwise the value 275.

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

In U14, use the appropriate function to calculate the total for all employees. Format answer as comma style, 0 decimal.

Answer:

9. In V14 use the AVERAGEIFS function to calculate the average Gross Monthly Salary for Female employees from ON who work in Department A and who are not bilingual.Format answer as comma style, 0 decimal.

Answer:

10. W14 use the SUMIFS function to calculate the total Gross Monthly Salary for Male employees from Scarborough with a Rating of 5 or less and an Education of 2 or more. Format answer as comma style, 0 decimal.

Answer:

11. In cell X14 use the COUNTIFS function to determine how many employees are from Markham, are not Bilingual, have a Rating of less than 5 and an Education of 2 or more.

Answer:

12. 3 Points

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

answer :

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

answer :

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

Answer:

Filter the data to show employees with Education of 2 or 3 from QC who are not bilingual.

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

Enter (type) this new Subtotal value into AB14 (the value should remain when the filters are cleared) and below. (Format as comma style, 0 decimal)

Clear the filters.

Answer: