QuestionQuestion

Transcribed TextTranscribed Text

Flight Planner Flight Planner The purpose of this exercise is to provide the traveler with way of choosing carriers based on the Origin, Destination and data about the Origin Destinatio IGA carrier. The carrier data we'reinterested inis their Quality Rating and their on time record. Dalayad> Minutes Early>5 Minuter Carrier Code Carrier Name Number % Number % Average Flight Time (min) Average Flight Time (hrs) Add tab at the end of the workbook for Airline Quality data. The data you need is in the file named 10 AS Naska Millines Inc. Assignment _Airline Quality_ Rating 00 letBluc 106 20% 69% 2:06 Create new worksheet, name it Flight Planner. Place your new worksheet immediately to the right on this worksheet in your workbook. PV 17% 50% 121 Ine Set up fields for the traveler to directly enter Origin and Destination airport codes. Spirt In: United Min Lines Inc We'r onlyinterested in the following carriers In the first column enter the following carrier codes: AA, AS, B6, DL, EV, F9, HA, NK, 00, UA, VX, WN Aillines 31% 5A%, For the selected carriers (Hint be sure touse Absolute References when required) Using INDEX and MATCH fill in the Carrier Name and Carrier Quality columns Using COUNTIFS, fill in the number of flights in the Flights column. The value returned should reflect the number of flights for each Extra Credit Use the Location Validation Table to create carrier from the specified origin tothe specified destination. Data Volidation Lists that will allow an end user o select Using COUNTIFS, count fill in the number of flights in the Number of Flights Delayed Minutes columr origins and destinations from dropdown box insteadof Divide the Delayed Flights by the Flights to get the % Flights delayed. You will need to use IFERROR to handle the divide by zero typing them in. Click anywhere in this text box to be errors. taken to Lynda Video to learn how to do Data Validation. When testing your data validation, don be surprised if the origin/departure combination you pick Repeat the above two steps for flights that were Early: 5 minutes zeroes out your table. There are many combinations for which there are noflights. Try testing on combination To fill in the Average Flight Time (min) column you need to use combination of AVERAGE, and Nested IFs as there is no equivalent you know works (e.g., BNAtoDEN, BOStoJFK). to COUNTIFS In addition you will need to use an IFERROR around the entire formula to handle any divide by zero errors that result from the Average calculation. (NOTE: THIS MUST BE TREATED AS AN ARRAY FORMULA.) To fill in the Average Flight Time (hrs) you will divide the Flight Time (min) by 1440. This will convert theminutes to the proper decimal format for Excel time. Use the TEXT function with "h:mm" format to properly display the infor mation. Delay Created by Visibility Issues Carrier Average Airspeed In this case we're interested in seeing the Average Airspeed by Carrier to seehow they compare. Carriers Flights Air Time (hours) Distance (miles) Air Speed (mph) United Alr Lines Inc. 9,695 26,618 12,491,46 469 Insert new pivot table named Airspeed immediately to the right of this worksheet. Virgin America 1.292 3,926 1,839,335 469 Alaska Ailines Inc. 3,233 8,709 4,035,506 463 You're going to use two pivot table calculated fields which you createin the pivot table Analysis tab. You canuse the Lynda tolearn how Frontier Airlines Inc 1,720 3,834 1,754,193 458 to dothis. Spirit Lines 2,498 5,501 2,504,776 455 AIR TIME HRS which is AIR_ TIME/60 American Airlines Inc 17,523 30,986 18,063,944 452 - AIRSPEED which is DISTANCE/AIR_TIME_HRS Hawaiiar Airlines Inc. 1,129 2,096 9/0,924 449 Delta Air inesinc. 15,519 31,200 The pivot tables fields areas shown: CARRIER NAME, #Flights, AIR_TIME_HRS, DISTANCE, Average AIRSPEED 13,903,437 446 Sort the data on Average AIRSPEED JelBlue Airways 5,373 13,134 5,848,738 445 Southwest Airlines Co 24.352 41,347 18,110,387 438 Format the table and data as shown. SkyWest Airlines Inc. 11,091 13,919 5,686,763 409 Expresslet Airlines Inc 10.069 11,734 4,494,005 383 Grand Total 103,794 202,003 89,682,479 444 Click here to go to the Lynda video on calculated fields.

Solution PreviewSolution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

    By purchasing this solution you'll be able to access the following files:
    Solution.xlsx.

    $50.00
    for this solution

    or FREE if you
    register a new account!

    PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

    Find A Tutor

    View available Database Development Tutors

    Get College Homework Help.

    Are you sure you don't want to upload any files?

    Fast tutor response requires as much info as possible.

    Decision:
    Upload a file
    Continue without uploading

    SUBMIT YOUR HOMEWORK
    We couldn't find that subject.
    Please select the best match from the list below.

    We'll send you an email right away. If it's not in your inbox, check your spam folder.

    • 1
    • 2
    • 3
    Live Chats