|
NZ Macguide Issue 6
Lots of us get AppleWorks free with our Macs. However, many of us don't realise quite how handy this multitasking software is.
We're thinking of going on holiday to Vanuatu. Before we can really make any decisions, though, we need to work out how much it will all cost. Pen and paper are handy, and a calculator makes things easier, but what happens if something changes, such as finding a lower airfare? We have to do all the calculations again. And we're never quite sure we didn't mistype one of the numbers into the calculator.
Here's where an AppleWorks spreadsheet can smooth the way for you. You can use a spreadsheet any time you want to work with numbers: plan a holiday, keep track of your investments or debt repayments, make a running tally of this year's Christmas spending.
Open AppleWorks and go to the File menu. Choose New>Spreadsheet. We're going to do some sums about how much our holiday for two will cost.
What are all these Lines and Boxes?
Once you've started a new spreadsheet, you see a work area which looks like a grid.
Your document contains columns labelled A, B, C and so on and rows labelled 1, 2, 3 and so on. The boxes where the columns and rows intersect are called cells.
Click in Cell A1 and in the top left corner of the window just above there, you'll see AppleWorks tells you you're in cell A1. A further clue is that the column name (the box containing the letter A) and the row name (where the number 1 is) are now grey.
Text Labels
A spreadsheet is all about doing things with numbers, but we need some text to help us remember what we're doing. We can enter text labels to make the numbers more useful.
In A1 enter a title like Annual Holiday (The typing shows up in the 'Entry Bar' a little to the right of where you saw the address for the cell.)
 |
We have some text labels in Cell A1 and in Cells A3 to A7 |
 |
Text labels are in Column A and numbers are in Column B |
Now, if you press the Tab key the words will go into the Cell and your cursor will move one cell to the right. If you press Enter or Return the data will be accepted and your cursor will move one cell down.
Let's leave Cell A2 blank, just to give us some breathing room.
Now we need to put labels in some more cells. Go to cell A3 and enter Package Cost which will be for the cost of the holiday package we choose. Now we need to enter other costs in cells A4, A5, A6 and A7.
A4 allows us to add the extra it costs to travel from Wellington. The other cells allow for Sundry Taxes, Vanuatu Departure Tax and Auckland Departure Tax.
Entering Numbers
Now we need to enter some figures beside the various costs.
Click in cell B3 (i.e. Package Cost) and enter 2698 then press Return. Notice how the text label Package Cost lines up with the left of the cell, while the number 2698 aligns to the right.
Now fill in 400, 72, 96 and 44 in Cells B4, B5, B6 and B7. You should now have a column of words on the left and a column of numbers on the right.
If this was all you could do with a spreadsheet you'd probably be thinking by now that a $5 calculator would be quicker and easier. It's only now though that we have enough data to start using the power of a spreadsheet. It can do our sums for us!
Now we want to find out the total cost so far. Let's leave some empty rows in case we think of other costs later (e.g. pet care, taxis to the airport, new swimming togs, sunscreen, cheap novels etc).
Click in A14 and enter the word 'total' (just for your reference).
In the cell B14 we want to see the total costs, so click in B14 and enter a formula which will make the computer add up all the numbers in rows 3 to 13.
Your formula starts with an equals sign: = (so press that)
Now put the word SUM, followed by a bracket: (
Next, put the address for the first cell: B3 . This is the first of all the numbers in consecutive cells you want to add.
Now put two full stops: .. (this tells the spreadsheet to use all the cells until the final one you specify).
 |
Cell A14 holds the word TOTAL, while B14 is where AppleWorks will show the total it has worked out. |
 |
This exploded pie chart will update dynamically if you change one of the numbers it uses |
After the two full stops put the address for the last cell in the range: B13. This is the last of all the numbers you want to add.
Close the brackets: )
The whole formula now looks like this: =sum(b3..b13)
Now press Return and voila: you have the sum of the cells B3 to B13.
Magic.
Still not impressed? Change the Sundry Taxes from 72 to 720 and watch the total automatically update. Change it back - that was just a data entry error!
Now use those empty rows to add your other holiday expenses.
Charts
Now we have some data in our spreadsheet we can start doing some really fun things like making graphs and charts, and watch the spreadsheet update your total each time.
Creating a chart
It would be interesting to see what share each cost contributed to the overall total. We can easily do this with a chart - perhaps a pie graph would quickly show us the proportions.
First you have to select the data you want to use, and then you tell a spreadsheet what kind of chart, and how it should present it. Be sure not to include the total or you'll be counting everything twice.
Start by selecting cells A3 to B13. Now go to the Options menu and choose Make Chart.
Let's select a Pie chart. There are all kinds of fancy things you can do, but we'll just stick to the basics for now. Just for fun though we'll check the Shadow box at the bottom of the window. On the left, click the Series button. Now click on the radio buttons for Label Data in Legend and Explode Slice.
On the left click the Labels button. Type a Title for your Chart in the Title area: Vanuatu Holiday.
On the left click the General button. Just look at the information.
Finally click OK.
Now you have a labelled chart which clearly shows the proportion of spending for each item. You can move the chart around by pointing to the middle and dragging it.
Now go to the cell showing Pet Care and add an extra zero - 3200 instead of 320 - and press Enter / Return. Notice how the chart changes automatically.
To remove the chart all together just click on it once and press the Delete key.
© Parkside Media 2002
For permission to use this document, email
This e-mail address is being protected from spam bots, you need JavaScript enabled to view it
 |
Subscribe to this comment's feed
Show/Hide comments
Show/Hide comment form
