The purpose of this exercise is to provide the traveler with way of choosing carriers based on the Origin, Destination and data about the
carrier. The carrier data we'reinterested inis their Quality Rating and their on time record.
Carrier Code Carrier Name
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
Naska Millines Inc.
Assignment _Airline Quality_ Rating
Create new worksheet, name it Flight Planner. Place your new worksheet immediately to the right on this worksheet in your workbook.
Set up fields for the traveler to directly enter Origin and Destination airport codes.
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
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
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.
Flights Air Time (hours) Distance (miles) Air Speed (mph)
United Alr Lines Inc.
Insert new pivot table named Airspeed immediately to the right of this worksheet.
Alaska Ailines Inc.
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
AIR TIME HRS which is AIR_ TIME/60
American Airlines Inc
- AIRSPEED which is DISTANCE/AIR_TIME_HRS
Hawaiiar Airlines Inc.
Delta Air inesinc.
The pivot tables fields areas shown: CARRIER NAME, #Flights, AIR_TIME_HRS, DISTANCE, Average AIRSPEED
Sort the data on Average AIRSPEED
Southwest Airlines Co
Format the table and data as shown.
SkyWest Airlines Inc.
Expresslet Airlines Inc
Click here to go to the Lynda video on
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.