What is the answer(excel formula) for question b?
a) Calculate the miles traveled for each route (from Pickup City to DropOff City). To determine the Miles Traveled, use the mileage table to lookup pickup city and dropoff city and then, get the miles on the intersection of row and column. (Use a function that returns one value after searching the Truck Driver Maximum Travel Table) Baltimore 83.2 Mileage Table Vienna Baltimore Alexandria Springfield Rockville Vienna 0 83.2 35.2 22.4 27.2 Alexandria 35.2 88 0 16 43.2 Springfield 22.4 96 16 0 41.6 Rockville 27.2 70.4 43.2 41.6 0 88 96 70.4 b) Create a conditional formatting rule to highlight route table rows with miles traveled greater than required mileage break (Your rule will need to lookup the mileage break value for each truck driver from the Truck Driver Maximum Travel Table.) Miles Traveled | Truck Driver Maximum Travel Table Truck Driver ID Mileage Break ABC1234 70 DEF5678 50 GHI9101 90 JKL1121 50 MNO3141 50 POR5161 STU7181 VWX9202 YZA1222 90 90 Truck Route Table Truck Driver ID Route Order ABC1234 ABC1234 ABC1234 3 DEF5678 DEF5678 2 DEF5678 DEF5678 GHI9101 GHI9101 GHI9101 JKL1121 JKL1121 JKL11213 JKL11214 MNO31411 MNO31412 MNO31413 PQR5161 1 POR5161 POR5161 STU7181 STU7181 STU7181 VWX9202 VWX9202 VWX9202 YZA12221 YZA12222 YZA1222 3 Pickup City Springfield Baltimore Vienna Baltimore Alexandria Springfield Rockville Alexandria Rockville Springfield Alexandria Rockville Baltimore Vienna Rockville Vienna Alexandria Alexandria Baltimore Springfield Rockville Springfield Vienna Rockville Springfield Alexandria Alexandria Springfield Rockville Drop Off City Vienna Springfield Baltimore Rockville Baltimore Alexandria Alexandria Springfield Alexandria Vienna Vienna Springfield Rockville Baltimore Vienna Alexandria Rockville Springfield Vienna Baltimore Vienna Rockville Baltimore Alexandria Rockville Springfield Baltimore Rockville Alexandria a) Calculate the miles traveled for each route (from Pickup City to DropOff City). To determine the Miles Traveled, use the mileage table to lookup pickup city and dropoff city and then, get the miles on the intersection of row and column. (Use a function that returns one value after searching the Truck Driver Maximum Travel Table) Baltimore 83.2 Mileage Table Vienna Baltimore Alexandria Springfield Rockville Vienna 0 83.2 35.2 22.4 27.2 Alexandria 35.2 88 0 16 43.2 Springfield 22.4 96 16 0 41.6 Rockville 27.2 70.4 43.2 41.6 0 88 96 70.4 b) Create a conditional formatting rule to highlight route table rows with miles traveled greater than required mileage break (Your rule will need to lookup the mileage break value for each truck driver from the Truck Driver Maximum Travel Table.) Miles Traveled | Truck Driver Maximum Travel Table Truck Driver ID Mileage Break ABC1234 70 DEF5678 50 GHI9101 90 JKL1121 50 MNO3141 50 POR5161 STU7181 VWX9202 YZA1222 90 90 Truck Route Table Truck Driver ID Route Order ABC1234 ABC1234 ABC1234 3 DEF5678 DEF5678 2 DEF5678 DEF5678 GHI9101 GHI9101 GHI9101 JKL1121 JKL1121 JKL11213 JKL11214 MNO31411 MNO31412 MNO31413 PQR5161 1 POR5161 POR5161 STU7181 STU7181 STU7181 VWX9202 VWX9202 VWX9202 YZA12221 YZA12222 YZA1222 3 Pickup City Springfield Baltimore Vienna Baltimore Alexandria Springfield Rockville Alexandria Rockville Springfield Alexandria Rockville Baltimore Vienna Rockville Vienna Alexandria Alexandria Baltimore Springfield Rockville Springfield Vienna Rockville Springfield Alexandria Alexandria Springfield Rockville Drop Off City Vienna Springfield Baltimore Rockville Baltimore Alexandria Alexandria Springfield Alexandria Vienna Vienna Springfield Rockville Baltimore Vienna Alexandria Rockville Springfield Vienna Baltimore Vienna Rockville Baltimore Alexandria Rockville Springfield Baltimore Rockville Alexandria
