In V15:

Using the appropriate nested functions to complete the following: If the seventh character in the employee ID is 0 or 2 or 3 or 4 or 5, insert the employee's Region, otherwise the cell should be blank. Copy the function down to the other cells in the column.

In cell V10 use the appropriate function to calculate how many times Region RG3 show up in the results.

In cell W15: Use the appropriate nested functions: If the employee's Department is 1211 or 1237 or 1249, and the Job Title is Junior Sales Rep or Sales Rep, and the Salary is less than 50,000, multiply the Annual Units Sold by the price in cell N2, otherwise enter a 0 in the cell. (Use absolute cell referencing where necessary). Copy the function down to the other cells in the column.

In W10 use the appropriate function to calculate the total for all the employees. Format comma style 0 decimal.

In Z15 use the VLOOKUP function to select the bonus rate value based on the Annual Units Sold and the appropriate VLOOKUP table located at the top of the worksheet. Use this bonus rate to calculate the bonus by multiplying the bonus rate by the Salary. Copy the formula down to the rest of the cells in the column.

In Z10 use the appropriate function to calculate how many employees got a bonus of less than $2,000.

In AB10 use the AVERAGEIFS function to calculate the average Annual Units Sold for employees with a Annual Units Sold of less than 21,000 with the Job Title Advisor, who are in Region RG3, who are female who are younger than 65. Format answer as comma style, 0 decimal.

