*** This 10-point assignment is due due by Thursday,
October 22, 2009 at 9:00pm.
Start with the file: T:\teach\classes\for112\IdahoAppropriations.xls
The tasks below detail the formatting and formulas you need to
write to create a spreadsheet that looks like the image below.
1. (1 pt) Center (and wrap the text of) the title across columns
A-G.
Format the title to Bold, Arial, 14 pt font.
2. (1 pt ) Set the row height of Row 1 to 50.25 and
vertically align the text to start at the top of the cell.
3a. (1 pt) Insert a blank row (same height as row 10) between the centered title and the column headings.
3b. (1 pt) Add the heavy single line border at the bottom of the cells with data in rows 3 and 19.
4. (2 pts) For each year in the table, you need a formula in Column F that uses the SUM
function and totals up the
Capital Outlay, Operating Expenditures, and Trustee/Benefit Payments amounts.
Column D is NOT included in the "Total Non-Personnel" total in Column F, so you'll use one cell
range and a cell reference in your SUM formula.
5. (2 pts) Write the average formula for row 21 using the AVERAGE
function and cell ranges.
Note that you only include the yellow years in your average
for each column.
6. (2 pts) Write the formula in G4 in such a way that you can copy it
down the column.
The Column G ratio formula will reference the value in
column F for that row and also use the value in F21 in all rows.
7. (1 pt) Set the column widths at: A: 10.7; B, C, D, E, F: 14.3 (do them all at once), G: 22.15.
8. (2 pts) Format the column headings (centered alignment) and cell A21 (right alignment and a height of 39.75). The text in all of these column headings and cell A21 should be as above -- bold, Arial, 10 pt, with text wrapped within the cell.
9. (2 pts) Format the numbers and percentages as shown.
Be careful
about where the dollar sign ($) appears by choosing the right number format.
10. Save your modified spreadsheet to your ONID space and attach it to an email you send to the instructor. For the e-mail subject line, please use FOR112 HW4.
