can someone please help with the formulas for number 7-12? ive tried everything. thank you!

Your task is to complete the Requests worksheet to provide fare information. All formulas must work when copied down the column to determine the requested information for each travel request. In cell E3 of the Requests worksheet, use the VLOOKUP function to retrieve the name of the departure city for this flight. Copy the formula down to cell E6. In cell F3 of the Requests worksheet, use the VLOOKUP function to retrieve the name of the arrival city for this flight. Copy the formula down to cell F6. 4. In cell G3 of the Requests worksheet, use a combination of the INDEX and MATCH functions to retrieve the base fare for this flight. Copy the formula down to cell G6. 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. 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. 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. . 8. In cells G2:33 of the Discounts worksheet, create a horizontal lookup table (without row headings) that can be used with the HLOOKUP function based on the following discount information: Fare discount category AA: 0% discount on the published base fare. Fare discount category X: 25% discount on the published base fare. Fare discount category Y: 50% discount on the published base fare. Fare discount category Z: 65% discount on the published base fare. 9. In cell K3 of the Requests worksheet, write a formula that includes the HLOOKUP function that determines the discounted fare price (base fare minus discount) of this flight using the table that you created in the Discounts worksheet. Use the ROUND function to round the fares to the nearest dollar. Copy the formula down to cell K6. Check Figure: Cell K5 = $333.00 10. In cell L3 of the Requests worksheet, use the VLOOKUP function to retrieve the airport fee based on the fee schedule in the Fees worksheet. Note that the airport fee is based on the discounted fare. Copy the formula down to cell L6. Check Figure: Cell L4 = $100.00 11. In cell M3 of the Requests worksheet, calculate the total ticket price that Jayhawks in Flight can obtain (discounted fare plus airport fee). Copy the formula down to cell M6. Check Figure: Cell M3 = $229.00 12. In cell N3 of the Requests worksheet, use a formula to compare the Jayhawks in Flight total ticket price to the corporate fare that Rock Chalk, Inc. found. Return TRUE if the Jayhawks in Flight price is less than the corporate fare that Rock Chalk, Inc. was offered. Return FALSE if the Jayhawks in Flight price is not less than the corporate fare that Rock Chalk, Inc. was offered. Do not write an IF statement! Copy the formula down to cell N6. 13. Type your first and last name into the center section of the custom header of the Requests worksheet. 14. Make the Requests tab the active worksheet, save and close your file, and use the Excel 8 link on Blackboard to submit your work. 2 Dute care parte Any way to helpottal 37/22/2010 Frauer, John 1/2010 Simpson, Amanda 5 1/4/2016 wexel Thomin 1/4/2016 Wane, Robert T 10 107 1068 1002 972.00 santranchon seattle 28.00 tondon Los Angeles 457.00 New York Mum 200 NEY 2 + 4 1 4 5 A B C D 1 Flight No. Departure City Arrival City Base Fare 2. 1000 New York Denver $ 579.00 3 1001 New York Seattle $ 938.00 4 1002 New York Seattle $ 939.00 1003 New York Atlanta $ 930.00 6 1004 New York Atlanta $ 948.00 7 1005 New York Atlanta $ 831.00 8 1006 New York Detroit $ 511.00 9 1007 London New York $ 1,634.00 10 1008 San Francisco Detroit $ 770.00 11 1009 London New York $ 851.00 12 1010 New York Los Angeles $ 820.00 13 1011 New York Seattle $ 732.00 14 1012 New York Washington, D.C. $ 267.00 15 1013 San Francisco Seattle $ 272.00 16 1014 New York Boston $ 410.00 17 1015 New York Boston $ 423.00 18 1016 New York Miami $ 500.00 19 1017 New York Boston $ 295.00 20 1018 New York Kansas City $ 770.00 21 1019 New York Boston $ 379.00 22 1020 New York Boston $ 289.00 23 1021 New York Washington, D.C. $ 286.00 24 1022 New York Boston $ 262.00 Requests Flights Fees Discounts 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 A B 1023 San Francisco 1024 San Francisco 1025 New York 1026 New York 1027 San Francisco 1028 New York 1029 New York 1030 Chicago 1031 New York 1032 New York 1033 San Francisco 1034 San Francisco 1035 New York 1036 San Francisco 1037 New York 1038 New York 1039 New York 1040 London 1041 New York 1042 New York 1043 New York 1044 London 1045 New York 1046 San Francisco Requests D Seattle $ 339.00 Kansas City $ 965.00 Dallas $ 755.00 Miami $ 480.00 Atlanta $ 793.00 Denver $ 746.00 Los Angeles $ 1,257.00 Seattle $ 710.00 Washington, D.C. $ 205.00 Los Angeles $ 850.00 Denver $ 368.00 Denver $ 449.00 Los Angeles $ 565.00 Miami $ 716.00 Washington, D.C. $ 302.00 Chicago $ 809.00 Chicago $ 676.00 New York $ 1,273.00 Chicago $ 666.00 Chicago $ 469.00 Chicago $ 829.00 Miami $1,319.00 Chicago $ 689.00 Boston $ 1,288.00 Flights Fees Discounts 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 A B 1047 New York 1048 San Francisco 1049 London 1050 San Francisco 1051 San Francisco 1052 New York 1053 New York 1054 San Francisco 1055 San Francisco 1056 San Francisco 1057 San Francisco 1058 London 1059 San Francisco 1060 London 1061 New York 1062 New York 1063 London 1064 New York 1065 Chicago 1066 New York 1067 San Francisco 1068 New York 1069 San Francisco 1070 London Requests G D Washington, D.C. $ 310.00 Washington, D.C. $ 920.00 Boston $1,539.00 Washington, D.C. $ 1,174.00 New York $ 807.00 Indianapolis $ 765.00 Dallas $ 559.00 Chicago $ 934.00 Chicago $ 919.00 Dallas $ 508.00 Indianapolis $924.00 Chicago $ 1,623.00 Los Angeles $ 343.00 Chicago $ 1,303.00 Detroit $ 528.00 Miami $ 746.00 Atlanta $ 1,434.00 Cleveland $ 464.00 Washington, D.C. $ 379.00 Houston $689.00 Cleveland $ 1,135.00 Miami $ 665.00 New York $ 1.210.00 Washington, D.C. $ 1.025.00 Flights Fees Discounts 64 65 66 67 68 69 70 71 72 E 72 73 74 75 76 B 1070 London 1071 New York 1072 New York 1073 New York 1074 San Francisco 1075 San Francisco 1076 San Francisco 1077 London 1078 San Francisco 1079 London 1080 Chicago D Washington, D.C. $ 1,025.00 Houston $ 889.00 Columbus $ 755.00 Columbus $ 322.00 New York $ 663.00 Houston $ 549.00 Los Angeles $310.00 Houston $ 1,838.00 Columbus $ 949.00 Los Angeles $ 2,277.00 New York $ 504.00 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 Requests Flights Fees Discounts A B D 4. 1 Fare Category Ticket Price Airport Fee 2. 1 $ $ 15.00 3 1 $ 200.00 $ 25.00 4. 2 $ 350.00 $ 35.00 5 2 $ 500.00 $ 40.00 6 2 $ 700.00 $ 50.00 7 3 $ 900.00 $ 60.00 8 3 $ 1,100.00 $ 100.00 3 $1,400.00 $ 125.00 8 9 10 12 13 14 15 16 17 18 Requests Flights Fees Discounts G2 fx E F G H Flight Discounts B D Discount Category 2. Fare Category 3 Weekday 1 2 3 4 1 AA AA X 5 2 Y Y Y 6 3 Y Y Z 7 4 Y Z Z 8 5 Z Y AA 9 6 10 7 Y Y Z 11 12 13 14 15 16 17 18 Requests Flights Fees Riscounts Your task is to complete the Requests worksheet to provide fare information. All formulas must work when copied down the column to determine the requested information for each travel request. In cell E3 of the Requests worksheet, use the VLOOKUP function to retrieve the name of the departure city for this flight. Copy the formula down to cell E6. In cell F3 of the Requests worksheet, use the VLOOKUP function to retrieve the name of the arrival city for this flight. Copy the formula down to cell F6. 4. In cell G3 of the Requests worksheet, use a combination of the INDEX and MATCH functions to retrieve the base fare for this flight. Copy the formula down to cell G6. 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. 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. 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. . 8. In cells G2:33 of the Discounts worksheet, create a horizontal lookup table (without row headings) that can be used with the HLOOKUP function based on the following discount information: Fare discount category AA: 0% discount on the published base fare. Fare discount category X: 25% discount on the published base fare. Fare discount category Y: 50% discount on the published base fare. Fare discount category Z: 65% discount on the published base fare. 9. In cell K3 of the Requests worksheet, write a formula that includes the HLOOKUP function that determines the discounted fare price (base fare minus discount) of this flight using the table that you created in the Discounts worksheet. Use the ROUND function to round the fares to the nearest dollar. Copy the formula down to cell K6. Check Figure: Cell K5 = $333.00 10. In cell L3 of the Requests worksheet, use the VLOOKUP function to retrieve the airport fee based on the fee schedule in the Fees worksheet. Note that the airport fee is based on the discounted fare. Copy the formula down to cell L6. Check Figure: Cell L4 = $100.00 11. In cell M3 of the Requests worksheet, calculate the total ticket price that Jayhawks in Flight can obtain (discounted fare plus airport fee). Copy the formula down to cell M6. Check Figure: Cell M3 = $229.00 12. In cell N3 of the Requests worksheet, use a formula to compare the Jayhawks in Flight total ticket price to the corporate fare that Rock Chalk, Inc. found. Return TRUE if the Jayhawks in Flight price is less than the corporate fare that Rock Chalk, Inc. was offered. Return FALSE if the Jayhawks in Flight price is not less than the corporate fare that Rock Chalk, Inc. was offered. Do not write an IF statement! Copy the formula down to cell N6. 13. Type your first and last name into the center section of the custom header of the Requests worksheet. 14. Make the Requests tab the active worksheet, save and close your file, and use the Excel 8 link on Blackboard to submit your work. 2 Dute care parte Any way to helpottal 37/22/2010 Frauer, John 1/2010 Simpson, Amanda 5 1/4/2016 wexel Thomin 1/4/2016 Wane, Robert T 10 107 1068 1002 972.00 santranchon seattle 28.00 tondon Los Angeles 457.00 New York Mum 200 NEY 2 + 4 1 4 5 A B C D 1 Flight No. Departure City Arrival City Base Fare 2. 1000 New York Denver $ 579.00 3 1001 New York Seattle $ 938.00 4 1002 New York Seattle $ 939.00 1003 New York Atlanta $ 930.00 6 1004 New York Atlanta $ 948.00 7 1005 New York Atlanta $ 831.00 8 1006 New York Detroit $ 511.00 9 1007 London New York $ 1,634.00 10 1008 San Francisco Detroit $ 770.00 11 1009 London New York $ 851.00 12 1010 New York Los Angeles $ 820.00 13 1011 New York Seattle $ 732.00 14 1012 New York Washington, D.C. $ 267.00 15 1013 San Francisco Seattle $ 272.00 16 1014 New York Boston $ 410.00 17 1015 New York Boston $ 423.00 18 1016 New York Miami $ 500.00 19 1017 New York Boston $ 295.00 20 1018 New York Kansas City $ 770.00 21 1019 New York Boston $ 379.00 22 1020 New York Boston $ 289.00 23 1021 New York Washington, D.C. $ 286.00 24 1022 New York Boston $ 262.00 Requests Flights Fees Discounts 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 A B 1023 San Francisco 1024 San Francisco 1025 New York 1026 New York 1027 San Francisco 1028 New York 1029 New York 1030 Chicago 1031 New York 1032 New York 1033 San Francisco 1034 San Francisco 1035 New York 1036 San Francisco 1037 New York 1038 New York 1039 New York 1040 London 1041 New York 1042 New York 1043 New York 1044 London 1045 New York 1046 San Francisco Requests D Seattle $ 339.00 Kansas City $ 965.00 Dallas $ 755.00 Miami $ 480.00 Atlanta $ 793.00 Denver $ 746.00 Los Angeles $ 1,257.00 Seattle $ 710.00 Washington, D.C. $ 205.00 Los Angeles $ 850.00 Denver $ 368.00 Denver $ 449.00 Los Angeles $ 565.00 Miami $ 716.00 Washington, D.C. $ 302.00 Chicago $ 809.00 Chicago $ 676.00 New York $ 1,273.00 Chicago $ 666.00 Chicago $ 469.00 Chicago $ 829.00 Miami $1,319.00 Chicago $ 689.00 Boston $ 1,288.00 Flights Fees Discounts 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 A B 1047 New York 1048 San Francisco 1049 London 1050 San Francisco 1051 San Francisco 1052 New York 1053 New York 1054 San Francisco 1055 San Francisco 1056 San Francisco 1057 San Francisco 1058 London 1059 San Francisco 1060 London 1061 New York 1062 New York 1063 London 1064 New York 1065 Chicago 1066 New York 1067 San Francisco 1068 New York 1069 San Francisco 1070 London Requests G D Washington, D.C. $ 310.00 Washington, D.C. $ 920.00 Boston $1,539.00 Washington, D.C. $ 1,174.00 New York $ 807.00 Indianapolis $ 765.00 Dallas $ 559.00 Chicago $ 934.00 Chicago $ 919.00 Dallas $ 508.00 Indianapolis $924.00 Chicago $ 1,623.00 Los Angeles $ 343.00 Chicago $ 1,303.00 Detroit $ 528.00 Miami $ 746.00 Atlanta $ 1,434.00 Cleveland $ 464.00 Washington, D.C. $ 379.00 Houston $689.00 Cleveland $ 1,135.00 Miami $ 665.00 New York $ 1.210.00 Washington, D.C. $ 1.025.00 Flights Fees Discounts 64 65 66 67 68 69 70 71 72 E 72 73 74 75 76 B 1070 London 1071 New York 1072 New York 1073 New York 1074 San Francisco 1075 San Francisco 1076 San Francisco 1077 London 1078 San Francisco 1079 London 1080 Chicago D Washington, D.C. $ 1,025.00 Houston $ 889.00 Columbus $ 755.00 Columbus $ 322.00 New York $ 663.00 Houston $ 549.00 Los Angeles $310.00 Houston $ 1,838.00 Columbus $ 949.00 Los Angeles $ 2,277.00 New York $ 504.00 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 Requests Flights Fees Discounts A B D 4. 1 Fare Category Ticket Price Airport Fee 2. 1 $ $ 15.00 3 1 $ 200.00 $ 25.00 4. 2 $ 350.00 $ 35.00 5 2 $ 500.00 $ 40.00 6 2 $ 700.00 $ 50.00 7 3 $ 900.00 $ 60.00 8 3 $ 1,100.00 $ 100.00 3 $1,400.00 $ 125.00 8 9 10 12 13 14 15 16 17 18 Requests Flights Fees Discounts G2 fx E F G H Flight Discounts B D Discount Category 2. Fare Category 3 Weekday 1 2 3 4 1 AA AA X 5 2 Y Y Y 6 3 Y Y Z 7 4 Y Z Z 8 5 Z Y AA 9 6 10 7 Y Y Z 11 12 13 14 15 16 17 18 Requests Flights Fees Riscounts