Problem Set # 6 - Due March 12,
2003
This assignment will be out of 25 possible points. You will be generating a spreadsheet showing your monthly budget.
- See the example resulting spreadsheet here. This is an example of what you're final spreadsheet may look like.
- You will be generating the same type of spreadsheet with DIFFERENT NUMBERS and different entries. The style is up to you. This is YOUR budget.
- You must have at least 8 columns.
- MINIMALLY you should have at least income and expenses, you can always add extra columns.
- Definition: A
grouping is:
- 1 column of text
- 1 column of numbers
- You could have, for example:
- 2 groupings of bills and 2 groupings of income
- 3 groupings of
bills and 1 grouping of income
- It should have a minimum of two forms of bills:
- monthly bills
- one other form of bills
- weekly
- annual
- bi-annual
- You must have some sort of income shown
- For example:
- parental income
- weekly job
income
- monthly job
income
- financial aid
income
- You will need an area for total Monthly bills
- You will need to use functions here.
- SUM adds groups of numbers
- Reference the
example work sheet.
- for example If I type =SUM(D2:D9) I add the cells from D2 to D9
- empty cells do not
change adversely affect the formula.
- I can do multiple math tasks at the same time
- =(SUM(B2:B9)*4)
+ SUM(D2:D9) will add my weekly bills and multiply them by four. To
this value I add my monthly bills.
- You will need a mathematical formula describing your monthly bills.
- For example
weekly bills should be multiplied by 4, yearly bills should be divided
by 12, bi-annual bills should be divided by 6, etc)
- You will need an area for Total Monthly Income
- If you make one annual student loan,
divide that figure by 12 in your spreadsheet calculation
- My calculation
from the spreadsheet for cell B11 =(SUM(J2:J9) + (SUM(L2:L9))/12)
- You will need an area showing how much money you have
left over each month (it may be negative as students often go into
debt).
- You will need to format your numbers as currency.
- Select the desired cells
- Under the Format Menu select the
Cells... option
- Click on the Number tab
- Choose Currency
- Make sure the symbol shows
- You will need a font that is not the default somewhere.
- Some Examples:
- Comic Sans
- Arial Narrow
- You will need to
italicize or bold face some text.
- I would like the cells formatted.
- Notice the bold lines I have around
my cells
- MINIMAL formatting: Total Monthly income,
Total monthly bills, and play money should be formatted with a box made
up of a bold line
- In Format menu choose cells
- Brings up a dialog box
- change the borders around cells
- I would like one cell
to have a different color and/or pattern.
- It should be visible when printed, so I recommend a pattern with contrasting colors
- For the example
it was cells A13:B13
- The monthly play money should be conditionally formatted
- It should have a pattern or background color if the value is negative (Pattern if you plan on printing in black and white, a color if you plan on printing in color)
- It should have a
pattern or font color change if the value is positive
- Click on the Cell (In the example Cell B14 )
- Under the format menu, select
"Conditional Formatting...>
- Choose Cell Value Is
- less than
- fill in 0 in the other box
- Click on Format....
- click on the Patterns tab for
colors and patterns
- In the "Conditional Formatting
"Add >> " another format
- Choose Cell Value is greater than
or equal
- Format...
- click on the Font tab to change
the font
- PRINT THE PAGE
· Cells can be addressed by their coordinates
o Cell A1 is the top left cell on the sheet
· Cells can be addressed by groups, this is useful for functions applied to them
o A1:A5 references the column of cells from A1 to A5
o B2:D2 references the row of cells from B2 to D2
o B2:D3 references a block of cells
· It is possible to alphabetize lists AFTER you create them
o Select the cells that you wish to alphabetize
§
make sure you get the text and the
value that goes with it. (For example I would select A2:B4)
o Notice the tool bar has a symbol which has an A on top and a Z on the bottom with an arrow beside it. If you click on this, the selected cells will alphabetize according to the contents of A2:A4)
§
If you don't like the results ^Z will
reverse your last change
HELP WITH EXCEL
- Starting Microsoft Excel
- An empty spreadsheet should appear
- IF IT DOES NOT show an empty spreadsheet
- Select New from the File Menu
- Choose a new workbook
- Click OK
- Click on Cell A1
- Enter the text Weekly Bills
- Change the font to anything other than the default. (For example Comic Sans)
- Notice that as you type the text appears both in the box and the line at the top after the equal sign
- It is possible to edit the text in either box
- If the text doesn't appear in the line at the top
- Go to the Tools menu
- Choose options...
- Under the View, choose to show Formula bar
- Click on Cell A2
- add text describing a weekly bill (for example food)
- Click on Cell B2
- Enter a value for the amount of money spent on the weekly bill. (For example if you live in the dorms you may buy one pizza per week. Your weekly bill would be $18.00)
- Correct the width of column 1 to allow for all of the text to show
- Put the mouse between the A and the B at the top of the spreadsheet.
1. Click and release the left mouse button
- Notice that the cursor changes to show a line and two arrows.
- This allows us to pull column A over
- Click the mouse button again, but this time hold it down while dragging column A to a width large enough to show all of the text
2. OR double click the left mouse button between A and B at the top of the spreadsheet.
