30 points. Due December 5. Late deadline December 18.
Reading:
In this assignment you will complete the identity theft worksheet you started in Task 11.
These instructions are specifically for Excel 2007 and follow the Excel Projects in the textbook. If you have Excel 2003 you can still do the exercise, but the options will be in the menus or can be accessed by right clicking.
1. Open the workbook from the last assignment, Task11.
2. In cell F2, type the column label: % Change 04 to 07
3. Click the Wrap text button in the Home tab of the ribbon. Office 2003 users click after the e in Change and press Alt+Enter from the keyboard to wrap the text.
4. (4 points) In cell F3, enter a formula to calculate the percent change. The formula should take the number for 2007 and subtract the number for 2004, then divide the result by the 2004 number. You MUST consider order of operations when creating this formula. Multiply and divide are completed first (left to right), then plus and minus. You can force a different order by using parenthesis.
5. Use the fill handle to copy the formula down for the other states.
6. (2 points) Format the percent change as % with one decimal place.
7. In cell B13, type the label: Average
8. (4 points) Use the AVERAGE function to calculate the average for 2007.
9. Copy the function across the row for all years.
10. In cell B14, type the label Standard Deviation
11. (4 points) Use the STDEV function to calculate the standard deviation for the values in B3:E12
12. Merge and center the title in A1 across the data columns.
13. Use a fill color of your choice for the title.
14. Make the font size of the title larger so it fills the area over the data nicely.
15. Adjust column widths as needed so all data displays.
16. Add at least 2 different borders of your choice.
17. Right-align the column headings and apply some other formatting to make them look attractive.
18. (2 points) Drag to select the years, state names and data. Do not select the percent change, average or standard deviation.
19. (2 points) Click the Insert tab. Click column and select 2-D Clustered Column. In Excel 2003 click the Chart Wizard toolbar button and fill in the boxes to create the chart instead.
20. Move the mouse pointer into a blank area of your new chart. Wait for it to say Chart Area. Then hold down the left mouse button and move the chart below your data area.
21. Make sure the chart is still selected. Click the Design tab. Choose a different Chart Layout that looks nice.
22. (2 points) In the Layout tab, click Chart Title. Choose Above Chart. Type a descriptive title then press the enter key and type “by <your name>”. Substitute your name for <your name>. Change the font by selecting your name and right clicking the mouse. Choose Font and make it smaller than the rest of the title.
23. (3 points) Use the Average row to make a pie chart with wedges showing the average for each year. Make sure there are appropriate labels on the chart. You can do this by selecting the labels, then holding down the Ctrl key and selecting the numbers. Include a chart title.
24. (2 points) Put the pie chart on a separate sheet by clicking the Move Chart button on the Chart Tools Design tab. Choose New Sheet in the dialog box.
Save this worksheet as Task12. Upload it to Tasks, Tests and Surveys.