# Design: Exercise Excel 5-5

Did you want Working with Numbers: 2007,2010,2013,2016  or español

You need to use what you just learned, and maybe learn a little more. Complete all parts of the following exercises. Don't forget to backup your Class disk when you have completed the exercises or whenever you stop for the day and saved a document along the way.

This exercise uses the document from Exercise 5-1. Save the changed document to your Class disk in the excel project5 folder. This keeps the original files intact in case you need to start over.
How to handle a full disk

Where you are:
JegsWorks > Lessons > Numbers

Project 2: Excel Basics

Project 4: Groups & Formulas

Search
Glossary

Appendix

## Theater Tickets - Subtotals

 What you will do: use the Planning checklist sort Subtotal create formulas

Start with: , theater tickets5.xls in folder excel project5 [Created in previous exercise 5-1.]

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:
Type _____ 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.

1. Open 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.

2. Planning 1 Goals- In a Comment in cell A1, state the goals for the sheet.

3. Planning 2 Input/Output- In the Comment in cell A1, identify the output wanted and the input and formulas required.

4. 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 subtitle.

Hints:
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 conditional formatting.

3) Subtotal to create subtotals for each type of ticket sold.

4)  Create the formulas to calculate the percentages.

5. 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.

6. Planning 5 Document- Document your formulas and design choices using comments in appropriate cells. Have all the comments print at the end.

7. 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.)

8.  Save. [percentages.xls]

9.   Print both Sheet1 and the comments. [Results will vary.]