Week 7 Practical Exercise

Week 7 Practical Exercise

EXCEL PRACTICAL EXERCISE IF function

Note: If the function or method was not covered in class or in the textbook, it should not be part of your answer. DO NOT go to the internet for solutions.

You are the troop leader for a Girl Scout Troop and it Cookie Sales Time. You want to keep track of how well your troop did with the sales so that you can give them a prize. Instructions:

1. Save the workbook as yourname_P5 2. Enter the data below into an Excel Spreadsheet. Be sure to keep the same columns

and rows.

A B C D E F G H

1 First Name Last Name

Months in Troop

Chal et

Samoa Thin Mints

Peanut Butter

Tagalongs

2 Jacqueline LaMar 48 5 8 12 16 5

3 Mariah Parr 52 6 7 13 14 3

4 Rachel Friend 18 7 5 19 13 6

5 Sharon Ames 6 2 5 21 11 4

6 Susie Kwon 12 9 6 14 11 9

7 Hannah Evans 33 4 2 13 9 8

8 Eliza Doolittle 17 2 7 9 12 3

9 Rebecca Lun 22 3 6 16 11 5

10 Marjorie Smith 37 4 2 11 15 8

3. Enter the row title TOTAL at cell A11 4. Enter the column title TOTAL at cell I1 5. Enter the column title PRIZE at cell J1 6. Insert 2 blank lines above the column titles 7. Enter the title: Girl Scout Cookie Sales 8. Enter the subtitle: Troop JR309DL

CALCUATIONS

9. Use the SUM function to find the Totals for each type of cookie sold a. Be sure to use the fill handle to copy the formula

10. Use the SUM function to find the Totals for the amount each Girl Scout sold a. Be sure to use the fill handle to copy the formulas

11. Enter the following row titles:

a. A15: Prize i. Make it bold

b. A16: T-Shirt c. A17: No Prize

Continued on the next page

IF FUNCTION 12. Show the Prize each Girl Scout has earned.

a. You MUST use the IF Function and use Absolute Reference to find the Prize for each of the Girl Scouts

i. USE the fill handle to copy the formula to all the other cells in the column b. If she sold more than 45 boxes of cookies, she gets a t-shirt (A16) c. Anything Less, she gets No Prize (A17)

FORMATTING

13. Center the Title and Subtitle across the table a. Format them to your liking

14. Place a BLUE colored Double Line Border around all the cells in the table 15. Center all of the Column Titles 16. Center all of the cells that contain numbers 17. Format the rest of the table to your liking.

a. Don’t just place the data into the table 18. Change the page orientation to Landscape 19. Adjust column widths as necessary 20. Name the sheet COOKIE SALES and color the tab BLUE

CHARTING 21. Create a 3-D Pie Chart that shows the totals for each type of cookie sold 22. Move the Graph to a new sheet

a. Call the new sheet Total Sales b. Add a title to the Chart

i. You create one c. Add Data Labels to show amount sold (no percent) d. Color the TAB RED

FINISHING TOUCHES

23. Add your name and student ID number to the header 24. Re-Order the tabs so that the chart is the 2nd tab 25. Change the document properties

– Author, Subject and TAGS (keywords – use a minimum of 3 words separated by commas).

o Tags should NOT be your initials, Excel, exercise, practical…