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

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

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

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

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

16. 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. 17. 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

18. 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. 19. In AA10: Use the Subtotal function only to calculate the average Units Made. Format as comma style, 0 decimal.

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

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

AutoSave OFF ^ A 20... Home Insert Draw Page Layout Calibri (Body) 12 LA (6 B I U E 13 14 15 16 18 20 Q10 A 1 tmployee Dots 2 3 5 6 Date 7 Rejection Rate 9 Last Name 13816 Abbar 13832 Abercrombie 10774 Ackerman 11737 Adel 14470 Akers 10598 Akhtar 11194 Abouco 11631 Alexander 14548 Alvardo 14957 Anderson 12911 Anderson 14048 Ansari 13827 Atkinson 14765 Bacon Jr. 13250 Baldwin 14923 Balke Save Bac 10827 Bambil 14584 Bar 10149 Barrett 11889 Bartkowski 14347 Beck 12005 Ben-Sachar 10069 Benson 12061 Ser 12833 Berglund 12037 Bergman 10040 Berry 13232 Bakeman 14038 Sete 11061 Bockman 13700 dan 21 22 23 24 25 20 27 28 20 30 31 32 33 34 Paste 35 30 37 38 30 40 41 42 43 43 44 45 Employee ID Ax x fx 8 C 01-May-201 3.50% First Name Department Gende 4302 F Km 5894 F Pilar 5894 F Jeft 4308 F kim 4303 F Sarah 5894 F Steve 43081 Sean 4308 M Mario 6007 F 4308 F Amy Charlie 4509 M Zachary 6007 M THERLA 4372 F Dan 4509 M Amie 4308 F Wendy 6007 F Angela 4509 F Jash 4312 M Adam 4372 M Holly 4302 F Joanna 6008 F Bradley 4312 M de 4509 F 4509 M Max Karen 4302 F Andreas 4370 M Ken Ja Synthia 4302 M 4302 F 6008 F Cleotide 6005 F 4509 F GOOGLE Employee Data Ready Sheett Indra D Formulas Data A A 3 Y H 1 F Days Abent D 1 Review D D E A A A E E A G BOTLE $ 1.000.00 $ 600.00 2 $ 400.001 $ 200.00 4 |$ $ 100.00 $ 20.00 Salary Rating Years of Days Service Absent $ 91,000 B 34.9 35.1 $ 50,000 B $ 31,000 C 31.0 $48.000 E 29.3 $ 47,000 410 $ 90,000 30.6 $ 100,000 28.3 $ 29,000 19.8 $ 53,000 15.2 $ 59,000 281 $ 48,000 13.9 324 $ 89,000 55,000 $ 32.1 $ 62,000 45.9 $ 100,000 151 $ 82,000 15.6 $ 92,000 24.2 $ 83,000 E 42.8 $ 65.000 A 26.8 $ 65.000 A 252 $ 33,000 D 35.8 $ 70.000 A 21.4 $ 77.000 D 26.6 $ 97,000 D 20.5 $ 45,000 E 229 23 02.000 $ 93,000 A 32.2 $ 58,000 A 192 $ 76,000 E 19.6 A 29.3 $ $ 48,000 41,000 D 32.7 $ 43,000 A A 21.7 741 99.000 Advanced Filter + D C E D Excel Practice Test 1 (worth zero marks) (1) (2) View Tell me Custom $ % 9 K M N 646.58 289,574.85 Date of Hire Units Made 24-Jun-85 25-Mar-85 26-Apr-89 01-Feb-91 29-May-79 16.557 20,448 16,453 16,613 16,223 15-Oct-89 16,646 15.564 12-Jan-92 06-Jul-00 19,462 20,533 21 Feb 05 11-A92 20,270 16,685 19,220 06-Jun-06 27-Dec-87 06-Apr-88 22-Jun-74 21-Apr-05 21-Oct-04 21,488 21,343 18,587 15,232 16-Feb-96 15,583 18,959 16,618 Lopes 15,319 26-Jul-77 09-Jul-93 14-Mar-95 13-Jul-84 20-Dec-98 21-Sep-93 21,667 20,223 16,481 15,142 22 Nov 99 01-Jun-97 19-Mar-88 17,581 18,548 21,142 17-Mar-01 20-Sep-00 20,475 19.231 01-Feb-91 11-Sep-87 20-Aug-98 77-Mar-41 21,966 18.340 15.630 J Date of Birth 1 16-Oct-58 4 28-Jan-55 1 05-Oct-50 3 21 Dec 64 08-Apr-58 0 3 22-Apr-70 4 28-May-64 4 16-Oct-62 4 26 Mar 76 A 01-Nov-20 0 20-Jun-76 2 26-Sep-51 4 14-Jan-61 4 15 May 49 3 24-Now-77 2 15-Dec-76 4 18-Apr-64 4 12-Oct-54 4 08-Apr 59 2 30-Jan-70 3 28-Feb-54 05-Feb-58 4 4 12-Jul-56 3 02 Sep 75 1 27-Feb-65 5 21-Oct-54 3 28-Jan-74 4 14-Jun-66 0 21-Dec-64 2 27-May-56 3 18-Now-51 al 20-ad-64 L Units Rejected 579 716 576 581 568 583 545 681 719 709 584 673 752 747 651 568 543 664 582 536 www 758 708 577 $30 615 015 649 740 717 673 769 642 547 Conditional Format Formatting as Table P 36 Net Units Birth Month 15,978 19,732 15,877 16,032 15,655 16,063 10 1 10 12 4 4 15,019 18,781 19.814 19,561 16,101 18,547 20,736 20,596 17,936 15,664 15,040 18,295 16,036 14,783 20,909 19.515 15,904 14,612 16,955 10:00 17,899 20,402 19,758 18,558 21,197 17,698 15 GRA 5 10 3 11 6 9 1 5 11 12 4 10 4 1 2 2 7 9 2 10 1 6 12 11 7 129 Birth Year 1958 1955 1950 1964 1958 1970 1954 1962 1976 1970 1976 1951 1961 1949 1977 1976 1954 1954 1959 1970 1954 1958 1956 1975 1965 TAZA 1954 1974 1966 1954 1956 1951 1954 Cell Styles Age In Danya R Age In Years Insert v xDelete v Format v 5 Rating Bonus @ 18 Share VAT | O Sort & Filter T Department Days Absent Bonus Bonus [10] Find & Select V a d Comments FO LIN Analyze Data W + 75%