R D F G 1 K 1 n P T Borus Rats Department Boru Ratu 1 2 3 Stunt Name Rating 1 2 3 4 5 Management Comment Review Neoced Maonlus Fr Good Excelen Bonus 109 200 300 730 1,000 H Annual Units Sold D 16,600 18,000 13,30 22,000 Bonus 270 960 1,400 1. 2.500 Awurd? No mard Chat BOR Gold Fianum Region RG1 RG2 0.756 1.GO 3.258 4.50 50 $ 3,950 19.00 $ $ 4,850 E ,100 $ $ $ J,500 Supervisor Lang Zheng Ahmed E- na Borrow Bonus 1.200 900 1,000 1,100 80 1,000 1212 1211 12 12 17B 1266 2. 2. 2. ROS RC4 6 2 8 9 AIWW 10 11 12 13 Employee ID Department Gender Annual Unts Sold Enter Functid Units Sold * 4,979 4.5.2 3.61 2,980 5,397 4,8523 F F - Pica RE) M 6.534 6,315 6.554 14 15 10 17 18 19 * 20 31 22 23 24 35 20 27 28 391 10 31 22 33 14 35 26 37 6.534 24,578 Last Name First Name Region MA Geel RCS LAH Www 1:2 Morgan Loure RG3 Rals RC1 Mccoy Michael 21 Young RT PA AL Stave G3 Tratt Adam Mathew Losep RC3 Dan NH FO2 Gmci 4 Hohman BOD RG 1 Vemhog Lauront R24 Bradley Dwi M RC2 Tinn Nik 3 1 Henshaw Jelf REZ G4 Poland Cardio RE4 Parch MIM ROOS 10: Coin Fat RG1 Bambu Josh | Eanaar.kri| FasAITH ROS Bulu Hrul 10:4 Boris 011 RG2 Frice cir Re1 Anderson Any R21 Ev. RCS Good and Derud RGJ Lugo -090 RE3 Mughal Samen R24 SHE y FOL. 1 C achard G3 Votava Michele RGA Perry Dweld R24 T. Puu | | 103147BE 11134921 10062550 107G2GB 10003446 1108451a 1237 1307 11601946 10865-40 1071212 1192 109627TG 11235908 11843 1210 MIN 10091724 12075206 11010DE 12112817 12003620 1103583 11814450 10175 10205111 1026739 1204256 1158M 12257500 1166483 11834654 121438 1051 12003931 11193601 11n M M F Job Title Rating Year of Hire Age Salary Junior Saks For 2 1993 61 $ 32,118 Margar MAA 4 TE 19410 543 138,45 Astaiso 4 1940 65 S Sales Rep 5 1997 G25 30.567 Junior Suks 1 e 1 1991 53 5 34,572 Mer 5 15835 58$ 97,5 Junior Sales 1 1 1982 50 $ wo 3.700 Manager 3 Conditional Formatting 126.164 Junior Saks 3 1990 453 37,214 Junior Shop 1 15901 e2 $ 35,401 Junior Sales o 1 10 35 5 WS Junior Sales Rep 3 1993 GOS 33.315 Saks Rep 5 1695 1985 633 44,FED 14.ro Sale For 3 3 1084 es $ les lo + JH $ 54.00 Junior Sales Rep 2 1999 G5 S 37.745 Junior Saks R41 1993 43S 34,211 Balus FD 3 1981 es 44,256 . 2000 as $ 95 Manager 4 19091 4B 5 120.875 Manager 4 1977 62 S 117.972 Addiad 1 1994 67 $ 64,223 Marger 4 1980 $ 105,511 Junior Sales 2 2000 33 5 34.400 Manggar 3 1999 40 S 124.63 Saks RAD - 4 1942 Te 44 $ 70,621 Audio 4 17 e2 $ Manager 5 1913 58 $ 77.721 Manager 1 1995 GOS 130.784 Junior Suks 3 1999 42 31,207 Page 4 1687 99 $ 82,65 Junior Roles 2 Z 1948 31 $ 51 31.122 1 Manager . 4 1991 GDS 122.214 Adelaar 2 1975 ) GOS 73,537 1 11 7 111 . 1225 12 1207 1211 1211 1249 1211 1202 1225 1225 170 1202 1265 1255 17217 1207 1237 1255 1211 1202 1211 1202 12112 1225 1225 1297 1225 1225 1265 1249 1225 1255 1225 1211 1766 Units Bold Units Sold Qir1 Or 2 3.228 SER 5. 4.011 4.100 5.462 3.601 2,649 5140 5,194 sens 3940 4.213 3.862 2.9:24 5.785 6,984 5851 e, 150 5310 3,515 4, 124 4,145 3.663 2,652 4.522 5311 3,311 4.711 4.096 4,877 4.933 4.427 SH 31,7 4.031 4,037 3.641 3,750 5.243 5.271 4.05 4,He 5. 120 5.200 3.761 3,781 4,833 4,873 8.991 3,404 4473 4,850 3.403 2,504 3.275 2,387 550 5,500 4731 3.995 4,050 4. 174 4,256 102 Units Sold diru 3884 5113 &181 4864 3.463 4.4HD 4.557 5.062 5.865 4 2H1 4.7815 5.865 5.561 5704 5741 5.060 5 843 4 297 S5 5.075 5.131 5.673 4.13 5. STO 4465 3.865 S. 5 2014 5.242 5.863 5.081 LEH 5.727 4.145 4.673 MAT 15,56 20,8 18.252 19,774 16,091 19,HD 18.40 20,302 22,950 22,725 19.8711 20.650 18,542 20,722 23,310 21.138 20,60 19,710 17,87 18.15 17,928 22,150 19,42 21.026 16,250 17,467 10,878 20.412 18,436 17,547 Jare F F M F ROS 5,807 3.97 0.529 4,943 6,450 4,374 3,436 4,50 5,975 5,854 3.076 4,273 4,100 4,245 3,1144 6,636 6,824 6,178 4,29 G47 6,447 6,074 con M M F F M M 99 40 41 12 43 44 45 16 47 48 F M W F F 17,878 18,637 18,176 1111 | T 1 X AA AC Supervisor Borus Region Bonus Route Bonus Bonus Bonus Rate 1 3 Department 1202 1211 1225 1237 1743 1255 IN hen Ahmed Enero The Sims BOME 1.2001 300 1.00 1,100 Real 1.000 RGA 1 RGZ R33 P04 200 2 25 2 3G 1.95% 1 100 1,300 1,175 1,200 4 Job Title Now or Junior Sales Rep NH Step Jllan Nur Jian 1.000 400 1.500 110 2.00% % 3.MN 2 5 G 7 8 9 10 11 12 13 Answers Column X de Rotuned the Term 802 089983 2088 DO NOT DELETE DO NOT DELETE Column AA an! and the Term 1:888888888 DO NOT DELETE Column AAN used this Tern Column X and Ye not used this Tem Unised Gr4 Annual Units Sok Enter Functions below according to Test instructions 4.973 4.506 Conditional Formatting 14 15 10 17 12 19 20 2 21 32 23 24 35 3G 27 ya 22 29 10 31 22 33 34 19 30 34 30 99 40 41 42 12 44 45 40 ? 47 48 3.865 5.30 4,598 5.7211 5.534 5.315 1.564 4.180 6.360 5.841 5.347 5.104 5.52 4.943 6.469 4,374 5.106 4.301 5.975 5.684 5.76 4.273 4.100 4,245 S.CH 5.25 5.544 A.B7B 4,2017 6.44 6.074 RA 15.686 20,585 2 18722 19.774 16.091 19.521 1H 481 20.02 22 SGD 22,725 19,6701 20.000 18.542 20,729 22,51 21.185 20.301 19.710 17,27 18.183 17.623 22.169 19,426 71.DK 16.260 17.461 18,679 21141 10.4.35 17.147 17,67H 10.A 18.175 1A AT R D F G 1 K 1 n P T Borus Rats Department Boru Ratu 1 2 3 Stunt Name Rating 1 2 3 4 5 Management Comment Review Neoced Maonlus Fr Good Excelen Bonus 109 200 300 730 1,000 H Annual Units Sold D 16,600 18,000 13,30 22,000 Bonus 270 960 1,400 1. 2.500 Awurd? No mard Chat BOR Gold Fianum Region RG1 RG2 0.756 1.GO 3.258 4.50 50 $ 3,950 19.00 $ $ 4,850 E ,100 $ $ $ J,500 Supervisor Lang Zheng Ahmed E- na Borrow Bonus 1.200 900 1,000 1,100 80 1,000 1212 1211 12 12 17B 1266 2. 2. 2. ROS RC4 6 2 8 9 AIWW 10 11 12 13 Employee ID Department Gender Annual Unts Sold Enter Functid Units Sold * 4,979 4.5.2 3.61 2,980 5,397 4,8523 F F - Pica RE) M 6.534 6,315 6.554 14 15 10 17 18 19 * 20 31 22 23 24 35 20 27 28 391 10 31 22 33 14 35 26 37 6.534 24,578 Last Name First Name Region MA Geel RCS LAH Www 1:2 Morgan Loure RG3 Rals RC1 Mccoy Michael 21 Young RT PA AL Stave G3 Tratt Adam Mathew Losep RC3 Dan NH FO2 Gmci 4 Hohman BOD RG 1 Vemhog Lauront R24 Bradley Dwi M RC2 Tinn Nik 3 1 Henshaw Jelf REZ G4 Poland Cardio RE4 Parch MIM ROOS 10: Coin Fat RG1 Bambu Josh | Eanaar.kri| FasAITH ROS Bulu Hrul 10:4 Boris 011 RG2 Frice cir Re1 Anderson Any R21 Ev. RCS Good and Derud RGJ Lugo -090 RE3 Mughal Samen R24 SHE y FOL. 1 C achard G3 Votava Michele RGA Perry Dweld R24 T. Puu | | 103147BE 11134921 10062550 107G2GB 10003446 1108451a 1237 1307 11601946 10865-40 1071212 1192 109627TG 11235908 11843 1210 MIN 10091724 12075206 11010DE 12112817 12003620 1103583 11814450 10175 10205111 1026739 1204256 1158M 12257500 1166483 11834654 121438 1051 12003931 11193601 11n M M F Job Title Rating Year of Hire Age Salary Junior Saks For 2 1993 61 $ 32,118 Margar MAA 4 TE 19410 543 138,45 Astaiso 4 1940 65 S Sales Rep 5 1997 G25 30.567 Junior Suks 1 e 1 1991 53 5 34,572 Mer 5 15835 58$ 97,5 Junior Sales 1 1 1982 50 $ wo 3.700 Manager 3 Conditional Formatting 126.164 Junior Saks 3 1990 453 37,214 Junior Shop 1 15901 e2 $ 35,401 Junior Sales o 1 10 35 5 WS Junior Sales Rep 3 1993 GOS 33.315 Saks Rep 5 1695 1985 633 44,FED 14.ro Sale For 3 3 1084 es $ les lo + JH $ 54.00 Junior Sales Rep 2 1999 G5 S 37.745 Junior Saks R41 1993 43S 34,211 Balus FD 3 1981 es 44,256 . 2000 as $ 95 Manager 4 19091 4B 5 120.875 Manager 4 1977 62 S 117.972 Addiad 1 1994 67 $ 64,223 Marger 4 1980 $ 105,511 Junior Sales 2 2000 33 5 34.400 Manggar 3 1999 40 S 124.63 Saks RAD - 4 1942 Te 44 $ 70,621 Audio 4 17 e2 $ Manager 5 1913 58 $ 77.721 Manager 1 1995 GOS 130.784 Junior Suks 3 1999 42 31,207 Page 4 1687 99 $ 82,65 Junior Roles 2 Z 1948 31 $ 51 31.122 1 Manager . 4 1991 GDS 122.214 Adelaar 2 1975 ) GOS 73,537 1 11 7 111 . 1225 12 1207 1211 1211 1249 1211 1202 1225 1225 170 1202 1265 1255 17217 1207 1237 1255 1211 1202 1211 1202 12112 1225 1225 1297 1225 1225 1265 1249 1225 1255 1225 1211 1766 Units Bold Units Sold Qir1 Or 2 3.228 SER 5. 4.011 4.100 5.462 3.601 2,649 5140 5,194 sens 3940 4.213 3.862 2.9:24 5.785 6,984 5851 e, 150 5310 3,515 4, 124 4,145 3.663 2,652 4.522 5311 3,311 4.711 4.096 4,877 4.933 4.427 SH 31,7 4.031 4,037 3.641 3,750 5.243 5.271 4.05 4,He 5. 120 5.200 3.761 3,781 4,833 4,873 8.991 3,404 4473 4,850 3.403 2,504 3.275 2,387 550 5,500 4731 3.995 4,050 4. 174 4,256 102 Units Sold diru 3884 5113 &181 4864 3.463 4.4HD 4.557 5.062 5.865 4 2H1 4.7815 5.865 5.561 5704 5741 5.060 5 843 4 297 S5 5.075 5.131 5.673 4.13 5. STO 4465 3.865 S. 5 2014 5.242 5.863 5.081 LEH 5.727 4.145 4.673 MAT 15,56 20,8 18.252 19,774 16,091 19,HD 18.40 20,302 22,950 22,725 19.8711 20.650 18,542 20,722 23,310 21.138 20,60 19,710 17,87 18.15 17,928 22,150 19,42 21.026 16,250 17,467 10,878 20.412 18,436 17,547 Jare F F M F ROS 5,807 3.97 0.529 4,943 6,450 4,374 3,436 4,50 5,975 5,854 3.076 4,273 4,100 4,245 3,1144 6,636 6,824 6,178 4,29 G47 6,447 6,074 con M M F F M M 99 40 41 12 43 44 45 16 47 48 F M W F F 17,878 18,637 18,176 1111 | T 1 X AA AC Supervisor Borus Region Bonus Route Bonus Bonus Bonus Rate 1 3 Department 1202 1211 1225 1237 1743 1255 IN hen Ahmed Enero The Sims BOME 1.2001 300 1.00 1,100 Real 1.000 RGA 1 RGZ R33 P04 200 2 25 2 3G 1.95% 1 100 1,300 1,175 1,200 4 Job Title Now or Junior Sales Rep NH Step Jllan Nur Jian 1.000 400 1.500 110 2.00% % 3.MN 2 5 G 7 8 9 10 11 12 13 Answers Column X de Rotuned the Term 802 089983 2088 DO NOT DELETE DO NOT DELETE Column AA an! and the Term 1:888888888 DO NOT DELETE Column AAN used this Tern Column X and Ye not used this Tem Unised Gr4 Annual Units Sok Enter Functions below according to Test instructions 4.973 4.506 Conditional Formatting 14 15 10 17 12 19 20 2 21 32 23 24 35 3G 27 ya 22 29 10 31 22 33 34 19 30 34 30 99 40 41 42 12 44 45 40 ? 47 48 3.865 5.30 4,598 5.7211 5.534 5.315 1.564 4.180 6.360 5.841 5.347 5.104 5.52 4.943 6.469 4,374 5.106 4.301 5.975 5.684 5.76 4.273 4.100 4,245 S.CH 5.25 5.544 A.B7B 4,2017 6.44 6.074 RA 15.686 20,585 2 18722 19.774 16.091 19.521 1H 481 20.02 22 SGD 22,725 19,6701 20.000 18.542 20,729 22,51 21.185 20.301 19.710 17,27 18.183 17.623 22.169 19,426 71.DK 16.260 17.461 18,679 21141 10.4.35 17.147 17,67H 10.A 18.175 1A AT