ACIVITY 3

Spreadsheet Exercise #1
The following are step by step instructions to complete this spreadsheet.

This exercise is designed to familiarize you with how to create functions and formulas and
the difference between them.

So what is the difference between a function and a formula?

Formulas
Well, a formula is a simple calculation that involves either *, /, +, -, or (). Of course your
formula will ALWAYS start with an = sign. Here are some examples of a few formulas:

• =(5+6)*9
• =(b1+b2)-b12
• =9+10+b6
• =((b10)*10)/(b2+4)

You'll see that beside the mathematical signs, you may have brackets, numbers, or cell
addresses.

Functions
A function on the other hand contains "functionality" in that there are parts of the "function"
that are programmed into it. For example, let's take the average function. Say we want to
take the average of cells B1 to B5.

We could make a formula that looks like one of these examples:

Both of these formulas do the same thing. They both add the cells b1 to b5 and then
divide the result by 5.

What if you wanted to take the average of 25 cells, 50 cells, 5000 cells. I don't know about
you, but I would definitely be a little frustrated. No need to worry though, because the MS
Works Spreadsheet program has a function that can solve your problems! Let's say we
want to take the average of the cells b1 to b1000. We could enter:

Or:
Or, we could use the average function and enter:

By using this function, MS Excel will generate the average for you. You don't even have to
count up how many cells you have.

This format can be used for a series of cells. You list the first cell, a colon, then the last
cell, and the function will take the average of those cells and all the cells in between them.

Notice that if you take the average of a series, you use a : to separate the first and
last cell addresses.

You may also use another format if you just want to take the average of 2 cells:

This format will take the average of 2 cells.

Notice that if you just want to take the average of 2 cells, you use a , to separate
the 2 cells addresses.

To see a list of all the functions available in MS Excel, go under the Formulas tab and
select Insert Function. This will open a new window displaying all available functions.
Let's say that you were using the SUM function. The SUM function can add 2 values and
up. Be careful and read the questions carefully!! If the question asks you to add A3 to A6
you would type:
=SUM(A3:A6)

If the question asked you to add A3 and A6, you have 2 options. You could type:
=SUM(A3,A6) or more simply =A3+A6

They both will result in the same answer.

Be careful!
=SUM(A3:A6) and =SUM(A3,A6)

DO NOT MEAN THE SAME THING. The colon is used for a series of cells and the
comma is used for 2 cells.
Exercise:

• Open your MS Excel

• Encode and format your spreadsheet that looks below:

1. Ok, fill in the formulas to add up the expenses for each month (dark blue area). Fill
in the sum for each expense as well (yellow area). You are free to use either
formulas or functions to create these totals.
(Hint: If you are going to use a function, your best choice here would be the SUM
function.)
2. Now, fill (right/down) the formulas to add up the income for each month as well as
the total from each income source (green and light blue areas). If you used a
 straight formula for the last step, try using a function this time. And, if you used a
function last time, then try using a straight formula this time.
3. Now finish off the calculations and subtract the expenses from the income for each
month, that is, the purple cell minus the red cell. This calculation should be located
in cell B26 (grey cell).

As you can see, there is often more one way to achieve a desired result. This is a
computer class - the one great thing about computers is that you can do things more than
once. Use this to your benefit. If you are in the middle of the spreadsheet test and don't
get the right answer - don't assume the test is incorrect - try re-doing the question, maybe
in a different way to get the right answer. Try and try again!

Answer Key

• Save your work as SURNAME_BUDGET2003_ACTIVITY 3 to your folder.

 Spreadsheet Exercise #2
The following are step by step instructions to complete this spreadsheet.

Step #1: Formatting Text:

The spreadsheet in this example is adapted from the one shown on your text, Computer
Confluence. To format the title in the spreadsheet, click on cell B1. Click on Home tab at
the top of the page. Under this tab, there are toolbars available that allow you to change
the font, style, colour, and alignment of your entries. The changes you select will be
applied to the selected cells.

For the title cell, B1, select Bold, change the color and change the font size to 16.

For most formatting needs, the quick toolbars available at the top of the screen will suffice.
However, a more detailed and thorough list of the available formatting options is available
in recent versions of Excel by clicking on the small arrow in the bottom right of the font
toolbar:
Which will open up a window that looks like this:

