BOOT CAMP 038

THE NUMBERS GAME

A couple of weeks ago in Boot Camp we touched very briefly on the topic of spreadsheets, it's time for a closer look. A spreadsheet is basically a smart calculator -- the classic number-crunching program -- they're aptly described as word processors for numbers, and they can be just as useful.

The suite of bundled software that came with your PC almost certainly included a spreadsheet, the best known being Lotus 1-2-3, Microsoft Excel and Claris Works. There are also simple spreadsheet functions in word processors, like Microsoft Word plus plenty of freeware and shareware programs available for the cost of a download on the Internet, so you have no excuse not to get to know this incredibly versatile application.

The best way to do that is to use one. We're going to show you how to create a simple home income calculator. The same basic techniques can be used to track the finances of a small business, club or society, generate a price list or even help to navigate an aircraft or boat, in fact any application where numbers interact with each other. Spreadsheets do not depend on the latest super fast processor chips or bucket-loads of memory, and once you've become familiar with the basics you can quickly adapt to using any spreadsheet on any computer, from a Sinclair Spectrum to an Apple iMac. 

In this example we'll be using MS Excel, it is included in the Office and Works software suites and sold separately. By sheer weight of numbers it is the one you're most likely to have, however, with only minor modifications our example will work with almost any other spreadsheet program.

The first thing you see when you open a spreadsheet program will be a blank table or grid. By convention vertical columns are labelled alphabetically, and rows numerically. By this means every square or 'cell' on the table has a unique identity code, i.e. the square in the top right left hand corner is A1, the one next to its right is B1, and so on. Begin by typing in the months of the year along the top row, starting in cell B1. Excel has a neat little trick to save you the effort of keying text or numbers that follow a logical sequence. When you've typed in the first month move the mouse pointer to the bottom left hand corner of the cell, where it changes to a black plus sign. Click and hold then drag the pointer along row 1, as it passes each column you'll see the month change. Stop when you come to M1 and a whole year's worth of correctly ordered months will be entered into the cells.

Now enter some expenditure headings into column A. You might want to make the column a tad wider to accommodate a line of text. In Excel put the mouse pointer into the shaded row of letters at the top and on to a column line, click hold and move the line slightly to the right. Obviously our headings are only a suggestion, put in as many or as few as you think necessary. When you have finished skip a row and put in something like 'Total Outgoings'. In the cell underneath -- A15 in our case -- type 'Income', and below that, in A16, 'What's Left'.

At his point you can begin to key in some figures. If any of your outgoings are fixed -- such as your mortgage, or any other standing orders -- type them in just once and use the logical sequence trick outlined above. This time it will repeat the same figure in each cell in a row. You can assign a pound sign to all of the cells containing numbers in Excel by highlighting the whole table in same way you highlight a block of text in a word processor. Put the mouse pointer into the top-left corner, click hold and drop in the bottom-right corner, then on the toolbar click on the currency icon (shaped like a banknote).

When you have entered all of the information click and highlight cell B14, (or whichever one relates to your Total Outgoings for the first month and click on the AutoSum button on the Toolbar (shaped like a Greek letter E). This adds together all of the numbers in the column above. Another way of doing that is to manually assign a mathematical expression to that cell. Highlight the cell and in the Formula window above the table, type in '=SUM (B3:B12)'. You're telling the spreadsheet to add together or sum all of the numbers in cells B3 to B12. This formula has to be inserted into every column in row 14, but again in Excel it is possible to do this in one step by clicking in the bottom left and corner of the cell and dragging it along the row. Excel automatically changes the column letter as it goes.

Finally, in cell B16, 'What's Left' insert the expression B15 - B14; this subtracts the total outgoings in B14 from the income in B15, to show how much is left. Repeat the formula in the relevant cells along the row as before.  The spreadsheet is now complete and all calculations are carried out automatically as you type in or change figures.

If you like you can instruct Excel to show negative numbers -- i.e. when your outgoings exceed your income -- in red. On the Format menu, click on Cells and select the Number tab and, click on Number in the Category window and highlight marked the red-coloured '-1234.10' entry in the Negative Numbers panel. The 'Cells' option on the Format menu also has the facility to add colours and shading to rows columns or blocks within the table. You'll find it on the Patterns tab, highlight the area you want to stand out first and choose a colour.

JARGON FILTER

CELL

A spreadsheet table is divided into boxes or cells, each of which is assigned a unique identity code. A cell can contain a mixture of text, numbers and mathematical formulae

FORMULA

Mathematical expressions, such as add, subtract, multiply and divide, used to create an instruction that tells a cell how to behave or process a piece of information

SHAREWARE             

Software programs that you can try, before you buy. If you continue to use it you are honour bound to send a payment to the author or publisher

TOP TIP

If you want to launch a screen-saver quickly -- maybe you're going out to lunch or prevent others from seeing what's on your screen -- open Windows Explorer, go to the Windows folder and open the System file. There you will find all of the Windows 95 screen-saver files. They're easy to spot as they have monitor-shaped icons and end with the file extension *.scr. Right click on the icon, select 'Send To' then 'Desktop as Shortcut', when you want to start it in a hurry just double-click on the desktop icon.