Flight No. A B C D F Flight No. Departure City Arrival City Base Fare 1000 |New York Denver $ 579.00 3 1001 New York Seattle 938.00 A 1002 New York Seattle 939.00 5 1003 New York Atlanta 930.00 6 1004 New York Atlanta ur 948.00 1005 New York Atlanta 831.00 8 1006 New York Detroit s 511.00 9 1007 London New York $ 1,634.00 10 1008 San Francisco Detroit $ 770.00 11 1009 London New York S 851.00 12 1010 New York Los Angeles 820.00 13 1011 New York Seattle ur 732.00 14 1012 New York Washington, D.C. 267.00 15 1013 San Francisco Seattle ur 272.00 16 1014 New York Boston 410.00 17 1015 New York Boston ur 423.00 18 1016 New York Miami 500.00 19 1017 New York Boston S 295.00 20 1018 New York Kansas City 770.00 21 1019 New York Boston 379.00 22 1020 New York Boston S 289.00 23 1021 New York Washington, D.C. 286.00 24 1022 New York Boston S 262.00 25 1023 San Francisco Seattle S 339.00 26 1024 San Francisco Kansas City S 965.00 27 1025 New York Dallas 755.00 28 1026 New York Miami ur 480.00 29 1027 San Francisco Atlanta 793.00 30 1028 New York Denver S 746.00 31 1029 New York Los Angeles $ 1,257.00 32 1030 Chicago Seattle $ 710.00 33 1031 New York Washington, D.C. S 205.00 34 1032 New York Los Angeles 850.00 35 1033 San Francisco Denver 368.00 36 1034 San Francisco Denver 449.00 37 1035 New York Los Angeles S 565.00 38 1036 San Francisco Miami 716.00 39 1037 New York Washington, D.C. 302.00 40 1038 New York Chicago S 809.00 Requests Flights Fees Discounts +fx Enter text or formula here A B C D E F G H K L Corporate Request Form Date Traveler Flight No. Corporate Fare Departure City Arrival City Base Fare Weekday Fare Category Discount Discounted Fare Airport Fee 8/1/21 Frasier, John 1013 $ 972.00 San Francisco Seattle $ 272.00 Sunday 1 U A W N 8/2/21 Simpson, Amanda 1079 $ 4,285.00 London Los Angeles 2,277.00 Monday 3 8/5/21 Wexel, Thomas 1068 $ 457.00 New York Miami 665.00 Thursday 1 8/6/21 Wang, Robert 1002 $ 837.00 New York Seattle S 939.00 Friday N 8 9 10 11\ffx Fare Category A B C D E Fare Category Ticket Price Airport Fee 2 1 $ $ 15.00 3 1 $ 200.00 25.00 4 2 $ 350.00 35.00 UI 2 $ 500.00 40.00 6 2 700.00 50.00 3 900.00 60.00 8 W 1,100.00 100.00 3 $ 1,400.00 S 125.00 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Requests Flights Fees Discounts +5. In cell H3 of the Requests worksheet, use the WEEKDAY function to determine the day of the week of this flight (Sunday = 1, Saturday = 7). Copy the formula down to cell H6. Figure Sense: How should you use the WEEKDAY function to compute the day of the week for each requested flight? Excel 6 BBA 407: Excel Expertise and Certification Page 2 How can you check to make sure that you have used the WEEKDAY function correctly? 6. Airline tickets are assigned a fare category based on the base fare ticket price (column G of the Requests worksheet) and the categories listed on the Fees worksheet. In cell 13 of the Requests worksheet, use the XLOOKUP function (or the LOOKUP function if you don't have the XLOOKUP function) to retrieve the fare category for this flight. Copy the formula down to cell 16. Figure Sense: How should you use the XLOOKUP or LOOKUP function to compute the fare category? The syntax for the XLOOKUP function is: =XLOOKUP(lookup_value,lookup_array,retum_array,[if_not_found],[match_mode],[search_mode]). What are the values for each of the arguments in the XLOOKUP function? The syntax for the LOOKUP function is: =LOOKUP(lookup_value, lookup_vector,[result_vector]). What are the values for each of the arguments in the LOOKUP function? How can you check to make sure that you used either the XLOOKUP or LOOKUP function correctly? 7. The Discounts worksheet contains a two-dimensional table that has been set up to find the discount category of a ticket based on the weekday of travel and the fare category. In cell J3 of the Requests worksheet, use the INDEX function to retrieve the discount category for this ticket. Copy the formula down to cell J6. Figure Sense: How should you use the INDEX function to compute the discount category? How can you check to make sure that you used the INDEX function correctly