1 Name cell C7 as Rejection_Rate.

Check the TRUE box once this has been done.

 

2 In M14: use formula to calculate the Units Rejected (multiply Units Made by the Rejection Rate in C7). Copy the function down for the employees.

In M10: Use the appropriate function to calculate the average Units Rejected. Format as comma style, 0 decimals

 

3In N14: Use the appropriate formula to calculate the Net Units. Copy the function down for the employees.

In N10: Use the appropriate function to calculate the Total Net Units. Format as comma style, 0 decimals

 

4What if is the Rejection Rate in C7 is changed to 5%?
Enter the new Net Units value (when the Rejection Rate is changed to 5%) below. Format as comma style, 0 decimals

 

5What if the goal is to have a total Net Units of 8,500,000? Use Goal Seek to determine what change in the Rejection Rate in C7 would achieve this goal.
Enter the new Rejection Rate % value (to achieve the total Net Units of 8,500,000) below. Format as percent style, 2 decimals

 

6 In O14: Use the appropriate function to extract the month from the Date of Birth. Copy the function down for all the employees.

In O10: Use the appropriate function to calculate how many employees were born in the month of January?

 

7 In P14: Use the appropriate function to extract the year from the Date of Birth. Copy the function down for all the employees.

In P10: Use the appropriate function to calculate how many employees were born after 1970?
 

8 In Q14: Use the appropriate function, along with the Date in C6 and the Date of Birth to calculate the Age in Days.  Copy the function down for all the employees.

In Q10: Use the appropriate function to calculate the average Age in Days. Format as comma style, 0 decimal.
 

9 In R14: Use the appropriate formula to calculate the employees Age in years. (Hint: divide the Age in Days by 365). Copy the function down for all the employees.

In R10: Use the appropriate function to calculate how many employees are younger than 45 years?  
 

10 In S14: Use the appropriate function to answer the question:

If the Rating is A, enter 1000, otherwise enter 300. Copy the function down for the employees.

In S10: Use the appropriate function to calculate the Total Rating Bonus. Format as comma style, 0 decimals

 

11 In T14: Use the appropriate function to answer the question:

If the Department is 4302, enter 1000, if the Department is 4308, enter 800, if  the Department is 4312, enter 600, otherwise enter 400. Copy the function down for the employees.

In T10: Use the appropriate function to calculate the Total Department Bonus. Format as comma style, 0 decimals

 

12 In U14: Based on the Days Absent use the VLOOKUP function, along with the VLOOKUP table, to enter the bonus for the employee. Copy the function down for the other employees.

In U10: Use the appropriate function to calculate the Total Days Absent Bonus. Format as comma style, 0 decimals

 

13 In V10: Use the appropriate function to calculate the Total Units Made by employees from Department 4302 who were born before January 1, 1970 and have an A Rating? Format as comma style, 0 decimals

 

14 In W10: Use the appropriate function to calculate the average Salary for female employees from Department 5894 who have an of E Rating? Format as comma style, 0 decimal.

 

15 In X10: Use the appropriate function to calculate how many employees have a Salary of less than $80,000 and are from Department 4302, and have a D Rating? Format as comma style, 0 decimal.

 

16 In Y10: Use the MATCH function to return the Row # in which 21,982 can be found in the range of Units Made values? Enter the row # below.

 

17 In Z10: Use the INDEX function and the Row number from the MATCH function to return the First Name of the employee who had 21,982 Units Made. Enter the name below.

 

18 In AA10: Use the Subtotal function only to calculate the average Units Made. Format as comma style, 0 decimal.

 

19 Filter the table to show female employees from Departments 4302, 4308 and 5894 with A or B Rating. What is the new average Units Made in AA10? Enter that value in AB10 and below. Format as comma style, 0 decimals

 

20 Click on the Advanced Filter worksheet.

Use the Advanced Filter to filter the table to show how many employees are from Departments 4302 and have an A Rating OR have a Salary of $80,000 or more and Years of Service of more than 20 years. Copy the filter to another location. Enter the number below.

 

 

https://1drv.ms/x/s!ApJIr6Uzz7fFzDMy4fIQu8QRLS0K?e=0uYOzq