Let's make use of this formatting window to continue modifying the Column Headers.
Select cells A3 to F3, then open the formatting window. Change the "Font Style"
to Bold and the Size to 12.

Let's kick this up another notch by doing an even simpler format of all of the column A
cells.
Click on the A button at the top of the spreadsheet to select the entire column. Now use
the formatting toolbar to make all of that column Bold and 12 point size.

Now there's a problem that may not be obvious at first. The problem is that Column A is
not wide enough for all of the labels in its cells. To solve this problem, we will adjust the
width of the column. To do this, you need to position your cursor over the boundary
between column A and column B and wait till you see the little "adjust" icon appear. Drag
the cursor to the right until all of the labels in the Column A cells are visible.
Now, just one more formatting task for the labels, highlight the cells B3 to F3 and then
click on the "Right Align" button from the set of alignment buttons on the Alignment
toolbar.

Step #2: Entering Data:

Most of the data for this spreadsheet has already been entered. There are just a few more
entries to make, and then you can tap into the power of the Excel Spreadsheet software to
help you complete the rest of this budget worksheet.

• To enter the monthly income for September, select cell B15, type the number 2700,
and press the Enter key. Don't worry about getting in the dollar signs, commas, and
periods just yet.
• To enter the monthly income for October, select the next cell, C15, type the
number 700 and press the Enter key.
• Now the rest of the monthy incomes can all be set to the same as October, so the
easy way to do that is:
o Click on the October income.
o Drag right to highlight the November and December incomes.
o Under the Editing toolbar, and select the Fill button
o Under the dropdown menu, select Fill Right.
• Before proceeding, you should format the spreadsheet to be understood as
currency. To do this:
o Highlight all of the cells that will contain dollar values (B5 to F17)
o Right Click these cells and select the "Format Cells..." option from the drop
down menu
o Under the Number Tab, select "Currency." Make sure that negative numbers
will be displayed in red and that numbers will appear to two decimal places.
Click OK.
• Now your spreadsheet will include dollar signs.

Step #3: Entering Formulas:

Don't forget to use the = sign before entering a formula.

The letters do not need to be capital letters.

• The first formula will go into cell F5. Select that cell and enter the following
formula: =SUM(B5:E5)
Press the Enter key to signal that you have completed the formula.
 • Now, use the Fill function to replicate that formula down the column to F11.
• The second formula will go into cell B13. Select that cell and enter the following
formula: =SUM(B5:B11)

• Once again, use the Fill function to replicate this formula across the row to E13.
• To enter the formula for the September Monthly Balance, select cell B17 and enter
the following formula: =B15-B13

• The next formula builds on that somewhat because you want to include the balance
from the previous month in your calculation. Select cell C17 and enter this
formula: =(C15-C13)+B17

• Use the Fill function to copy that formula across the row to E17.
• Finally, as a last test of a previous modification, change the value in D11 from 100
to 600. If you have adjusted the currency settings properly, as detailed in the
previous section, you will notice that the Monthly Balance for November is now
displayed in Red. If this does not occur, please review Step #2.

Step #4: Entering an IF Function:

• In a row below the Monthly Balance row, we will be creating a series of functions
that will indicate whether the current balance is "OK", or if it is "Overdrawn". To do
this, we will be learning about the IF function.
• First, select cell B18 by clicking on it.
• Select the Formulas tab at the top of the screen, then, click on Insert Function.
 • Scroll through the available functions until you find the IF function. Select it and
Click OK.
• A window will open displaying the requirements of the IF function. These are called
the arguments to the function.
o Specify the Logical Test as B17>=0
o In the If_True Box type "OK"
o In the If_False Box type "Overdrawn"
o The Function Arguments window should look like this:

o Click OK.
• You can also insert an IF function manually using the formula bar by typing "IF" and
then, in the following parenthesis, naming your Logical Test, the value if true, and
the value if false, all seperated by commas. The result will look like this:

This function is identical to the one above

• Just as with previous formulas, use Fill to replicate the IF function across the row,
from B18:E18.
• Notice rows 17 and 18 in particular. You should see a negative value in red in cell
D17. Just below that you should see the word "Overdrawn". All of the other
balances are fine, so the word "OK" appears beneath them. This results from the IF
function.

Now that you have finished editing your spreadsheet, it should look something like this:
• Save your work as SURNAME_BUDGET-EX_ACTIVITY 3 to your folder.