In cell W15: Use the appropriate nested functions: If the employee's Year of Hire is 1974...

   

Transcribed Image Text:

In cell W15: Use the appropriate nested functions: If the employee's Year of Hire is 1974 or 1977 or 1987, and the Region is RG2 or RG4, and the Gender is Male, 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. 1 3 4 5 6 7 8 9 10 11 123 B C D E F G H J K L M N Management Annual Units Bonus Rating Bonus Bonus Award? Comment Sold Rate 1 Review Needed 100 0 270 No Award 2 Monitor 200 16,500 950 Certificate 0.75% 1.50% $ 3,950 $ 18.00 $ 4,650 3 Fair 500 18,000 1,400 Bronze 3.25% $ 5,100 4 Good 750 5 Excellent 1,000 19,500 1,800 22,000 2,500 Gold Platinum 4.50% 6.00% $ 3,500 Q Department Supervisor 1202 Laing 1211 Zheng 1225 Ahmed 1237 Enkomo 1249 Da Silva 1255 Borrows Answers Units Sold Units Sold Units Sold Units Sold Employee ID Last Name First Name Region Department Job Title Rating Year of Hire Age Salary Gender 15600022222222222 14 Qtr 1 Qtr 2 Qtr 3 Qtr 4 Annual Units Sold 10014766 Miller Geof RG3 1225 Junior Sales Rep 2 1998 61 $ 32,118 M 3,229 3,499 3,884 4,973 15,585 11134120 Lee Mark RG2 1237 Manager 4 1989 59 $ 136,465 M 5,683 5,693 5,113 4,506 20,995 17 10062555 Morgan Louise RG3 1237 Advisor 4 1990 65 $ 86,624 F 4,011 4,109 4,491 5,641 18,252 18 10752656 Ralls Kim RG1 1211 Sales Rep 5 1997 52 $ 50,657 F 5,460 5,495 4,854 3,965 19,774 19 10008446 Matey Michael RG1 1211 Junior Sales Rep 1 1991 53 $ 34,572 M 3,601 3,643 3,450 5,397 16,091 11064518 Young Jonathan RG1 1249 Manager 5 1988 58 $ 97,983 M 5,140 5,194 4,469 4,888 19,691 12371307 Alboucq Steve RG3 1211 Junior Sales Rep 1 1992 50 $ 34,706 M 3,940 4,213 4,557 5,781 18,491 11681945 Tratt Adam RG3 1202 Manager 3 1987 66 $ 126,164 M 3,852 3,864 6,052 6,534 20,302 23 10966149 Matthews Joseph RG3 1225 Junior Sales Rep 3 1990 46 $ 37,294 M 5,786 5,984 5,865 5,315 22,950 24 10763212 Dunn Micheal RG2 1225 Junior Sales Rep 1 1999 62 $ 35,401 M 5,851 6,059 4,261 6,554 22,725 11922536 Groncki Douglas RG4 1249 Junior Sales Rep 1 1986 55 $ 36,323 M 5,370 5,535 4,785 4,180 19,870 10952776 Hohman Bob RG1 1202 Junior Sales Rep 3 1993 66 $ 33,816 M 4,124 4,145 5,855 6,564 20,688 27 11235308 Vernhes Laurent RG4 1255 Sales Rep 5 1985 53 $ 44,760 M 3,668 3,682 5,551 5,641 18,542 28 11646365 Bradley David M RG2 1255 Sales Rep 3 1984 63 $ 74,578 M 4,546 4,582 5,704 5,897 20,729 29 12103860 Tiano Mike RG3 1237 Sales Rep 4 1986 59 $ 54,040 M 5,311 5,311 5,794 5,924 22,340 30 10891724 Henshaw Jeff RG4 1237 Junior Sales Rep 2 1999 65 $ 37,745 M 4,711 4,895 6,060 5,529 21,195 31 12075206 Poland Carole RG4 1237 Junior Sales Rep 4 1996 43 $ 34,211 F 4,877 4,933 5,848 4,943 20,601 32 11019095 Pardi William RG3 1255 Sales Rep 3 1981 66 $ 44,256 M 4,427 4,427 4,397 6,459 19,710 In cell W15: Use the appropriate nested functions: If the employee's Year of Hire is 1974 or 1977 or 1987, and the Region is RG2 or RG4, and the Gender is Male, 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. 1 3 4 5 6 7 8 9 10 11 123 B C D E F G H J K L M N Management Annual Units Bonus Rating Bonus Bonus Award? Comment Sold Rate 1 Review Needed 100 0 270 No Award 2 Monitor 200 16,500 950 Certificate 0.75% 1.50% $ 3,950 $ 18.00 $ 4,650 3 Fair 500 18,000 1,400 Bronze 3.25% $ 5,100 4 Good 750 5 Excellent 1,000 19,500 1,800 22,000 2,500 Gold Platinum 4.50% 6.00% $ 3,500 Q Department Supervisor 1202 Laing 1211 Zheng 1225 Ahmed 1237 Enkomo 1249 Da Silva 1255 Borrows Answers Units Sold Units Sold Units Sold Units Sold Employee ID Last Name First Name Region Department Job Title Rating Year of Hire Age Salary Gender 15600022222222222 14 Qtr 1 Qtr 2 Qtr 3 Qtr 4 Annual Units Sold 10014766 Miller Geof RG3 1225 Junior Sales Rep 2 1998 61 $ 32,118 M 3,229 3,499 3,884 4,973 15,585 11134120 Lee Mark RG2 1237 Manager 4 1989 59 $ 136,465 M 5,683 5,693 5,113 4,506 20,995 17 10062555 Morgan Louise RG3 1237 Advisor 4 1990 65 $ 86,624 F 4,011 4,109 4,491 5,641 18,252 18 10752656 Ralls Kim RG1 1211 Sales Rep 5 1997 52 $ 50,657 F 5,460 5,495 4,854 3,965 19,774 19 10008446 Matey Michael RG1 1211 Junior Sales Rep 1 1991 53 $ 34,572 M 3,601 3,643 3,450 5,397 16,091 11064518 Young Jonathan RG1 1249 Manager 5 1988 58 $ 97,983 M 5,140 5,194 4,469 4,888 19,691 12371307 Alboucq Steve RG3 1211 Junior Sales Rep 1 1992 50 $ 34,706 M 3,940 4,213 4,557 5,781 18,491 11681945 Tratt Adam RG3 1202 Manager 3 1987 66 $ 126,164 M 3,852 3,864 6,052 6,534 20,302 23 10966149 Matthews Joseph RG3 1225 Junior Sales Rep 3 1990 46 $ 37,294 M 5,786 5,984 5,865 5,315 22,950 24 10763212 Dunn Micheal RG2 1225 Junior Sales Rep 1 1999 62 $ 35,401 M 5,851 6,059 4,261 6,554 22,725 11922536 Groncki Douglas RG4 1249 Junior Sales Rep 1 1986 55 $ 36,323 M 5,370 5,535 4,785 4,180 19,870 10952776 Hohman Bob RG1 1202 Junior Sales Rep 3 1993 66 $ 33,816 M 4,124 4,145 5,855 6,564 20,688 27 11235308 Vernhes Laurent RG4 1255 Sales Rep 5 1985 53 $ 44,760 M 3,668 3,682 5,551 5,641 18,542 28 11646365 Bradley David M RG2 1255 Sales Rep 3 1984 63 $ 74,578 M 4,546 4,582 5,704 5,897 20,729 29 12103860 Tiano Mike RG3 1237 Sales Rep 4 1986 59 $ 54,040 M 5,311 5,311 5,794 5,924 22,340 30 10891724 Henshaw Jeff RG4 1237 Junior Sales Rep 2 1999 65 $ 37,745 M 4,711 4,895 6,060 5,529 21,195 31 12075206 Poland Carole RG4 1237 Junior Sales Rep 4 1996 43 $ 34,211 F 4,877 4,933 5,848 4,943 20,601 32 11019095 Pardi William RG3 1255 Sales Rep 3 1981 66 $ 44,256 M 4,427 4,427 4,397 6,459 19,710