*** This 15-point assignment is due due by Thursday,
October 29, 2009 at 9:00pm.
Start with the file: T:\teach\classes\for112\all-nps-visit-9698.xls
1. (1 pt) Rename the fiscal9598 sheet:
visits9698
Insert a new worksheet in the workbook and rename it:
NB
Set the order of the
worksheets so NB is the second sheet.
2. (3 pts) Filter the data of visits9698 to
display only the rows of Type: NB
[National Battlefield]
Copy all these filtered NB data rows with the Row 3 column headers to your
NB sheet.
3. (1 pt) Format all the data columns in the NB sheet to be just wide enough for the column data. (Don't just eyeball things!! Do it the savvy way.)
4. (1 pt) WITHOUT FILTERING, sort the data of the NB sheet to appear in Decreasing order of 1998 visits. Hint..."Monocacy" should appear at the bottom.
5. (1 pt) In visits9698, first turn off the data filter. Then use Find-Replace to change all occurrences of NMem to: National Memorial
6. (1 pt) In visits9698, go to cell C352. Use the AutoSum icon to help you total up the visits for 1996. Only include rows 4 through 346. Copy your formula across to the other years.
7. (1 pt) In visits9698, filter your data again on some Type characteristic. Go to cell C350. If you use the AutoSum icon now to help you write a summation formula, it will help you write a SUBTOTAL formula to include only the filtered rows in the total visits for the year. Manually edit the formula to only include rows 4 through 346. Copy your formula across to the other years. (Change your filter condition and watch the Subtotal result change.)
8. (2 pts) In NB,
use the following text in F1 as the header for the formula you are about to
write: % of Total 1998 NPS Visits (Format
like the other column headers and then add word wrap)
The formula in F2 will divide E2 by the value in E352 on visits9698.
Copy your formula down the column and format the results as a percentage
with enough decimal places to not display any results as zero.
9. (1 pt) You'll find a comment in one of the cells in the first 20 rows of visits9698. Follow the comment instructions.
10. (3 pts) Make sure the data in visits9698
is unfiltered at this point. Use Page Layout | Page Setup Pullout menu and its many tabs to setup a
landscape
printout printed with gridlines
and fitted to
only one page with your full
name printed as a centered footer.
Print only the cells A1:E60 (through Chamizal).
Your printout must be submitted at the beginning of class on Friday, October
30 (or before).
Leave the data of visits9698 filtered when you save your file.
11. Save your modified spreadsheet as your_onid_username-hw5.xls to your ONID space and attach it to an email you send to the instructor. For the e-mail subject line, please use FOR112 HW5.
