Excel File Edit Insert F Format Tools Data Window Help zm 100% 8. Mon Jun 9 10:00 AM AutoSave KN BoydLogistics Final Project 2 Q Search (Cmd + Ctrl + U) Home Insert Draw Page Layout Formulas Data Review View Automate Table Comments Share X Cut AutoSum v Calibri 11 AA 29 Wrap Text v Date Copy Fill Paste $ ~ % " conditional Format Cell Insert Delete Format Sort & Find & Analyze Format B U v MVA Merge & Center v Formatting as Table Styles Clear v Filter Select Data A6 X V fx 2/1/2023 W AA AB AC AD AE AF AG AH Boyd Logistics Route Data Boyd Invoice ID Origin Destination Containers Damaged L 2/1/23 BLOO1 Kansas City Chicago 90 2/4/23 BLOO ansas City Fargo 2/7/23 BLOO3 Chicago Omaha 2/10/23 BLOO4 Kansas City Minneapolis 90 2/1/23 BLOO5 St. Louis Kansas City 75 2/4/23 BLOO6 Chicago St. Louis 90 2/7/23 BLOOT Fargo Milwaukee 2/10/23 BLOO8 Chicago Kansas City 2/13/23 BLOO9 Fargo Kansas City 2/16/23 BLO10 Omaha Chicago 8 8 5 2/19/23 BLO11 St. Louis Chicago 2/22/23 BLO12 Minneapolis Kansas City 2/25/23 BLO13 Fargo Chicago /28/23 BL014 St. Louis Omaha 3/3/23 BLO15 Kansas City Chicago 3/6/23 BLO16 Kansas City Fargo 3/9/23 BLO17 Chicago omaha 3/12/23 BLO18 Kansas Cit Minneapolis 3/15/23 BLO19 St . Louis Kansas City 3/18/23 BLO20 Chicago St. Loui 3/21/23 BLO2 Fargo Milwaukee 3/24/23 BLO22 Chicag Kansas City 3/27/23 BLO23 Fargo Kansas City 3/30/23 BLO24 Omaha Chicago 4/2/23 BLO25 St. Louis Chicago 4/5/23 BLO26 Minneapolis Kansas City 4/8/23 BLO27 Chicago /11/23 BLO28 St. Louis Omaha 4/14/23 BLO29 Kansas City Chicago 4/17/23 BLO30 Kansas City Fargo 4/20/23 BLO31 Chicago Omaha 4/23/23 BLO32 Minneapolis 4/26/23 BLO33 kan city St. Louis Kansas City 4/29/23 BLO34 Chicago St. Louis 5/2/23 BLO35 Fargo Milwaukee 5/5/23 BLO36 Chicago Kansas City 5/8/23 BLO37 Fargo Kansas City 5/11/23 BLO38 Omaha Chicago 5/14/23 BLO39 St. Louis Chicago 5/17/23 BLO40 Minneapolis Kansas City 5/20/23 BLO41 Fargo Chicago 5/23/23 BLO42 St. Louis Omaha 5/26/23 BLO43 Kansas City Chicago 5/29/23 BLO44 Kansas City Fargo 6/1/23 BLO45 Chicago Omaha /4/23 BLO46 Kansas City Minneapolis 6/7/23 BLO47 St. Louis Kansas City /10/23 BLO48 Chicago St. Louis 6/13/23 BLO49 Fargo Milwaukee 6/16/23 BLO50 Chicago Kansas City 6/19/23 BLO51 Kansas City 6/22/23 BLO52 Omaha Chicago 6/25/23 BL053 St. Louis Chicago 6/28/23 BLO54 Minneapolis Kansas City 7/1/23 BL055 Fargo Chicago 7/4/23 BLO56 St. Louis Omaha RouteData Mileage Table ExpansionLo Questions + Ready TX Accessibility: Investigate 75%2. On the RouteData sheet is a list of the trips from February 1 through July 4, 2023. On the MileageTable sheet, is a table with the miles between the different cities. 3. Name the mileage table MileageTable, 4. On the RouteData sheet, add a new column titled Miles. 5. In the new column use the INDEX function with the XMATCH function nested inside it to determine the miles driven for each route. Spot check a few of the routes to make sure your function is working correctly. (Hint: remember the INDEX array is the whole Mileage Table. An XMATCH function will be nested in the INDEX Row num box/argument to determine which row the city of origin is in. A second XMATCH will be nested in the INDEX Column num box/argument to determine which column the destination city in is.) BA 216A Final Project Part II Page 1 of 3 6. Add another new column titled Charges. 7. In this column, use an IF function to calculate the total charges for each trip. If the number of containers on the trip is equal to or greater than 50, the cost per mile is $2.50. Otherwise, the cost per mile is $2.00. 8. Build a PivotTable from the RouteData sheet showing the total charges and number of containers for each city of origin. Place this PivotTable on a new sheet named RataAnalysis