Excel file link

https://docs.google.com/spreadsheets/d/1zUuJBGgAhXcpHMb9_lmq1VPD-XCx6UJK/edit?usp=drive_link&ouid=106020471773606203483&rtpof=true&sd=true

 

1,In cell N14, enter a function to calculate how many employees have a Gross Monthly Salary greater than 4,500. Blank 1 Format as comma style, 0 decimal

2.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: What is the total for all employees?Blank 1 Format answer as comma style, 0 decimal.

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 7250?Blank 2

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

Blank 1

4.In cell R17: Use the VLOOKUP function to return Education 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 R14 use the appropriate function to answer: How many employees have an Education Bonusgreater than 700? Format answer as comma style, 0 decimal.

Blank 1

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

Blank 1

6.In cell T17 use the IF function to enter 700 if EDUCATION is 5 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. Format as comma style 0 decimal

Blank 1

7.In U17 use NESTED IF functions to enter:

• the value 500, if the CITY is Mississauga,
• the value 350, if the CITY is Winnipeg,
• the value 485, if the CITY is Richmond Hill,
• the value 450, if the CITY is Ottawa,
• otherwise the value 315.

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,0decimal.

8.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.

9.In W14 use the SUMIFS function to calculate the total Gross Monthly Salary forFemale employees from Mississauga with a Rating of less than 7 who are not bilingual.Format answer as comma style,0decimal.

10.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.

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

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

12.In cell AA14 use the SUBTOTAL Function only to calculatethe maximum Gross Monthly Salary for all employeesBlank 1 (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?Blank 2 (Format as comma style,0 decimal)

https://docs.google.com/spreadsheets/d/1zUuJBGgAhXcpHMb9_lmq1VPD-XCx6UJK/edit?usp=drive_link&ouid=106020471773606203483&rtpof=true&sd=true

Excel file link

1,In cell N14, enter a function to calculate how many employees have a Gross Monthly Salary greater than 4,500. Blank 1 Format as comma style, 0 decimal

2.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: What is the total for all employees?Blank 1 Format answer as comma style, 0 decimal.

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 7250?Blank 2

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

Blank 1

4.In cell R17: Use the VLOOKUP function to return Education 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 R14 use the appropriate function to answer: How many employees have an Education Bonusgreater than 700? Format answer as comma style, 0 decimal.

Blank 1

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

Blank 1

6.In cell T17 use the IF function to enter 700 if EDUCATION is 5 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. Format as comma style 0 decimal

Blank 1

7.In U17 use NESTED IF functions to enter:

• the value 500, if the CITY is Mississauga,
• the value 350, if the CITY is Winnipeg,
• the value 485, if the CITY is Richmond Hill,
• the value 450, if the CITY is Ottawa,
• otherwise the value 315.

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,0decimal.

8.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.

9.In W14 use the SUMIFS function to calculate the total Gross Monthly Salary forFemale employees from Mississauga with a Rating of less than 7 who are not bilingual.Format answer as comma style,0decimal.

10.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.

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

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

12.In cell AA14 use the SUBTOTAL Function only to calculatethe maximum Gross Monthly Salary for all employeesBlank 1 (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?Blank 2 (Format as comma style,0 decimal)