In U$17$ use NESTED IF functions to enter:

the value in AE$2,$ if the PROVINCE is ON$,$

the value in AE$3,$ if the PROVINCE is BC$,$

the value in AE$4,$ if the PROVINCE is AB$,$

the value in AE$5,$ if the PROVINCE is MB$,$

otherwise the value in AE$6.$

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

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

In V$14$ use the AVERAGEIFS function to calculate the average Gross Monthly Salary for employees from Winnipeg with Rating of $6$ or less and Education of $3$ or more and who are not Bilingual.Format answer as comma style, $0$ decimal.

In W$14$ 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, $0$ decimal.

In cell X$14$ use the COUNTIFS function to determine how many Female employees have a Rating of more than $4$ and an Education of $2$ and are from Department D$.$

In cell Y$14$ use the MATCH Function to answer: What is the Row # $($in the range of Gross Monthly Salary data$)$ that $5477$ can be found?Blank $1$

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

In cell AA$14$ use the SUBTOTAL Function only to calculate the average Gross Monthly Salary for all employeesBlank $1($Format as comma style, $0$ decimal$)$

Filter the data to show female employees with Rating of $5$ or more from ON$.$

The Subtotal value in AA$14$ should update to show the result for the filtered data.

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

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

Clear the filters.

Blank $1$

Add your answer

Blank $2$

Add your answer