Department Bonus If the Dept is 6007, enter 900, if it is 4305, enter 600, if it is 4312, enter 350, otherwise enter 200. Copy the function down for the employees. Calculate the total Department Bonus in cell Q1. OR IF AND etc. -nt Days Absent Bonus Use the VLOOKUP function to enter the appropriate Bonus value for each employee based on R etc. the Days Absent. Calculate the total bonus for all the employees in R1. AND IF Comment Use VLOOKUP to insert the appropriate comment for each employee. How many employees have the Good comment? IF AND OR etc. If the Rating is A, B or D and the Department is 4302, 4303 or 6007, and the Units Rejected is less than 2500, enter 1000, otherwise enter 0. What is the total? Calculate the total in T1. D . IF AND OR etc. If the Units Made is 17000 or more IOR the Units Rejected is 2000 or less AND the Rating is A or B enter 1000, otherwise enter 0. What is the total? Calculate the total in U1. D CC. IF AND IF AND OR etc. If the 3rd character of the ID I# is a 3, 5 or 8 AND the Rating is A, B, C or D enter 750, otherwise enter 0. Calculate the total in V1. D CC. IF AND IF AND OR etc. If the 3rd character of the ID I# is a 3, 5 or 8 AND the Rating is A, B, C or D enter 750, otherwise enter 0. Calculate the total in V1. D IF AND OR C. etc. If the ID ends with a 2, 4, 6, or 8 and the Days Absent is 2 or less, enter 500, otherwise the cell should be blank. What is the total? Calculate the total in W1. RIF AND OR etc. If the Department begins with 43 or 60 and the Units Made 19000 or more, enter Yes, otherwise enter No. How many Yes are there? A 1 Employee Data 2 3 4 5 6 7 8 9 LO 11 12 13 B Employee Last Name ID 13816 Abbar 13832 Abercrombie 10774 Ackerman 11737 Adell 14470 Akers 10598 Akhtar 11194 Alboucq 11631 Alexander 14548 Alvardo D First Date of Name Hire E Date of Birth Anas Kim Pilar Jeff 24-Jun-85 16-Oct-58 25-Mar-85 28-Jan-55 26-Apr-89 5-Oct-50 1-Feb-91 21-Dec-64 Kim 29-May-79 8-Apr-58 Sarah 15-Oct-89 22-Apr-70 Steve 12-Jan-92 28-May-64 Sean 6-Jul-00 16-Oct-62 Marion 21-Feb-05 26-Mar-76 F Age G Gender 61.49 F 65.21 F 69.52 F 55.30 F 62.01 F 49.96 F 55.87 F 57.48 M 44.03 F $ $ $ $ $ $ $ $ $ H Salary 1 J 91,000 B 50,000 B 31,000 C 48,000 E 47,000 D 90,000 D 100,000 E 29,000 A 53,000 A Rating Years of Vacation Service Days K 35.6 35.9 31.8 30.0 41.7 31.3 29.1 20.6 16.0 L 30 16,309 30 19,089 30 20,643 30 15,523 30 17,939 30 13,602 25 15,728 M 20 16,309 15 14,851 Units Units Days Department Made Rejected Absent N 2,370 2,815 3,063 2,244 2,471 1,937 2,277 2,290 3126 O 1 4 1 3 0 3 4 4 4 4302 5894 5894 4308 4303 5894 4308 4308 6007 P 0 R Department Days Bonus Absent Bonus S Comment T IF AND OR etc. U IF AND OR etc. V W X IF AND IF AND OR IF AND OR OR etc. etc. etc. Y Z 4 5 6 BUS308-Quiz1 (1) A B Employee Last Name ID 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 44 13816 Abbar 13832 Abercrombie 10774 Ackerman 11737 Adell 14470 Akers 10598 Akhtar 11194 Alboucq 11631 Alexander 14548 Alvardo 14957 Anderson 12911 Anderson 14048 Ansari 13827 Atkinson 14765 Bacon Jr. 13250 Baldwin 14923 Balke 14675 Barbariol 10827 Barnhill 14584 Barr 10149 Barrett 11889 Bartkowski 14347 Beck 12005 Ben-Sachar 10069 Benson 12061 Berge 12833 Berglund 12037 Bergman 10940 Berry 13232 Blakeman 14038 Blose 11061 Bockman 13700 Bogdan Employee Data C First Name D Date of Hire Anas Kim Pilar Jeff Kim Sarah Steve Sean Marion E Date of Birth 24-Jun-85 16-Oct-58 25-Mar-85 28-Jan-55 26-Apr-89 5-Oct-50 1-Feb-91 21-Dec-64 29-May-79 8-Apr-58 15-Oct-89 22-Apr-70 12-Jan-92 28-May-64 6-Jul-00 16-Oct-62 21-Feb-05 26-Mar-76 11-Apr-92 1-Nov-70 6-Jun-06 20-Jun-76 27-Dec-87 26-Sep-51 6-Apr-88 14-Jan-61 22-Jun-74 15-May-49 21-Apr-05 24-Nov-77 21-Oct-04 15-Dec-76 16-Feb-96 18-Apr-64 26-Jul-77 12-Oct-54 9-Jul-93 8-Apr-59 14-Mar-95 30-Jan-70 13-Jul-84 28-Feb-54 20-Dec-98 5-Feb-58 21-Sep-93 12-Jul-56 Max 22-Nov-99 2-Sep-75 Karen 1-Jun-97 27-Feb-65 Andreas 19-Mar-88 21-Oct-54 Ken 17-Mar-01 28-Jan-74 Jo 20-Sep-00 14-Jun-66 Synthia 1-Feb-91 21-Dec-64 Cleotilde 11-Sep-87 27-May-56 Anne 20-Aug-98 18-Nov-51 Deadra 27-Mar-91 20-Jul-64 Amy Charlie Zachary Teresa Dan Amie Wendy Angela Josh Adam Holly Joanna Bradley Ido F G Age Gender 61.49 F 65.21 F 69.52 F 55.30 F 62.01 F 49.96 F 55.87 F 57.48 M 44.03 F 49.44 F 43.80 M 68.55 M 59.24 F 70.92 M 42.37 F 43.31 F 55.98 F 65.50 M 61.01 M 50.19 F 66.12 F 62.18 M 63.75 F 44.60 M 55.12 F 65.48 M 46.19 M 53.82 F 55.30 F 63.88 F 68.40 F 55.72 F $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 33,000 70,000 77,000 D $ $ $ $ $ $ $ A GA GA $ H Salary Rating Years of Vacation Units Service B B E 91,000 50,000 31,000 48,000 47,000 D 90,000 D 100,000 F 29,000 A 53,000 A 59,000 A 48,000 E 89,000 E 55,000 A 62,000 100,000 82,000 92,000 83,000 E 66,000 A 65,000 A D C E D D A $ C olohulalahulolololo 97,000 45,000 F 93,000 A D 58,000 A 76,000 E 48,000 A 41,000 $ 43,000 $ D A 99,000 A 35.6 35.9 31.8 30.0 41.7 31.3 29.1 20.6 16.0 28.8 14.7 33.1 32.8 46.6 15.8 16.3 25.0 43.6 27.6 25.9 36.6 22.1 27.4 21.2 23.7 32.9 19.9 20.4 30.0 33.4 22.5 29.9 Days Made Rejected Absent 30 16,309 30 19,089 30 20,643 30 15,523 30 17,939 30 13,602 25 15,728 20 16,309 15 14,851 25 12,691 10 11,382 30 15,686 30 16,941 30 21,144 15 10,867 15 15,058 20 15,767 30 19,195 25 17,579 25 12,963 30 19,849 20 18,000 25 18,565 20 11,670 20 15,456 30 19,186 M N O Units Days Department 15 10,803 20 14,991 30 20,569 30 19,966 20 20,240 25 18,602 2,370 2,815 3,063 2,244 2,471 1,937 2,277 2,290 3126 1,631 1,422 2349 2,471 3,144 1,499 1058 2,123 2,832 2,573 2,047 3067 2,640 2,731 1,628 2,153 2,670 1,489 2,079 2488 2915 3,212 1369 4 1 0 3 4 41 4 4 0 2 4 4 3 2 4 4 4 2 3 4 4 3 1 5 3 4 0 2 0 4302 5894 5894 4308 4303 5894 4308 4308 6007 4308 4509 6007 4372 4509 4308 6007 4509 4312 4372 4302 6008 4312 4509 4509 4302 4370 4302 4302 6008 6005 4509 6006 P Q R Department Days Comment Bonus Absent Bonus T IF AND OR etc. U IF AND OR etc. V W X IF AND IF AND OR IF AND OR OR etc. etc. etc. Y Z - AA X D Question 1 If the Dept is 6007, enter 900, if the Department is 4305, enter 600, if the Department is 4312, enter 350, otherwise enter 200. Copy the function down for the employees. Calculate the total Department Bonus for all the employees in cell P1. What is this amount? Format as comma style, 0 decimals Question 2 Use the VLOOKUP function to enter the appropriate Bonus value for each employee based on the Days Absent. Calculate the total bonus for all the employees. What is this amount? Format as comma style 0 decimal. Question 3 Use VLOOKUP to insert the appropriate comment for each employee. How many employees have the Good comment? Question 4 2 pts If the Rating is A, B or D and the Department is 4302, 4303 or 6007, and the Units Rejected is less than 2500, enter 1000, otherwise enter O. What is the total? Format as comma style, 0 decimals. 5 pts 5 pts 5 pts Tir Att 71 Department Bonus If the Dept is 6007, enter 900, if it is 4305, enter 600, if it is 4312, enter 350, otherwise enter 200. Copy the function down for the employees. Calculate the total Department Bonus in cell Q1. OR IF AND etc. -nt Days Absent Bonus Use the VLOOKUP function to enter the appropriate Bonus value for each employee based on R etc. the Days Absent. Calculate the total bonus for all the employees in R1. AND IF Comment Use VLOOKUP to insert the appropriate comment for each employee. How many employees have the Good comment? IF AND OR etc. If the Rating is A, B or D and the Department is 4302, 4303 or 6007, and the Units Rejected is less than 2500, enter 1000, otherwise enter 0. What is the total? Calculate the total in T1. D . IF AND OR etc. If the Units Made is 17000 or more IOR the Units Rejected is 2000 or less AND the Rating is A or B enter 1000, otherwise enter 0. What is the total? Calculate the total in U1. D CC. IF AND IF AND OR etc. If the 3rd character of the ID I# is a 3, 5 or 8 AND the Rating is A, B, C or D enter 750, otherwise enter 0. Calculate the total in V1. D CC. IF AND IF AND OR etc. If the 3rd character of the ID I# is a 3, 5 or 8 AND the Rating is A, B, C or D enter 750, otherwise enter 0. Calculate the total in V1. D IF AND OR C. etc. If the ID ends with a 2, 4, 6, or 8 and the Days Absent is 2 or less, enter 500, otherwise the cell should be blank. What is the total? Calculate the total in W1. RIF AND OR etc. If the Department begins with 43 or 60 and the Units Made 19000 or more, enter Yes, otherwise enter No. How many Yes are there? A 1 Employee Data 2 3 4 5 6 7 8 9 LO 11 12 13 B Employee Last Name ID 13816 Abbar 13832 Abercrombie 10774 Ackerman 11737 Adell 14470 Akers 10598 Akhtar 11194 Alboucq 11631 Alexander 14548 Alvardo D First Date of Name Hire E Date of Birth Anas Kim Pilar Jeff 24-Jun-85 16-Oct-58 25-Mar-85 28-Jan-55 26-Apr-89 5-Oct-50 1-Feb-91 21-Dec-64 Kim 29-May-79 8-Apr-58 Sarah 15-Oct-89 22-Apr-70 Steve 12-Jan-92 28-May-64 Sean 6-Jul-00 16-Oct-62 Marion 21-Feb-05 26-Mar-76 F Age G Gender 61.49 F 65.21 F 69.52 F 55.30 F 62.01 F 49.96 F 55.87 F 57.48 M 44.03 F $ $ $ $ $ $ $ $ $ H Salary 1 J 91,000 B 50,000 B 31,000 C 48,000 E 47,000 D 90,000 D 100,000 E 29,000 A 53,000 A Rating Years of Vacation Service Days K 35.6 35.9 31.8 30.0 41.7 31.3 29.1 20.6 16.0 L 30 16,309 30 19,089 30 20,643 30 15,523 30 17,939 30 13,602 25 15,728 M 20 16,309 15 14,851 Units Units Days Department Made Rejected Absent N 2,370 2,815 3,063 2,244 2,471 1,937 2,277 2,290 3126 O 1 4 1 3 0 3 4 4 4 4302 5894 5894 4308 4303 5894 4308 4308 6007 P 0 R Department Days Bonus Absent Bonus S Comment T IF AND OR etc. U IF AND OR etc. V W X IF AND IF AND OR IF AND OR OR etc. etc. etc. Y Z 4 5 6 BUS308-Quiz1 (1) A B Employee Last Name ID 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 44 13816 Abbar 13832 Abercrombie 10774 Ackerman 11737 Adell 14470 Akers 10598 Akhtar 11194 Alboucq 11631 Alexander 14548 Alvardo 14957 Anderson 12911 Anderson 14048 Ansari 13827 Atkinson 14765 Bacon Jr. 13250 Baldwin 14923 Balke 14675 Barbariol 10827 Barnhill 14584 Barr 10149 Barrett 11889 Bartkowski 14347 Beck 12005 Ben-Sachar 10069 Benson 12061 Berge 12833 Berglund 12037 Bergman 10940 Berry 13232 Blakeman 14038 Blose 11061 Bockman 13700 Bogdan Employee Data C First Name D Date of Hire Anas Kim Pilar Jeff Kim Sarah Steve Sean Marion E Date of Birth 24-Jun-85 16-Oct-58 25-Mar-85 28-Jan-55 26-Apr-89 5-Oct-50 1-Feb-91 21-Dec-64 29-May-79 8-Apr-58 15-Oct-89 22-Apr-70 12-Jan-92 28-May-64 6-Jul-00 16-Oct-62 21-Feb-05 26-Mar-76 11-Apr-92 1-Nov-70 6-Jun-06 20-Jun-76 27-Dec-87 26-Sep-51 6-Apr-88 14-Jan-61 22-Jun-74 15-May-49 21-Apr-05 24-Nov-77 21-Oct-04 15-Dec-76 16-Feb-96 18-Apr-64 26-Jul-77 12-Oct-54 9-Jul-93 8-Apr-59 14-Mar-95 30-Jan-70 13-Jul-84 28-Feb-54 20-Dec-98 5-Feb-58 21-Sep-93 12-Jul-56 Max 22-Nov-99 2-Sep-75 Karen 1-Jun-97 27-Feb-65 Andreas 19-Mar-88 21-Oct-54 Ken 17-Mar-01 28-Jan-74 Jo 20-Sep-00 14-Jun-66 Synthia 1-Feb-91 21-Dec-64 Cleotilde 11-Sep-87 27-May-56 Anne 20-Aug-98 18-Nov-51 Deadra 27-Mar-91 20-Jul-64 Amy Charlie Zachary Teresa Dan Amie Wendy Angela Josh Adam Holly Joanna Bradley Ido F G Age Gender 61.49 F 65.21 F 69.52 F 55.30 F 62.01 F 49.96 F 55.87 F 57.48 M 44.03 F 49.44 F 43.80 M 68.55 M 59.24 F 70.92 M 42.37 F 43.31 F 55.98 F 65.50 M 61.01 M 50.19 F 66.12 F 62.18 M 63.75 F 44.60 M 55.12 F 65.48 M 46.19 M 53.82 F 55.30 F 63.88 F 68.40 F 55.72 F $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 33,000 70,000 77,000 D $ $ $ $ A GA GA $ H Salary Rating Years of Vacation Units Service E 91,000 50,000 31,000 48,000 47,000 D 90,000 D 100,000 F 29,000 A 53,000 A 59,000 A 48,000 E 89,000 E 55,000 A 62,000 100,000 82,000 92,000 83,000 E 66,000 A 65,000 A D C E D D A $ B B C olohulalahulolololo 97,000 45,000 F 93,000 A D 58,000 A 76,000 E 48,000 A 41,000 $ 43,000 $ D A 99,000 A 35.6 35.9 31.8 30.0 41.7 31.3 29.1 20.6 16.0 28.8 14.7 33.1 32.8 46.6 15.8 16.3 25.0 43.6 27.6 25.9 36.6 22.1 27.4 21.2 23.7 32.9 19.9 20.4 30.0 33.4 22.5 29.9 Days Made Rejected Absent 30 16,309 30 19,089 30 20,643 30 15,523 30 17,939 30 13,602 25 15,728 20 16,309 15 14,851 25 12,691 10 11,382 30 15,686 30 16,941 30 21,144 15 10,867 15 15,058 20 15,767 30 19,195 25 17,579 25 12,963 30 19,849 20 18,000 25 18,565 20 11,670 20 15,456 30 19,186 M N O Units Days Department 15 10,803 20 14,991 30 20,569 30 19,966 20 20,240 25 18,602 2,370 2,815 3,063 2,244 2,471 1,937 2,277 2,290 3126 1,631 1,422 2349 2,471 3,144 1,499 1058 2,123 2,832 2,573 2,047 3067 2,640 2,731 1,628 2,153 2,670 1,489 2,079 2488 2915 3,212 1369 4 1 0 3 4 41 4 4 0 2 4 4 3 2 4 4 4 2 3 4 4 3 1 5 3 4 0 2 0 4302 5894 5894 4308 4303 5894 4308 4308 6007 4308 4509 6007 4372 4509 4308 6007 4509 4312 4372 4302 6008 4312 4509 4509 4302 4370 4302 4302 6008 6005 4509 6006 P Q R Department Days Comment Bonus Absent Bonus T IF AND OR etc. U IF AND OR etc. V W X IF AND IF AND OR IF AND OR OR etc. etc. etc. Y Z - AA X D Question 1 If the Dept is 6007, enter 900, if the Department is 4305, enter 600, if the Department is 4312, enter 350, otherwise enter 200. Copy the function down for the employees. Calculate the total Department Bonus for all the employees in cell P1. What is this amount? Format as comma style, 0 decimals Question 2 Use the VLOOKUP function to enter the appropriate Bonus value for each employee based on the Days Absent. Calculate the total bonus for all the employees. What is this amount? Format as comma style 0 decimal. Question 3 Use VLOOKUP to insert the appropriate comment for each employee. How many employees have the Good comment? Question 4 2 pts If the Rating is A, B or D and the Department is 4302, 4303 or 6007, and the Units Rejected is less than 2500, enter 1000, otherwise enter O. What is the total? Format as comma style, 0 decimals. 5 pts 5 pts 5 pts Tir Att 71 Department Bonus If the Dept is 6007, enter 900, if it is 4305, enter 600, if it is 4312, enter 350, otherwise enter 200. Copy the function down for the employees. Calculate the total Department Bonus in cell Q1. OR IF AND etc. -nt Days Absent Bonus Use the VLOOKUP function to enter the appropriate Bonus value for each employee based on R etc. the Days Absent. Calculate the total bonus for all the employees in R1. AND IF Comment Use VLOOKUP to insert the appropriate comment for each employee. How many employees have the Good comment? IF AND OR etc. If the Rating is A, B or D and the Department is 4302, 4303 or 6007, and the Units Rejected is less than 2500, enter 1000, otherwise enter 0. What is the total? Calculate the total in T1. D . IF AND OR etc. If the Units Made is 17000 or more IOR the Units Rejected is 2000 or less AND the Rating is A or B enter 1000, otherwise enter 0. What is the total? Calculate the total in U1. D CC. IF AND IF AND OR etc. If the 3rd character of the ID I# is a 3, 5 or 8 AND the Rating is A, B, C or D enter 750, otherwise enter 0. Calculate the total in V1. D CC. IF AND IF AND OR etc. If the 3rd character of the ID I# is a 3, 5 or 8 AND the Rating is A, B, C or D enter 750, otherwise enter 0. Calculate the total in V1. D IF AND OR C. etc. If the ID ends with a 2, 4, 6, or 8 and the Days Absent is 2 or less, enter 500, otherwise the cell should be blank. What is the total? Calculate the total in W1. RIF AND OR etc. If the Department begins with 43 or 60 and the Units Made 19000 or more, enter Yes, otherwise enter No. How many Yes are there? A 1 Employee Data 2 3 4 5 6 7 8 9 LO 11 12 13 B Employee Last Name ID 13816 Abbar 13832 Abercrombie 10774 Ackerman 11737 Adell 14470 Akers 10598 Akhtar 11194 Alboucq 11631 Alexander 14548 Alvardo D First Date of Name Hire E Date of Birth Anas Kim Pilar Jeff 24-Jun-85 16-Oct-58 25-Mar-85 28-Jan-55 26-Apr-89 5-Oct-50 1-Feb-91 21-Dec-64 Kim 29-May-79 8-Apr-58 Sarah 15-Oct-89 22-Apr-70 Steve 12-Jan-92 28-May-64 Sean 6-Jul-00 16-Oct-62 Marion 21-Feb-05 26-Mar-76 F Age G Gender 61.49 F 65.21 F 69.52 F 55.30 F 62.01 F 49.96 F 55.87 F 57.48 M 44.03 F $ $ $ $ $ $ $ $ $ H Salary 1 J 91,000 B 50,000 B 31,000 C 48,000 E 47,000 D 90,000 D 100,000 E 29,000 A 53,000 A Rating Years of Vacation Service Days K 35.6 35.9 31.8 30.0 41.7 31.3 29.1 20.6 16.0 L 30 16,309 30 19,089 30 20,643 30 15,523 30 17,939 30 13,602 25 15,728 M 20 16,309 15 14,851 Units Units Days Department Made Rejected Absent N 2,370 2,815 3,063 2,244 2,471 1,937 2,277 2,290 3126 O 1 4 1 3 0 3 4 4 4 4302 5894 5894 4308 4303 5894 4308 4308 6007 P 0 R Department Days Bonus Absent Bonus S Comment T IF AND OR etc. U IF AND OR etc. V W X IF AND IF AND OR IF AND OR OR etc. etc. etc. Y Z 4 5 6 BUS308-Quiz1 (1) A B Employee Last Name ID 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 44 13816 Abbar 13832 Abercrombie 10774 Ackerman 11737 Adell 14470 Akers 10598 Akhtar 11194 Alboucq 11631 Alexander 14548 Alvardo 14957 Anderson 12911 Anderson 14048 Ansari 13827 Atkinson 14765 Bacon Jr. 13250 Baldwin 14923 Balke 14675 Barbariol 10827 Barnhill 14584 Barr 10149 Barrett 11889 Bartkowski 14347 Beck 12005 Ben-Sachar 10069 Benson 12061 Berge 12833 Berglund 12037 Bergman 10940 Berry 13232 Blakeman 14038 Blose 11061 Bockman 13700 Bogdan Employee Data C First Name D Date of Hire Anas Kim Pilar Jeff Kim Sarah Steve Sean Marion E Date of Birth 24-Jun-85 16-Oct-58 25-Mar-85 28-Jan-55 26-Apr-89 5-Oct-50 1-Feb-91 21-Dec-64 29-May-79 8-Apr-58 15-Oct-89 22-Apr-70 12-Jan-92 28-May-64 6-Jul-00 16-Oct-62 21-Feb-05 26-Mar-76 11-Apr-92 1-Nov-70 6-Jun-06 20-Jun-76 27-Dec-87 26-Sep-51 6-Apr-88 14-Jan-61 22-Jun-74 15-May-49 21-Apr-05 24-Nov-77 21-Oct-04 15-Dec-76 16-Feb-96 18-Apr-64 26-Jul-77 12-Oct-54 9-Jul-93 8-Apr-59 14-Mar-95 30-Jan-70 13-Jul-84 28-Feb-54 20-Dec-98 5-Feb-58 21-Sep-93 12-Jul-56 Max 22-Nov-99 2-Sep-75 Karen 1-Jun-97 27-Feb-65 Andreas 19-Mar-88 21-Oct-54 Ken 17-Mar-01 28-Jan-74 Jo 20-Sep-00 14-Jun-66 Synthia 1-Feb-91 21-Dec-64 Cleotilde 11-Sep-87 27-May-56 Anne 20-Aug-98 18-Nov-51 Deadra 27-Mar-91 20-Jul-64 Amy Charlie Zachary Teresa Dan Amie Wendy Angela Josh Adam Holly Joanna Bradley Ido F G Age Gender 61.49 F 65.21 F 69.52 F 55.30 F 62.01 F 49.96 F 55.87 F 57.48 M 44.03 F 49.44 F 43.80 M 68.55 M 59.24 F 70.92 M 42.37 F 43.31 F 55.98 F 65.50 M 61.01 M 50.19 F 66.12 F 62.18 M 63.75 F 44.60 M 55.12 F 65.48 M 46.19 M 53.82 F 55.30 F 63.88 F 68.40 F 55.72 F $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 33,000 70,000 77,000 D $ $ $ $ $ $ $ A GA GA $ H Salary Rating Years of Vacation Units Service B B E 91,000 50,000 31,000 48,000 47,000 D 90,000 D 100,000 F 29,000 A 53,000 A 59,000 A 48,000 E 89,000 E 55,000 A 62,000 100,000 82,000 92,000 83,000 E 66,000 A 65,000 A D C E D D A $ C olohulalahulolololo 97,000 45,000 F 93,000 A D 58,000 A 76,000 E 48,000 A 41,000 $ 43,000 $ D A 99,000 A 35.6 35.9 31.8 30.0 41.7 31.3 29.1 20.6 16.0 28.8 14.7 33.1 32.8 46.6 15.8 16.3 25.0 43.6 27.6 25.9 36.6 22.1 27.4 21.2 23.7 32.9 19.9 20.4 30.0 33.4 22.5 29.9 Days Made Rejected Absent 30 16,309 30 19,089 30 20,643 30 15,523 30 17,939 30 13,602 25 15,728 20 16,309 15 14,851 25 12,691 10 11,382 30 15,686 30 16,941 30 21,144 15 10,867 15 15,058 20 15,767 30 19,195 25 17,579 25 12,963 30 19,849 20 18,000 25 18,565 20 11,670 20 15,456 30 19,186 M N O Units Days Department 15 10,803 20 14,991 30 20,569 30 19,966 20 20,240 25 18,602 2,370 2,815 3,063 2,244 2,471 1,937 2,277 2,290 3126 1,631 1,422 2349 2,471 3,144 1,499 1058 2,123 2,832 2,573 2,047 3067 2,640 2,731 1,628 2,153 2,670 1,489 2,079 2488 2915 3,212 1369 4 1 0 3 4 41 4 4 0 2 4 4 3 2 4 4 4 2 3 4 4 3 1 5 3 4 0 2 0 4302 5894 5894 4308 4303 5894 4308 4308 6007 4308 4509 6007 4372 4509 4308 6007 4509 4312 4372 4302 6008 4312 4509 4509 4302 4370 4302 4302 6008 6005 4509 6006 P Q R Department Days Comment Bonus Absent Bonus T IF AND OR etc. U IF AND OR etc. V W X IF AND IF AND OR IF AND OR OR etc. etc. etc. Y Z - AA X D Question 1 If the Dept is 6007, enter 900, if the Department is 4305, enter 600, if the Department is 4312, enter 350, otherwise enter 200. Copy the function down for the employees. Calculate the total Department Bonus for all the employees in cell P1. What is this amount? Format as comma style, 0 decimals Question 2 Use the VLOOKUP function to enter the appropriate Bonus value for each employee based on the Days Absent. Calculate the total bonus for all the employees. What is this amount? Format as comma style 0 decimal. Question 3 Use VLOOKUP to insert the appropriate comment for each employee. How many employees have the Good comment? Question 4 2 pts If the Rating is A, B or D and the Department is 4302, 4303 or 6007, and the Units Rejected is less than 2500, enter 1000, otherwise enter O. What is the total? Format as comma style, 0 decimals. 5 pts 5 pts 5 pts Tir Att 71 Department Bonus If the Dept is 6007, enter 900, if it is 4305, enter 600, if it is 4312, enter 350, otherwise enter 200. Copy the function down for the employees. Calculate the total Department Bonus in cell Q1. OR IF AND etc. -nt Days Absent Bonus Use the VLOOKUP function to enter the appropriate Bonus value for each employee based on R etc. the Days Absent. Calculate the total bonus for all the employees in R1. AND IF Comment Use VLOOKUP to insert the appropriate comment for each employee. How many employees have the Good comment? IF AND OR etc. If the Rating is A, B or D and the Department is 4302, 4303 or 6007, and the Units Rejected is less than 2500, enter 1000, otherwise enter 0. What is the total? Calculate the total in T1. D . IF AND OR etc. If the Units Made is 17000 or more IOR the Units Rejected is 2000 or less AND the Rating is A or B enter 1000, otherwise enter 0. What is the total? Calculate the total in U1. D CC. IF AND IF AND OR etc. If the 3rd character of the ID I# is a 3, 5 or 8 AND the Rating is A, B, C or D enter 750, otherwise enter 0. Calculate the total in V1. D CC. IF AND IF AND OR etc. If the 3rd character of the ID I# is a 3, 5 or 8 AND the Rating is A, B, C or D enter 750, otherwise enter 0. Calculate the total in V1. D IF AND OR C. etc. If the ID ends with a 2, 4, 6, or 8 and the Days Absent is 2 or less, enter 500, otherwise the cell should be blank. What is the total? Calculate the total in W1. RIF AND OR etc. If the Department begins with 43 or 60 and the Units Made 19000 or more, enter Yes, otherwise enter No. How many Yes are there? A 1 Employee Data 2 3 4 5 6 7 8 9 LO 11 12 13 B Employee Last Name ID 13816 Abbar 13832 Abercrombie 10774 Ackerman 11737 Adell 14470 Akers 10598 Akhtar 11194 Alboucq 11631 Alexander 14548 Alvardo D First Date of Name Hire E Date of Birth Anas Kim Pilar Jeff 24-Jun-85 16-Oct-58 25-Mar-85 28-Jan-55 26-Apr-89 5-Oct-50 1-Feb-91 21-Dec-64 Kim 29-May-79 8-Apr-58 Sarah 15-Oct-89 22-Apr-70 Steve 12-Jan-92 28-May-64 Sean 6-Jul-00 16-Oct-62 Marion 21-Feb-05 26-Mar-76 F Age G Gender 61.49 F 65.21 F 69.52 F 55.30 F 62.01 F 49.96 F 55.87 F 57.48 M 44.03 F $ $ $ $ $ $ $ $ $ H Salary 1 J 91,000 B 50,000 B 31,000 C 48,000 E 47,000 D 90,000 D 100,000 E 29,000 A 53,000 A Rating Years of Vacation Service Days K 35.6 35.9 31.8 30.0 41.7 31.3 29.1 20.6 16.0 L 30 16,309 30 19,089 30 20,643 30 15,523 30 17,939 30 13,602 25 15,728 M 20 16,309 15 14,851 Units Units Days Department Made Rejected Absent N 2,370 2,815 3,063 2,244 2,471 1,937 2,277 2,290 3126 O 1 4 1 3 0 3 4 4 4 4302 5894 5894 4308 4303 5894 4308 4308 6007 P 0 R Department Days Bonus Absent Bonus S Comment T IF AND OR etc. U IF AND OR etc. V W X IF AND IF AND OR IF AND OR OR etc. etc. etc. Y Z 4 5 6 BUS308-Quiz1 (1) A B Employee Last Name ID 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 44 13816 Abbar 13832 Abercrombie 10774 Ackerman 11737 Adell 14470 Akers 10598 Akhtar 11194 Alboucq 11631 Alexander 14548 Alvardo 14957 Anderson 12911 Anderson 14048 Ansari 13827 Atkinson 14765 Bacon Jr. 13250 Baldwin 14923 Balke 14675 Barbariol 10827 Barnhill 14584 Barr 10149 Barrett 11889 Bartkowski 14347 Beck 12005 Ben-Sachar 10069 Benson 12061 Berge 12833 Berglund 12037 Bergman 10940 Berry 13232 Blakeman 14038 Blose 11061 Bockman 13700 Bogdan Employee Data C First Name D Date of Hire Anas Kim Pilar Jeff Kim Sarah Steve Sean Marion E Date of Birth 24-Jun-85 16-Oct-58 25-Mar-85 28-Jan-55 26-Apr-89 5-Oct-50 1-Feb-91 21-Dec-64 29-May-79 8-Apr-58 15-Oct-89 22-Apr-70 12-Jan-92 28-May-64 6-Jul-00 16-Oct-62 21-Feb-05 26-Mar-76 11-Apr-92 1-Nov-70 6-Jun-06 20-Jun-76 27-Dec-87 26-Sep-51 6-Apr-88 14-Jan-61 22-Jun-74 15-May-49 21-Apr-05 24-Nov-77 21-Oct-04 15-Dec-76 16-Feb-96 18-Apr-64 26-Jul-77 12-Oct-54 9-Jul-93 8-Apr-59 14-Mar-95 30-Jan-70 13-Jul-84 28-Feb-54 20-Dec-98 5-Feb-58 21-Sep-93 12-Jul-56 Max 22-Nov-99 2-Sep-75 Karen 1-Jun-97 27-Feb-65 Andreas 19-Mar-88 21-Oct-54 Ken 17-Mar-01 28-Jan-74 Jo 20-Sep-00 14-Jun-66 Synthia 1-Feb-91 21-Dec-64 Cleotilde 11-Sep-87 27-May-56 Anne 20-Aug-98 18-Nov-51 Deadra 27-Mar-91 20-Jul-64 Amy Charlie Zachary Teresa Dan Amie Wendy Angela Josh Adam Holly Joanna Bradley Ido F G Age Gender 61.49 F 65.21 F 69.52 F 55.30 F 62.01 F 49.96 F 55.87 F 57.48 M 44.03 F 49.44 F 43.80 M 68.55 M 59.24 F 70.92 M 42.37 F 43.31 F 55.98 F 65.50 M 61.01 M 50.19 F 66.12 F 62.18 M 63.75 F 44.60 M 55.12 F 65.48 M 46.19 M 53.82 F 55.30 F 63.88 F 68.40 F 55.72 F $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 33,000 70,000 77,000 D $ $ $ $ A GA GA $ H Salary Rating Years of Vacation Units Service E 91,000 50,000 31,000 48,000 47,000 D 90,000 D 100,000 F 29,000 A 53,000 A 59,000 A 48,000 E 89,000 E 55,000 A 62,000 100,000 82,000 92,000 83,000 E 66,000 A 65,000 A D C E D D A $ B B C olohulalahulolololo 97,000 45,000 F 93,000 A D 58,000 A 76,000 E 48,000 A 41,000 $ 43,000 $ D A 99,000 A 35.6 35.9 31.8 30.0 41.7 31.3 29.1 20.6 16.0 28.8 14.7 33.1 32.8 46.6 15.8 16.3 25.0 43.6 27.6 25.9 36.6 22.1 27.4 21.2 23.7 32.9 19.9 20.4 30.0 33.4 22.5 29.9 Days Made Rejected Absent 30 16,309 30 19,089 30 20,643 30 15,523 30 17,939 30 13,602 25 15,728 20 16,309 15 14,851 25 12,691 10 11,382 30 15,686 30 16,941 30 21,144 15 10,867 15 15,058 20 15,767 30 19,195 25 17,579 25 12,963 30 19,849 20 18,000 25 18,565 20 11,670 20 15,456 30 19,186 M N O Units Days Department 15 10,803 20 14,991 30 20,569 30 19,966 20 20,240 25 18,602 2,370 2,815 3,063 2,244 2,471 1,937 2,277 2,290 3126 1,631 1,422 2349 2,471 3,144 1,499 1058 2,123 2,832 2,573 2,047 3067 2,640 2,731 1,628 2,153 2,670 1,489 2,079 2488 2915 3,212 1369 4 1 0 3 4 41 4 4 0 2 4 4 3 2 4 4 4 2 3 4 4 3 1 5 3 4 0 2 0 4302 5894 5894 4308 4303 5894 4308 4308 6007 4308 4509 6007 4372 4509 4308 6007 4509 4312 4372 4302 6008 4312 4509 4509 4302 4370 4302 4302 6008 6005 4509 6006 P Q R Department Days Comment Bonus Absent Bonus T IF AND OR etc. U IF AND OR etc. V W X IF AND IF AND OR IF AND OR OR etc. etc. etc. Y Z - AA X D Question 1 If the Dept is 6007, enter 900, if the Department is 4305, enter 600, if the Department is 4312, enter 350, otherwise enter 200. Copy the function down for the employees. Calculate the total Department Bonus for all the employees in cell P1. What is this amount? Format as comma style, 0 decimals Question 2 Use the VLOOKUP function to enter the appropriate Bonus value for each employee based on the Days Absent. Calculate the total bonus for all the employees. What is this amount? Format as comma style 0 decimal. Question 3 Use VLOOKUP to insert the appropriate comment for each employee. How many employees have the Good comment? Question 4 2 pts If the Rating is A, B or D and the Department is 4302, 4303 or 6007, and the Units Rejected is less than 2500, enter 1000, otherwise enter O. What is the total? Format as comma style, 0 decimals. 5 pts 5 pts 5 pts Tir Att 71 Department Bonus If the Dept is 6007, enter 900, if it is 4305, enter 600, if it is 4312, enter 350, otherwise enter 200. Copy the function down for the employees. Calculate the total Department Bonus in cell Q1. OR IF AND etc. -nt Days Absent Bonus Use the VLOOKUP function to enter the appropriate Bonus value for each employee based on R etc. the Days Absent. Calculate the total bonus for all the employees in R1. AND IF Comment Use VLOOKUP to insert the appropriate comment for each employee. How many employees have the Good comment? IF AND OR etc. If the Rating is A, B or D and the Department is 4302, 4303 or 6007, and the Units Rejected is less than 2500, enter 1000, otherwise enter 0. What is the total? Calculate the total in T1. D . IF AND OR etc. If the Units Made is 17000 or more IOR the Units Rejected is 2000 or less AND the Rating is A or B enter 1000, otherwise enter 0. What is the total? Calculate the total in U1. D CC. IF AND IF AND OR etc. If the 3rd character of the ID I# is a 3, 5 or 8 AND the Rating is A, B, C or D enter 750, otherwise enter 0. Calculate the total in V1. D CC. IF AND IF AND OR etc. If the 3rd character of the ID I# is a 3, 5 or 8 AND the Rating is A, B, C or D enter 750, otherwise enter 0. Calculate the total in V1. D IF AND OR C. etc. If the ID ends with a 2, 4, 6, or 8 and the Days Absent is 2 or less, enter 500, otherwise the cell should be blank. What is the total? Calculate the total in W1. RIF AND OR etc. If the Department begins with 43 or 60 and the Units Made 19000 or more, enter Yes, otherwise enter No. How many Yes are there? A 1 Employee Data 2 3 4 5 6 7 8 9 LO 11 12 13 B Employee Last Name ID 13816 Abbar 13832 Abercrombie 10774 Ackerman 11737 Adell 14470 Akers 10598 Akhtar 11194 Alboucq 11631 Alexander 14548 Alvardo D First Date of Name Hire E Date of Birth Anas Kim Pilar Jeff 24-Jun-85 16-Oct-58 25-Mar-85 28-Jan-55 26-Apr-89 5-Oct-50 1-Feb-91 21-Dec-64 Kim 29-May-79 8-Apr-58 Sarah 15-Oct-89 22-Apr-70 Steve 12-Jan-92 28-May-64 Sean 6-Jul-00 16-Oct-62 Marion 21-Feb-05 26-Mar-76 F Age G Gender 61.49 F 65.21 F 69.52 F 55.30 F 62.01 F 49.96 F 55.87 F 57.48 M 44.03 F $ $ $ $ $ $ $ $ $ H Salary 1 J 91,000 B 50,000 B 31,000 C 48,000 E 47,000 D 90,000 D 100,000 E 29,000 A 53,000 A Rating Years of Vacation Service Days K 35.6 35.9 31.8 30.0 41.7 31.3 29.1 20.6 16.0 L 30 16,309 30 19,089 30 20,643 30 15,523 30 17,939 30 13,602 25 15,728 M 20 16,309 15 14,851 Units Units Days Department Made Rejected Absent N 2,370 2,815 3,063 2,244 2,471 1,937 2,277 2,290 3126 O 1 4 1 3 0 3 4 4 4 4302 5894 5894 4308 4303 5894 4308 4308 6007 P 0 R Department Days Bonus Absent Bonus S Comment T IF AND OR etc. U IF AND OR etc. V W X IF AND IF AND OR IF AND OR OR etc. etc. etc. Y Z 4 5 6 BUS308-Quiz1 (1) A B Employee Last Name ID 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 44 13816 Abbar 13832 Abercrombie 10774 Ackerman 11737 Adell 14470 Akers 10598 Akhtar 11194 Alboucq 11631 Alexander 14548 Alvardo 14957 Anderson 12911 Anderson 14048 Ansari 13827 Atkinson 14765 Bacon Jr. 13250 Baldwin 14923 Balke 14675 Barbariol 10827 Barnhill 14584 Barr 10149 Barrett 11889 Bartkowski 14347 Beck 12005 Ben-Sachar 10069 Benson 12061 Berge 12833 Berglund 12037 Bergman 10940 Berry 13232 Blakeman 14038 Blose 11061 Bockman 13700 Bogdan Employee Data C First Name D Date of Hire Anas Kim Pilar Jeff Kim Sarah Steve Sean Marion E Date of Birth 24-Jun-85 16-Oct-58 25-Mar-85 28-Jan-55 26-Apr-89 5-Oct-50 1-Feb-91 21-Dec-64 29-May-79 8-Apr-58 15-Oct-89 22-Apr-70 12-Jan-92 28-May-64 6-Jul-00 16-Oct-62 21-Feb-05 26-Mar-76 11-Apr-92 1-Nov-70 6-Jun-06 20-Jun-76 27-Dec-87 26-Sep-51 6-Apr-88 14-Jan-61 22-Jun-74 15-May-49 21-Apr-05 24-Nov-77 21-Oct-04 15-Dec-76 16-Feb-96 18-Apr-64 26-Jul-77 12-Oct-54 9-Jul-93 8-Apr-59 14-Mar-95 30-Jan-70 13-Jul-84 28-Feb-54 20-Dec-98 5-Feb-58 21-Sep-93 12-Jul-56 Max 22-Nov-99 2-Sep-75 Karen 1-Jun-97 27-Feb-65 Andreas 19-Mar-88 21-Oct-54 Ken 17-Mar-01 28-Jan-74 Jo 20-Sep-00 14-Jun-66 Synthia 1-Feb-91 21-Dec-64 Cleotilde 11-Sep-87 27-May-56 Anne 20-Aug-98 18-Nov-51 Deadra 27-Mar-91 20-Jul-64 Amy Charlie Zachary Teresa Dan Amie Wendy Angela Josh Adam Holly Joanna Bradley Ido F G Age Gender 61.49 F 65.21 F 69.52 F 55.30 F 62.01 F 49.96 F 55.87 F 57.48 M 44.03 F 49.44 F 43.80 M 68.55 M 59.24 F 70.92 M 42.37 F 43.31 F 55.98 F 65.50 M 61.01 M 50.19 F 66.12 F 62.18 M 63.75 F 44.60 M 55.12 F 65.48 M 46.19 M 53.82 F 55.30 F 63.88 F 68.40 F 55.72 F $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 33,000 70,000 77,000 D $ $ $ $ A GA GA $ H Salary Rating Years of Vacation Units Service E 91,000 50,000 31,000 48,000 47,000 D 90,000 D 100,000 F 29,000 A 53,000 A 59,000 A 48,000 E 89,000 E 55,000 A 62,000 100,000 82,000 92,000 83,000 E 66,000 A 65,000 A D C E D D A $ B B C olohulalahulolololo 97,000 45,000 F 93,000 A D 58,000 A 76,000 E 48,000 A 41,000 $ 43,000 $ D A 99,000 A 35.6 35.9 31.8 30.0 41.7 31.3 29.1 20.6 16.0 28.8 14.7 33.1 32.8 46.6 15.8 16.3 25.0 43.6 27.6 25.9 36.6 22.1 27.4 21.2 23.7 32.9 19.9 20.4 30.0 33.4 22.5 29.9 Days Made Rejected Absent 30 16,309 30 19,089 30 20,643 30 15,523 30 17,939 30 13,602 25 15,728 20 16,309 15 14,851 25 12,691 10 11,382 30 15,686 30 16,941 30 21,144 15 10,867 15 15,058 20 15,767 30 19,195 25 17,579 25 12,963 30 19,849 20 18,000 25 18,565 20 11,670 20 15,456 30 19,186 M N O Units Days Department 15 10,803 20 14,991 30 20,569 30 19,966 20 20,240 25 18,602 2,370 2,815 3,063 2,244 2,471 1,937 2,277 2,290 3126 1,631 1,422 2349 2,471 3,144 1,499 1058 2,123 2,832 2,573 2,047 3067 2,640 2,731 1,628 2,153 2,670 1,489 2,079 2488 2915 3,212 1369 4 1 0 3 4 41 4 4 0 2 4 4 3 2 4 4 4 2 3 4 4 3 1 5 3 4 0 2 0 4302 5894 5894 4308 4303 5894 4308 4308 6007 4308 4509 6007 4372 4509 4308 6007 4509 4312 4372 4302 6008 4312 4509 4509 4302 4370 4302 4302 6008 6005 4509 6006 P Q R Department Days Comment Bonus Absent Bonus T IF AND OR etc. U IF AND OR etc. V W X IF AND IF AND OR IF AND OR OR etc. etc. etc. Y Z - AA X D Question 1 If the Dept is 6007, enter 900, if the Department is 4305, enter 600, if the Department is 4312, enter 350, otherwise enter 200. Copy the function down for the employees. Calculate the total Department Bonus for all the employees in cell P1. What is this amount? Format as comma style, 0 decimals Question 2 Use the VLOOKUP function to enter the appropriate Bonus value for each employee based on the Days Absent. Calculate the total bonus for all the employees. What is this amount? Format as comma style 0 decimal. Question 3 Use VLOOKUP to insert the appropriate comment for each employee. How many employees have the Good comment? Question 4 2 pts If the Rating is A, B or D and the Department is 4302, 4303 or 6007, and the Units Rejected is less than 2500, enter 1000, otherwise enter O. What is the total? Format as comma style, 0 decimals. 5 pts 5 pts 5 pts Tir Att 71