In cell T17 use the IF function to enter 800 if DEPARTMENT IS D otherwise enter 500. Copy the function to the other cells in the column. In cell T14 use the appropriate function to calculate the total for all employees. Format as comme style O decimal 41 HUREN 2150 LGH04 Monthly on Bates Bate on Rame Rae Wed Deduct Deductie 3 6 1000 Bonus 15.02 5000 LITO Bows 5 Bonus Rate 1 022 Bil Hate 2 1 2004 7500 5 RE 200% NY OR 1 OA 50 04 D 2 3503 20 255 TO 373 1 50A 15.000 300 1 4 1 33.5 1 42% 400 OVE 5 201000 HEM 03 11 475 wo 4560 400 4152 11 11 TO ES 12 10 TE 0 1 6003 14 4 15 ANIVERSE TITLE DEPARTME LAST FIRST 167 CITY NT NAME NAME PROV POSTAL RATING EDUCATI CILINGU GENE GROSS MONTHI CODE NET ON AL RATING R MONTH Y MONTIRE BONUS 17 Presiden D Snel Jennides Non Yol Y DEDUCT ON Y 10 Adoor Rep A FON MORTVO Dad 101 5 ED LIVES CAO 20.00 365 19 COSManager ON MI Sores HO 4 M 20 ASIOITA Proville ON C ClemerneMAI Thomh NO M ill ON 212Sectropuch A Grer. Margreto NA ON 22 Ses Rep C Gili Oshava M ON LAM 22 TopVO! NO A B an Ogoede ON FBA KO 24 Store Manager To Wine PAD Mino ON EDAD 25 Multing Manage M 12 D HE he Sewborough 9:40 ON IMW 0 100 26 Service Branch Manag D Whe Rh MNR ON PSRS 23 D LO 27 Service anche D Lever MP3 FOE 420 EUR TU 20 Product Manager A OU Mham ON NE TE AR 700 otse Bh Pad AB TE M 50 5evice anch Manag D Eston Share More ON W TKANI 21 WE Hep 054 M 0 Vincent Saved IN MWAM Agingut TE 02 Serie A HA EW ON THE 1 3000 C ON Dou 2 LA 100 34 D WS MNOS Se branch Man YE Reche Lowe 1 Picduong A Thompson Chi Mwili ZON 514 TA 36 TACCO Hep Robbie CA STU A 37 rocher C Ideos M Hoxe 0 THERE 30 Rep MIN NO A [T 33 Active ON NO TEN MR 1311 40 Oranch Man A C ON 41 In IN Worce M G 42 VE Sales Hog ON ME TUM D Lean Harvey 43 per ON MA A Todo VE 31 46 Barva Hegante cachines 45 President ICH Weg TE 40 her M Tos George 151 a la Mager ON OL V Dede 495 4 Sep Edu NO ON bchaden A TON TACOLI One ol Mb MLM HR DATASET 8:33 PM Edit 20C 2021-09-27 C 3775 F MA 11 IG D ans IN IS NET Adx ENG Alignment Number ds Table Cells a. Ar Editing Cell Styles Analyze Data Font 7 Styles Analysis fx L M s N Rating WWW G Gross Monthly $0.00 $ 5.000 $ 7,500 $ 9,000 $ 11.000 $ 15.000 $ 20.000 H K Deducti Deducti Deducti Deductio on Rate on Rate on Rate In Rate 4 20.0" 15.02 10.0 17.5% 25.0%. 20.022 15.0% 21.5%. 30.0% 25.02 20.0% 25.5% 35.0% 30.0 25.0% 29.5% 37.5% 35.0% 30.0% 33.54 42.54 40.04 35.0% 37.54 47.5% 45.07. 40.0% 415% 1 2 3 4 5 6 7 8 9 10 0 Bonus Rate 1 0.50% 0.75% 100% 1.50% 2.00% 2.50%. 3.00% 3.50% 4.50% 5.00% Bonus Rate 2 0.35% 0.55% 0.75% 0.95% 1 15% 135%. 1.554 1759 195% 2.15% Bonus Rate 3 0.45% 0.90% 135%. 1.80% 2.2594 270% 3.15% 3.60% 4.05. 4.50%. R Bonus Rate 4 0.55% 100% 145% 190% 2.35% 2.80% 3.25% 3.70% 4.15% 4.60%. + 11 167 500 367 500 ANSWER3 434 303 CITY PROV RST AME - 4,741 nifer North York id Scarborough old Stouffville 34 Thornhill saret Gormley Oshawa met Osgoode hoe RR #3 Manoti iela Scarborough alph Markham enique Riviere Des Pu Markham Calgary atee Manotick ON ON ON ON ON ON ON ON ON ON ON AB ON CAT POSTAL RATING EDUCATI BILINGU GENDE GROSS MONTHL NET CODE ON AL R MONTHL Y MONTHL Y DEDUCT Y MOR 1VO 10 5 YES F 15.095 6,438 9.657 MIR 2H1 4 4 NO M 4198 630 3.568 L2H162 1 3 NO M 8.781 2. 195 6.588 L3J X3 1 2 NO F 4.550 683 3.868 LGH164 6 2 YES F 4.189 628 3.561 LSR 3M5 9 5 NO F 5.926 1185 IKEA ZW6 2 2 YES F 8,600 2 150 6450 K7A 2N7 7 5 NO M 8.405 2 101 6.300 M8W 3H8 2 5 YES F 12 057 4.220 7.837 3 2 LSP5H3 M NO 6.840 9.770 2931 5 5 F YES 9 812 HOE 420 2.944 6.868 2. LIP SP1 2 NO M 3,280 20.933 4 9 M NO 715 4,767 T2L 1E2 5445 9.207 F 3 2762 YES 8 K3A 2N3 MW M4 4064 217 NO 2 9 M M 6105 1329 TOR RATING EDUCAT DEPARTM BONUS ION ENT BONUS BONUS 15.000 S 5 4 7.500 3 3 2 2 2 2 5.000 5 7500 7500 5 5 11000 5 5 9000 3 9.000 5 5 97000 2 4 4 9000 3 2 5.000 2 5000 7,650 bert anald 4787 456 Hugiment Number Cells H a. A Editing Cell Styles Clipboard Font 2 Styles AH8 fr T AD U Education Bonus 1 AH Bonus 2 Bonus 3 1 2 3 4 5 1 2 3 4 5 5 1 $ 1 200 2 AA AB AC Department Bonus 1 Bonus 2 Bonus 3 A 9 800 $ 500 $ 400 B 3 1 800 600 725 31000 700 B $ 695112003 300 . 1 1 $ 1 AE AF AG Values 1Values Values 3 100 600 STS 500 100 450 450 150 600 375 920 500 350 828 100 $ 200 $ 900 300 600 400 + ZSO 500 1200 300 500 800 1000 7 10 11 12 AVERAGE SUMIFS COUNT MATCH INDEX SUBTOTA FILTER 14 15 IF FUNCTI ON NESTED IF BONUS 17 19 19 20 21 22 23 24 25 26 27 24 23 30 31 32 30 34 5 In cell T17 use the IF function to enter 800 if DEPARTMENT IS D otherwise enter 500. Copy the function to the other cells in the column. In cell T14 use the appropriate function to calculate the total for all employees. Format as comme style O decimal 41 HUREN 2150 LGH04 Monthly on Bates Bate on Rame Rae Wed Deduct Deductie 3 6 1000 Bonus 15.02 5000 LITO Bows 5 Bonus Rate 1 022 Bil Hate 2 1 2004 7500 5 RE 200% NY OR 1 OA 50 04 D 2 3503 20 255 TO 373 1 50A 15.000 300 1 4 1 33.5 1 42% 400 OVE 5 201000 HEM 03 11 475 wo 4560 400 4152 11 11 TO ES 12 10 TE 0 1 6003 14 4 15 ANIVERSE TITLE DEPARTME LAST FIRST 167 CITY NT NAME NAME PROV POSTAL RATING EDUCATI CILINGU GENE GROSS MONTHI CODE NET ON AL RATING R MONTH Y MONTIRE BONUS 17 Presiden D Snel Jennides Non Yol Y DEDUCT ON Y 10 Adoor Rep A FON MORTVO Dad 101 5 ED LIVES CAO 20.00 365 19 COSManager ON MI Sores HO 4 M 20 ASIOITA Proville ON C ClemerneMAI Thomh NO M ill ON 212Sectropuch A Grer. Margreto NA ON 22 Ses Rep C Gili Oshava M ON LAM 22 TopVO! NO A B an Ogoede ON FBA KO 24 Store Manager To Wine PAD Mino ON EDAD 25 Multing Manage M 12 D HE he Sewborough 9:40 ON IMW 0 100 26 Service Branch Manag D Whe Rh MNR ON PSRS 23 D LO 27 Service anche D Lever MP3 FOE 420 EUR TU 20 Product Manager A OU Mham ON NE TE AR 700 otse Bh Pad AB TE M 50 5evice anch Manag D Eston Share More ON W TKANI 21 WE Hep 054 M 0 Vincent Saved IN MWAM Agingut TE 02 Serie A HA EW ON THE 1 3000 C ON Dou 2 LA 100 34 D WS MNOS Se branch Man YE Reche Lowe 1 Picduong A Thompson Chi Mwili ZON 514 TA 36 TACCO Hep Robbie CA STU A 37 rocher C Ideos M Hoxe 0 THERE 30 Rep MIN NO A [T 33 Active ON NO TEN MR 1311 40 Oranch Man A C ON 41 In IN Worce M G 42 VE Sales Hog ON ME TUM D Lean Harvey 43 per ON MA A Todo VE 31 46 Barva Hegante cachines 45 President ICH Weg TE 40 her M Tos George 151 a la Mager ON OL V Dede 495 4 Sep Edu NO ON bchaden A TON TACOLI One ol Mb MLM HR DATASET 8:33 PM Edit 20C 2021-09-27 C 3775 F MA 11 IG D ans IN IS NET Adx ENG Alignment Number ds Table Cells a. Ar Editing Cell Styles Analyze Data Font 7 Styles Analysis fx L M s N Rating WWW G Gross Monthly $0.00 $ 5.000 $ 7,500 $ 9,000 $ 11.000 $ 15.000 $ 20.000 H K Deducti Deducti Deducti Deductio on Rate on Rate on Rate In Rate 4 20.0" 15.02 10.0 17.5% 25.0%. 20.022 15.0% 21.5%. 30.0% 25.02 20.0% 25.5% 35.0% 30.0 25.0% 29.5% 37.5% 35.0% 30.0% 33.54 42.54 40.04 35.0% 37.54 47.5% 45.07. 40.0% 415% 1 2 3 4 5 6 7 8 9 10 0 Bonus Rate 1 0.50% 0.75% 100% 1.50% 2.00% 2.50%. 3.00% 3.50% 4.50% 5.00% Bonus Rate 2 0.35% 0.55% 0.75% 0.95% 1 15% 135%. 1.554 1759 195% 2.15% Bonus Rate 3 0.45% 0.90% 135%. 1.80% 2.2594 270% 3.15% 3.60% 4.05. 4.50%. R Bonus Rate 4 0.55% 100% 145% 190% 2.35% 2.80% 3.25% 3.70% 4.15% 4.60%. + 11 167 500 367 500 ANSWER3 434 303 CITY PROV RST AME - 4,741 nifer North York id Scarborough old Stouffville 34 Thornhill saret Gormley Oshawa met Osgoode hoe RR #3 Manoti iela Scarborough alph Markham enique Riviere Des Pu Markham Calgary atee Manotick ON ON ON ON ON ON ON ON ON ON ON AB ON CAT POSTAL RATING EDUCATI BILINGU GENDE GROSS MONTHL NET CODE ON AL R MONTHL Y MONTHL Y DEDUCT Y MOR 1VO 10 5 YES F 15.095 6,438 9.657 MIR 2H1 4 4 NO M 4198 630 3.568 L2H162 1 3 NO M 8.781 2. 195 6.588 L3J X3 1 2 NO F 4.550 683 3.868 LGH164 6 2 YES F 4.189 628 3.561 LSR 3M5 9 5 NO F 5.926 1185 IKEA ZW6 2 2 YES F 8,600 2 150 6450 K7A 2N7 7 5 NO M 8.405 2 101 6.300 M8W 3H8 2 5 YES F 12 057 4.220 7.837 3 2 LSP5H3 M NO 6.840 9.770 2931 5 5 F YES 9 812 HOE 420 2.944 6.868 2. LIP SP1 2 NO M 3,280 20.933 4 9 M NO 715 4,767 T2L 1E2 5445 9.207 F 3 2762 YES 8 K3A 2N3 MW M4 4064 217 NO 2 9 M M 6105 1329 TOR RATING EDUCAT DEPARTM BONUS ION ENT BONUS BONUS 15.000 S 5 4 7.500 3 3 2 2 2 2 5.000 5 7500 7500 5 5 11000 5 5 9000 3 9.000 5 5 97000 2 4 4 9000 3 2 5.000 2 5000 7,650 bert anald 4787 456 Hugiment Number Cells H a. A Editing Cell Styles Clipboard Font 2 Styles AH8 fr T AD U Education Bonus 1 AH Bonus 2 Bonus 3 1 2 3 4 5 1 2 3 4 5 5 1 $ 1 200 2 AA AB AC Department Bonus 1 Bonus 2 Bonus 3 A 9 800 $ 500 $ 400 B 3 1 800 600 725 31000 700 B $ 695112003 300 . 1 1 $ 1 AE AF AG Values 1Values Values 3 100 600 STS 500 100 450 450 150 600 375 920 500 350 828 100 $ 200 $ 900 300 600 400 + ZSO 500 1200 300 500 800 1000 7 10 11 12 AVERAGE SUMIFS COUNT MATCH INDEX SUBTOTA FILTER 14 15 IF FUNCTI ON NESTED IF BONUS 17 19 19 20 21 22 23 24 25 26 27 24 23 30 31 32 30 34 5