Office 2013 MyITLab MS-Excel Grader Whisenhunt Enterprises

Microsoft Excel

Whisenhunt Enterprises is located in a metropolitan area. Denis Petrillo and Omar Vincent travel frequently for business. To help plan for flight delays, you will create a workbook that provides airline data with up-to-date arrival and departure information from the Tri-State Metropolitan Airport.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step Instructions Points Possible
1 Start Excel. Download the text file exploring_e10_grader_h1.txt, the XML file exploring_e10_grader_h1.xml, and the Excel file exploring_e10_grader_h1.xlsx. Open the workbook. 0
2 In the Airlines Codes sheet, click cell A2 and create a link to the tab-delimited text file you downloaded. Select the option that the text file has headers. Accept all other defaults. 10
3 Open Notepad, edit the e10_grader_h1.txt tab-delimited file. At the end of the last line, press ENTER, type Unified, press TAB, type UNA. Save and close the text file. In Excel, refresh the connection to the text file. 5
4 In the Departures sheet, in cell A1, create a Web query to the departures table on this Web page: http://media.pearsoncmg.com/ph/bp/bp_myitlab/departure_schedule_files/departure_schedule.htm. Maintain full HTML formatting before importing the table. 10
5 In the Filtered List sheet, create a table with headers using the range A1:E29. 5
6 In the Filtered List sheet, click cell A2. Insert a VLOOKUP function to lookup the airline in the Departures sheet, compare it to the airline table in the Airline Codes sheet, and return the code for that airline. For example, Unified will display as UNA. (The function should copy to the range A3:A29 automatically.) 10
7 In the Filtered List sheet, click cell B2. Use a text function to combine the airline code from the previous step with a space and the flight number on the respective row in the Departures sheet. (The function should copy to the range B3:B29 automatically.) 10
8 In the Filtered List sheet, click cell C2. Use a text function to display the cities from the Departures sheet where the first letter is capitalized but the remaining letters are lowercase, such as Houston. (The function should copy to the range C3:C29 automatically.) 5
9 In the Filtered List sheet, click cell D2. Enter a formula to repeat the time from the Departures sheet. Format the range D2:D29 with the Time format, such as 1:30 PM. 5
10 In the Filtered List sheet, click cell E2. Enter a text function that returns the status in title case, such as On Time. (The function should copy to the range E3:E29 automatically.) 5
11 In the Filtered List sheet, set a filter for both Houston airports with a status of On Time. 10
12 In cell A1 of the Airports sheet, create a connection to the exploring_e10_grader_h1.xml file. 10
13 Open exploring_e10_grader_h1.xml in Notepad. Copy the first Airport element and paste the copy above the original first airport element. Edit the data by typing Atlanta and ATL within the proper tags. Change the Chicago airport code to ORD. Save and close the XML file. Refresh the XML connection only. 15
14 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Departures, Filtered List, Airline Codes, Airports.  Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0
Total Points 100
One comment

Leave a Reply

Your email address will not be published. Required fields are marked *



  • File Format: MS-Excel .xlsx
  • Version: 2013