Exercise Excel 5-5:
Theater Tickets - Subtotals
|What you will do:
|use the Planning checklist
theater tickets5.xls in folder
excel project5 [Created in previous
The theater manager wants to know what percentage of
total sales during August each of the ticket types was. In other words, he
wants to be able to fill in the blanks in the following sentence:
_____ tickets were _____% of total sales in August. This compares each
type to the total of actual sales.
You will use the Planning Checklist and construct such a sheet based on
theater tickets5.xls from your Class disk.
You will use Subtotals to calculate how many of each ticket type were sold.
You will have to sort first.
theater tickets5.xls from the
excel project5 folder on your Class disk. Use Save As to save the file with a new name-
percentages.xls in the excel project5 folder of your Class disk.
- Planning 1 Goals- In a Comment in cell A1, state the
goals for the sheet.
- Planning 2 Input/Output- In the Comment in cell A1, identify
the output wanted and the input and formulas required.
- Planning 3 Layout- Make changes to the layout and formatting
so it will be easy to read and so that the percentages that the manager wants
to see are especially obvious. Remove from the final sheet the parts of the
original that are not used, such as the play names and dates. Change the
1) From the lower table you only need the data in the columns Ticket Type and # Sold.
2) Sort based on ticket type.
When you sort, the normal
formatting follows the value (note the bottom borders) But conditional
formatting does not follow the value. What a mess this makes! You must
repair the borders and decide whether to repair or to remove the
3) Subtotal to create subtotals for each type of ticket sold.
4) Create the formulas to calculate the percentages.
- Planning 4 Test- Test your calculations by making a copy to Sheet3 and change the numbers on that
sheet - easy numbers, realistic numbers, & special numbers.
- Planning 5 Document- Document your formulas and design
choices using comments in appropriate cells. Have all the comments print at
- Prepare to print: Create a header with your name and the date on the Left, the filename and sheet name in the Center, and Exercise Excel
5-5 on the Right. Spell Check. Print Preview. (All
comments should print at the end.)
- Save. [percentages.xls]
- Print both Sheet1 and the comments. [Results will vary.]