1. In cell E2, enter a formula using the HLOOKUP function to determine a staff member?s base salary (which is based on the number of years of service).

a. Use a structured reference to look up the value in the Service Years column. Retrieve the value in the 2 ^nd row of the table in the range Q15:U16 . Since base salary is tiered based on the number of years of service, find an approximate match.

b. Fill the formula into the range E3:E31, if necessary.

2. In cell G2, enter a formula without a function using structured references to determine the number of years since Adam Moriarty received his last First Aid Certification. The formula should subtract the value shown in the First Aid Certification Year column from 2018 and should automatically fill to the range G3:G31.

Transcribed Image Text:

A Staff ID B Name 1103 Adam Moriarty 1053 Artie Jimenez 1056 Bo Peterson 1035 Claire Hunter 1094 Eileen Randall 1066 Flossie Frampton 1050 Grace Richards 1054 Hayley Jin 1070 Hiro Marumi 1040 Isaac Runyon 1010 Isabella Thorne 1073 James Gutierrez 1069 Joey Stockton 1060 Julio de Souza 1043 Kira Weston 1034 Liam Swanson 1084 Maria Flatley 1078 Melinda Masterson 1036 Oscar Alberts 1074 Philip Jones 1076 Richard Tepper 1009 Robert Miller 1089 Sarah Winters 1022 Sienna Shapiro 1033 Stephanie Ito 1065 Stephen McAllister 1078 Sylvia Lee 1048 Tanya Oldman 1053 Tristan Reinholt 1064 Walter Kaminsky C Age 18 28 24 35 18 23 30 25 23 25 33 42 22 22 าว 23 24 32 35 19 20 33 22 21 44 19 38 35 23 20 30 25 23 D Service Years 1 9 7 10 1 0 5 8 4 4 8 25 3 0 7 4 18 1 2 16 5 4 25 2 16 17 6 3 13 8 6 E Base Salary First Aid Certification Year 2017 2015 2013 2016 2013 2016 2017 2013 2013 2016 2017 2014 2015 2013 2015 ---- 2016 2017 Lever 2017 2015 2015 2014 2014 2015 2016 2016 2017 2013 2013 2014 2013 G Years since First Aid Certification H College Graduate No Yes Yes No No Yes Yes Yes Yes Tes Yes Yes Yes Yes Yes No No No No Yes No No Yes No Yes Yes No No Yes Yes Yes Leadership Training Transportation No Yes No Yes No No Yes No Yes Tes No NO Yes No No Yes No Yes No Yes Yes No Yes Yes No Yes Yes Yes Yes Yes Yes Yes Group Leader O Staff ID Staff Name Service Years P Leadership Trained Staff All Staff Service Years Base Salary Oscar Alberts $ es Total Staff 1036 0 R Average Service Years 0 15.00 $ 1 15.50 $ S 2 16.75 $ T 5 17.50 $ U 7 18.50 A Staff ID B Name 1103 Adam Moriarty 1053 Artie Jimenez 1056 Bo Peterson 1035 Claire Hunter 1094 Eileen Randall 1066 Flossie Frampton 1050 Grace Richards 1054 Hayley Jin 1070 Hiro Marumi 1040 Isaac Runyon 1010 Isabella Thorne 1073 James Gutierrez 1069 Joey Stockton 1060 Julio de Souza 1043 Kira Weston 1034 Liam Swanson 1084 Maria Flatley 1078 Melinda Masterson 1036 Oscar Alberts 1074 Philip Jones 1076 Richard Tepper 1009 Robert Miller 1089 Sarah Winters 1022 Sienna Shapiro 1033 Stephanie Ito 1065 Stephen McAllister 1078 Sylvia Lee 1048 Tanya Oldman 1053 Tristan Reinholt 1064 Walter Kaminsky C Age 18 28 24 35 18 23 30 25 23 25 33 42 22 22 าว 23 24 32 35 19 20 33 22 21 44 19 38 35 23 20 30 25 23 D Service Years 1 9 7 10 1 0 5 8 4 4 8 25 3 0 7 4 18 1 2 16 5 4 25 2 16 17 6 3 13 8 6 E Base Salary First Aid Certification Year 2017 2015 2013 2016 2013 2016 2017 2013 2013 2016 2017 2014 2015 2013 2015 ---- 2016 2017 Lever 2017 2015 2015 2014 2014 2015 2016 2016 2017 2013 2013 2014 2013 G Years since First Aid Certification H College Graduate No Yes Yes No No Yes Yes Yes Yes Tes Yes Yes Yes Yes Yes No No No No Yes No No Yes No Yes Yes No No Yes Yes Yes Leadership Training Transportation No Yes No Yes No No Yes No Yes Tes No NO Yes No No Yes No Yes No Yes Yes No Yes Yes No Yes Yes Yes Yes Yes Yes Yes Group Leader O Staff ID Staff Name Service Years P Leadership Trained Staff All Staff Service Years Base Salary Oscar Alberts $ es Total Staff 1036 0 R Average Service Years 0 15.00 $ 1 15.50 $ S 2 16.75 $ T 5 17.50 $ U 7 18.50

Answer rating: 100% (QA)

1 Click on cell E2 and type the following formula that uses structured reference HLOOKUP Service YearsQ15U162 Then press enter If you insert A1J31 as ... View the full answer