A Moving to another question will save this response. Question 3 Question 3 of 4 points Save In cell 017: Use the VLOOKUP function to return the Deduction Rate 4 from the appropriate table at the top of the worksheet. Multiply this Deduction Rate 4 by the Gross Monthly Salary to calculate the Monthly Deduction. Copy the function down to the other cells in the column. In cell 014 use the appropriate function to answer: What is the total for all employees? Format answer as comma style, O decimal. In cell P17: Calculate the Net Monthly Salary by subtracting the Monthly Deduction from the Gross Monthly Salary. Copy the formula to all cells in the column. In cell P14: use the appropriate function to answer: How many employees have a Net Monthly Salary greater than 7250? Moving to another question will save this response. Question 4 7 3 points Save Answer Question 5 In cell R17: Use the VLOOKUP function to return Education Bonus 3 from the appropriate table at the top of the worksheet. Copy the function down to the other cells in the column. In cell R14 use the appropriate function to answer: How many employees have an Education Bonus greater than 400? Format answer as comma style, O decimal. A Moving to another question will save this response. 13 Question 5 of 15 > Sho A Moving to another question will save this response. Question 6 Question 6 of 15 3 points Save Answ In cell $17: Use the VLOOKUP function to return Department Bonus 1 from the appropriate table at the top of the worksheet. Copy the function down to the other cells in the column. In cell S14 use the appropriate function to answer: What is the total for all the employees? Format answer as comma style, O decimal. Moving to another question will save this response. Question 6 of 15 A Moving to another question will save this response. Question 7 In cell T17 use the IF function to enter 350 if EDUCATION is 3 or greater otherwise enter 75. 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 comma style 0 decimal Moving to another question will save this response. Question 7 2 points S Question 7 Question 8 In U17 use NESTED IF functions to enter: the value 500, if the CITY is Mississauga, the value 350, if the CITY is Winnipeg, the value 485, if the CITY is Richmond Hill, the value 450, if the CITY is Ottawa, otherwise the value 315. Copy the nested functions to the rest of the cells in the column. In U14, use the appropriate function to calculate the total for all employees. Format answer as comma style, O decimal. Moving to another question will save Question 9 3 points Save Answer In V14 use the AVERAGEIFS function to calculate the average Gross Monthly Salary for employees from Winnipeg with Rating of 6 or less and Education of 3 or more and who are not Bilingual. Format answer as comma style, O decimal. Moving to another question will save this response. Question 9 of 15 A Moving to another question will save this response. Question 10 Question 10 of 15 3 points Save Answer In W14 use the SUMIFS function to calculate the total Gross Monthly Salary for Female employees with a Rating of 8 and Education of 3 who work in Department D. Format answer as comma style, O decimal. A Moving to another question will save this response. Question 10 of 15 L A Moving to another question will save this response. Question 12 In cell Y14 use the MATCH Function to answer: What is the Row # (in the range of Gross Monthly Salary data) that 14324 can be found? In cell Z14 use the INDEX Function only to answer: What is the Last Name of the employee with a Gross Monthly Salary of 14324? Moving to another question will save this response. Question 13 In cell AA14 use the SUBTOTAL Function only to calculate the maximum Gross Monthly Salary for all employees Filter the data to show employees with Education of 3 or more from Toronto who are bilingual. The Subtotal value in AA14 should update to show the result for the filtered data. Enter (type) this new Subtotal value into cell AB14 (the value should remain when the filters are cleared). (Format as comma style, O decimal) What is the new Subtotal value? Clear the filters. (Format CD . AEC File Home Insert Page Layout Formulas Data Review View Help Acrobat Tell me what you want to do Cut Arial Narrow 11 A A ab Wrap Text General Copy Paste B I U A Format Painter Merge & Center G Clipboard Font 17 Alignment S 3 %, 0 .00 .0 Number go A1 X fr B C 1 2 Student Name 3 Student ID 4 5 LO 6 7 QUIZ 2 HR DATA FILE(1) (1) - Excel Harshith Nayak HN 1 Normal Bad Conditional Format as Formatting Check Cell Good Explanatory... Input Neutral Calculation AutoSum 4 Linked Cell Note Insert Delete Format Table Fill Clear Styles Cells Sort & Find & Filter Select Editing X E F G H Gross Deduction Deduction Deduction K Deduction M N Rating P Q R S Monthly Rate 1 Rate 2 Rate 3 Rate 4 Bonus Rate 1 Bonus Rate Bonus Rate Bonus Rate 2 U Education V Bonus 1 Z AA AB AC Al Bonus 2 Bonus 3 3 4 Department Bonus 1 Bonus 2 Bonus 3 Salary $0.00 20.0% 15.0% 10.0% 17.5% 1 0.50% 0.35% 0.45% 0.55% 1 $ 150 $ 100 $ $ 5,000 25.0% 20.0% 15.0% 21.5% 2 0.75% 0.55% 0.90% 1.00% 2 $ 300 $ 200 $ 200 400 A $ 800 $ 600 $ 400 B $ 700 $ 800 $ 600 $ 7,500 30.0% 25.0% 20.0% 25.5% 3 1.00% 0.75% 1.35% 1.45% 3 $ 500 $ 300 $ 600 C $ 725 $ 1,000 $ 700 $ 9,000 35.0% 30.0% 25.0% 29.5% 4 1.50% 0.95% 1.80% 1.90% 4 $ 800 $ 400 $ $ 11,000 37.5% 35.0% 30.0% 33.5% 5 2.00% 1.15% 2.25% 2.35% 5 $ 1,000 $ 500 $ 750 1,200 D $ 695 $ 1,200 $ 900 $ 15,000 42.5% 40.0% 35.0% 37.5% 6 2.50% 1.35% 2.70% 2.80% $ 20,000 47.5% 45.0% 40.0% 41.5% 7 3.00% 1.55% 3.15% 3.25% 8 3.50% 1.75% 3.60% 3.70% 9 4.50% 1.95% 4.05% 4.15% 10 5.00% 2.15% 4.50% 4.60% AVERAGEIF SUMIFS COUNTIFS MATCH INDEX SUBTOTAL FILTER 8 00 9 10 11 12 13 14 ANSWERS 15 EMPLOYEE JOB CODE TITLE DEPARTMENT LAST ID NAME FIRST NAME CITY PROV POSTAL CODE RATING EDUCATION BILINGUAL GENDER 16 17 1050 622001 President D Sinclair 18 1051 601710 Account Rep A Farrow Jennifer David North York ON MOR 1V0 10 5 YES F MONTHLY GROSS NET MONTHLY DEDUCTIO MONTHLY SALARY N SALARY 16,095 IF EDUCATIO DEPARTMENT RATING BONUS FUNCTIO N BONUS BONUS N BONUS NESTED IF BONUS Scarborough ON M1R 2H1 4 4 NO M 4,198 19 1052 411301 Credit Manager A Jones Harold Stouffville ON L2H 1G2 1 3 NO M 8,781 20 1053 501111 Associate Sales Rep C Clemente Mary Thornhill ON L3J 1X3 1 21 1054 304000 Secretary Dispatch A Greenwood Margret Gormley ON L4H 1G4 6 22 NO F 4,550 YES F 4,189 22 1055 520201 Sales Rep C Gilles Paula Oshawa ON L5R 3M5 9 5 NO F 5,926 23 1056 601910 Territory Supervisor A Bell Janet Osgoode ON K6A 2W6 2 2 YES F 8,600 24 1057 511111 Store Manager D Taylor Vince RR #3 Manotick ON K7A 2N7 7 5 NO M 8,405 25 1058 411201 Marketing Manager D Harris Shiela Scarborough ON M8W 3H8 2 5 YES F 12,057 26 1059 610201 Service Branch Manager D Whity Ralph Markham ON L9P 5H9 2 3 NO M 9,771 27 1060 610201 Service Branch Manager D Levesque Monique Riviere Des Pra QC HOE 4ZO 5 5 YES F 9,812 28 1061 603100 Product Manager A Dwyer Robert Markham ON L1P 5P1 2 2 NO M 10,933 29 1062 501111 Sales Rep A Brash Ronald Calgary AB T2L 1E2 g 4 NO M 4,767 30 1063 610301 Service Branch Manager Easton Sharee Manotick ON K3A 2N3 8 3 YES F 9,207 Saveda Vincent Agincourt ON M4W 2M4 9 2 NO M 4,781 31 1064 601210 Service Rep 32 1065 604200 Senior Instructor A Hill Albert Scarborough ON M5V 2J5 4 2 NO M 6,145 33 1066 605501 Supervisor Repair C Zoltan John Stouffville ON L6A 7X6 8 2 NO M 6,541 34 1067 610201 Service Branch Manager D Malice Rachel Vancouver BC V7P 257 5 2 YES F 9,206 35 1068 505301 Product Manager A Thompson Charles Markham ON L8P 4R8 7 3 NO M 10,460 36 1069 601710 Account Rep D Robbins C Edwards George Emily Orleans ON K9C 2K9 9 5 NO M 4,096 Kars ON KOA 2E0 8 2 YES F 6,260 37 1070 601510 Tech Specialist A Houle Jean-Paul Verdun QC H1H 2P1 6 2 YES M 4,439 38 1071 601210 Service Rep 39 1072 403100 Assistant Credit Manager A Derry Mary Scarborough ON M2T 3N2 8 2 NO F 6,009 Whalen Ariel Mississauga ON L3Y 2G3 4 3 NO F 10.114 2 YES M 3,911 40 1073 610201 Service Branch Manager Gardner Craig Nepean ON K46 4E4 1.080 NO C 41 1074 601310 Installer AND MES QUE Died 42 4075 602000 + HR DATASET Ready Accessibility: Good to go Type here to search O DOLI 18:33 14C Sunny A 40) ENG 23-09-2022