Excel Project: Airport Worksheet

Copying and adjusting tables

1. a. Copy the entire table from the Sorted worksheet to the Airport worksheet. In the Airport worksheet, the field names should be in row 1 and the Year column should be in column A.

b. Some column adjustments may be necessary so that the field names as well as the data is all legible.

c. Make sure that the same format requirements for the Data Worksheet are met in the Airport Worksheet.

2. In the Airport worksheet:

a. Add a new column heading AvgRev for the column where the average revenue per car for each row will be calculated and displayed. This column must be the next column to the right of the NumCars column, the column heading should be in Cell G1.

b. Add a formula to calculate the AvgRev by dividing the contents of the Revenue cell by the contents of the NumCars cell for each data row in the table c. The AvgRev values format is currency (a $ and two decimal points) with no space between the $ and the first number following it. When you are clicking on cells to construct a formula in cell G2, while you may be expecting to see is =E2/F2 what may appear in the cell editor is =[@Revenue]/[@NumCars] or =[Revenue]/[NumCars], the last two formulas the result of using an Excel table.d. Column adjustment may be needed to ensure both the title and the data is visible.

e. Apply the formatting instructions in the Comments column (next column at right).

3. In the Airport worksheet:

a. Create a range named AE that includes every AvgRev value for all Economy cars.

b. Create a range named AH that includes every AvgRev value for all Hybrid cars.

c. Create a range named AP that includes every AvgRev value for all Premium cars.

d. Create a range named AS that includes every AvgRev value for all SUV cars.

4. In the Airport worksheet:

a. In row 1, two or three columns to the right of the AvgRev column:

b. Create a label called Average Quarterly Revenue per Economy Car (Airport) in row 1.

c. Immediately (same column, next row down) under the Economy label create a label called Average Quarterly Revenue per Hybrid Car (Airport) in row 2.

d. Immediately (same column, next row down) under the Hybrid Car label, create a label called Average Quarterly Revenue per Premium Car (Airport) in row 3.

e. Immediately (same column, next row down) under the Premium label, create a label called Average Quarterly Revenue per SUV (Airport) in row 4.

f. Apply the formatting instructions in the Comments column (next column, at right)

5. In the Airport worksheet:

In the first fully visible cell to the right of the of the widest of the four labels so that all four values are in the same column aligned with the appropriate row for the specified carclass:

a. Calculate the Average Quarterly Revenue (AQR) for the Label-indicated carclass for each of the four carclasses.

b. Use the average function and the named range (AE, AH, AP, or AS) ONLY NOT a range of cells. Ensure each of four averages is Currency format with two decimal places preceded by a $ with no spaces between the $ and the first number