Auto 200 A Malah aplic.CHT.HOLA, Eleme le Home Page Layout To View Costa -20-AA wepo Pate 3 10- aA. DE Merge - $ %) Fom - Candi ng Al Input Area M 0 D Icomparata ) tes De Per 12/31/10 Tar Cote 1 2 Monde 1/11/2010 Salary Input Area M Wisconsin 18 Web Thu how hy weet Region Terred med 8 Title 9 Aalders 10 sa Represente 11 bernas Recentive 1 thng em vui 13 D Beptat 4 out REDE 15 t Representative Fabr Representative 17 G Rretti TBC 19 Halla 30 Berett Bapter Excutive HD City Salary Poul 5125350 w/10/2014 55.00 11/18 St. SS 101/2007 Det $ 100 1/1000 155125 2014 01 5 400 11/10/2015 5641 114/2009 510350 2020 155190 1300 SL 1/11/2011 Des 75 E104 1000 T700 M 51210 300 2014 315 manter Data 2 AM 2r ING e Benowa N The 1-Data worksheet contains employee data. You will insert several functions to complete this worksheet Column C contains the actual hire dates for the employees. You want to extract only the year in column G. In cell G9, insert the appropriate date function to extract the year from the date in cell 09. Copy the function from cell G9 to the range G10G33. 3 3 Next, you want to determine how many years each employee has worked for the company In cell H9, insert the YEARFRAC function to calculate the years between the hire date and the last day of the year contained in cell G2. Use a mixed reference to cell G2. Copy the function from cell H9 to the range H10:H33 You want to determine what day of the week each employee was hired In cell 19, insert the WEEKDAY function to display the day of the week the first employee was hired. Use 2 as the retum_type. Copy the function from cell 19 to the range 110:133. 5 The value returned in cell 19 is a whole number. You want to display the weekday equivalent. In cell 09, insert a VLOOKUP function to look up the value stored in cell 19, compare it to the array in the range H2:16, and return the day of the week. Use mixed references to the table array. Copy the function from cell 09 to the range J10 J33. Column D contains the city each employee works in. You want to display the state in cell F9.insert the SWITCH function to switch the city stored in cell D9 with the respective state contained in the range C2.C4. Switch Des Moines for lowa, St. Paul for Minnesota, and Milwaukee for Wisconsin Use mixed references to cells C2, C3, and C4. Copy the function from cell F9 to the range F10 F33. Your next task is to calculate the bonus for the first employee. If the employee was hired before 1/1/2010 the employee's salary is multiplied by 3% If the employee was hired before 1/1/2015, the employee's salary is multiplied by 2%. If the employee was hired before 1/1/2020 the employee's salary is multiplied by 1% In cell K9 insert the IFS function to create the three logical tests to calculate the appropriate bonus Use mixed references to cells within the range K2L4 Copy the function from cell K9 to the range K10:33 7 Auto 200 A Malah aplic.CHT.HOLA, Eleme le Home Page Layout To View Costa -20-AA wepo Pate 3 10- aA. DE Merge - $ %) Fom - Candi ng Al Input Area M 0 D Icomparata ) tes De Per 12/31/10 Tar Cote 1 2 Monde 1/11/2010 Salary Input Area M Wisconsin 18 Web Thu how hy weet Region Terred med 8 Title 9 Aalders 10 sa Represente 11 bernas Recentive 1 thng em vui 13 D Beptat 4 out REDE 15 t Representative Fabr Representative 17 G Rretti TBC 19 Halla 30 Berett Bapter Excutive HD City Salary Poul 5125350 w/10/2014 55.00 11/18 St. SS 101/2007 Det $ 100 1/1000 155125 2014 01 5 400 11/10/2015 5641 114/2009 510350 2020 155190 1300 SL 1/11/2011 Des 75 E104 1000 T700 M 51210 300 2014 315 manter Data 2 AM 2r ING e Benowa N The 1-Data worksheet contains employee data. You will insert several functions to complete this worksheet Column C contains the actual hire dates for the employees. You want to extract only the year in column G. In cell G9, insert the appropriate date function to extract the year from the date in cell 09. Copy the function from cell G9 to the range G10G33. 3 3 Next, you want to determine how many years each employee has worked for the company In cell H9, insert the YEARFRAC function to calculate the years between the hire date and the last day of the year contained in cell G2. Use a mixed reference to cell G2. Copy the function from cell H9 to the range H10:H33 You want to determine what day of the week each employee was hired In cell 19, insert the WEEKDAY function to display the day of the week the first employee was hired. Use 2 as the retum_type. Copy the function from cell 19 to the range 110:133. 5 The value returned in cell 19 is a whole number. You want to display the weekday equivalent. In cell 09, insert a VLOOKUP function to look up the value stored in cell 19, compare it to the array in the range H2:16, and return the day of the week. Use mixed references to the table array. Copy the function from cell 09 to the range J10 J33. Column D contains the city each employee works in. You want to display the state in cell F9.insert the SWITCH function to switch the city stored in cell D9 with the respective state contained in the range C2.C4. Switch Des Moines for lowa, St. Paul for Minnesota, and Milwaukee for Wisconsin Use mixed references to cells C2, C3, and C4. Copy the function from cell F9 to the range F10 F33. Your next task is to calculate the bonus for the first employee. If the employee was hired before 1/1/2010 the employee's salary is multiplied by 3% If the employee was hired before 1/1/2015, the employee's salary is multiplied by 2%. If the employee was hired before 1/1/2020 the employee's salary is multiplied by 1% In cell K9 insert the IFS function to create the three logical tests to calculate the appropriate bonus Use mixed references to cells within the range K2L4 Copy the function from cell K9 to the range K10:33 7