Design:
Exercise Excel 5-5

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

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.
Full floppy disk How to handle a full disk

 


Where you are:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel BasicsTo subtopics    

Project 3: Format & ArrangeTo subtopics   

Project 4: Groups & FormulasTo subtopics

Project 5: Design
    Analysis To subtopics
    What If...To subtopics
    Logical TestsTo subtopics
    Sharing DataTo subtopics
    Summary
    Quiz
    Exercises To subtopics
    Exercise Ex. 1 Theater Tickets
    Exercise Ex. 2 Soccer - IF
    Exercise Ex. 3 Soccer - What If
    Exercise Ex. 4 Computers Today
    Exercise Ex. 5 Ticket subtotals
    Exercise Ex. 6 On Your Own

Search 
Glossary  
Appendix

Exercise Excel 5-5:

Theater Tickets - Subtotals
What you will do: use the Planning checklist
sort
Subtotal
create formulas

Start with: Class disk, 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. Class diskOpen 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.
    WarningWhen 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. Class disk Save. [percentages.xls]
     
  9. Print  Print both Sheet1 and the comments. [Results will vary.]