Excel Spreadsheets
Assignment 2
Information in Cells
You can enter various kinds of information in Excel cells. We have entered:
Text - which serves as labels for our columns and rows
Numbers - this serves as our raw data, for example our monthly expenses and income.
Finally, we can add Formulas. This is part of the power of Excel. We can enter mathematical expressions that will automatically perform the calculations for us. For example we can add up our monthly bills and subtract the total from our monthly income to calculate the spending money that is left over.
We have made the base for our budget spreadsheet. It contains the data that we have entered for our monthly expenses and income. Now we need to add the formulas to do the calculations discussed above for us. Excel is quite friendly to help us create our formulas. Generally, the steps are as follows:
Refer to a cell address, i.e., C7, D8, etc.
Perform a mathematical operation much like an algebraic expression, i.e., +, -, * (the Shift - 8 key), or /.
RULE - In Excel you ALWAYS tell the program that you want to create a formula by starting with an equal sign (=).
Look at our little spreadsheet. We want to calculate our monthly bill total. We will label cell B11 as Totals. Click on cell B11 and type "Totals". Lets calculate the Jan bills by using what we have just learned. In cell C11 type the following (not the " marks), "=C7+C8+C9". Press the Enter key.
Let's look at our results for the month. The bills Total $745. Look at how Excel came to that value. What if we examined our bills and found that we had made an error. The utilities did not total $120 for the month, but $130. Type "$130" in cell C9 and observe the effect on our monthly bills. Excel re-evaluates the new information for us.
Repeat the calculations for Feb and Mar. Results should be $750 and $645 respectively.
How would you use Excel to calculate the Quarter totals for rent for Jan, Feb and Mar. Do the calculation. Now repeat the calculations for the Quarter totals for heating and utilities. Quarter total results should be $1500 for rent, $335 for heating and $315 for utilities.
Now, we need to calculate our spending money that is left over for the month. Lets put our monthly income in cell F3. We'll say that it is $1475.
In cell B13 (selected arbitrarily), you can put text to describe your spending money. Call it "Leftover" (without the " marks), or Spending Money. Our Spending Money for each month is our monthly income minus our monthly bills. So in cell C13 type "=F3-C11", to give the result $720. Examine the result to understand where it came from.
Repeat the calculation for Feb and Mar. The results should be $725 and $830.
We can also calculate our quarterly monthly bill totals, and quarterly Spending Money totals. Obtain these results in cells F11 and F13. Your results should be $2150 for monthly bills and $2275 for spending money.
W always try to show you the way that the program thinks. A good way to do that is use the Formula Bar in Excel.
Formula Bar - is the long white box after the = sign near the top of your screen. It is right below the toolbar. It contains the true contents of your cells. Why is that important?
Click on cell B7. The Formula Bar shows that the cell contains the text "Rent"
Click on cell C7. The Formula Bar shows that the cell contains the value 500. The cell shows $500, but that is because we formatted the cell to look like currency. The value in the cell is really 500.
Click on cell C11. The cell shows $755. But that is not the true contents of the cell, which is in fact the sum of cells C7+C8+C9.