Part One: Tutorial

The Grid

The Display

Commands

The Grid

A Spreadsheet is an electronic version of the accountant's pad. The pad is divided into rows and columns in the form of a grid, labelled across the top with letters and down the left hand side with numbers.

On the Pocket Spreadsheet, the grid goes from column A across to column Z and from row 1 down to row 99. Only a number of these can be shown on the screen at any one time, but you can move around the grid to view any part of it. So imagine that the screen is like a window in a piece of card which you move up, down, left or right to see different areas of the grid. Each of the boxes in the grid is called a cell.
The cell which you are in at any given moment is the current cell.

To keep track of which cell is current, we use a cell reference like a grid reference on a map. The cell reference is made up of:

• The letter of the column
• The number of the row
in which the current cell lies. So the cell at the top left of the grid is cell A 1, and the cell to its right is cell B1.

When you start up the Spreadsheet, you enter the grid at the top left corner. The current cell is marked by a cursor bar:

 | B2  | B |C | | 2   | _ | |    or    | B2  | B |C    | | 3   | | |          | 2   | _ |     | | 4   | | |

To start with you are in cell B2. Cells are separated by dotted bars. The current cell reference is always shown in the top left cell in the display so that you know exactly where you are on the grid:

 | B2 |B     | C | | 2 |      | |    or    | B2 |B    | C    | | 3 |      | |          | 2 |     | | | 4 |      | |

The contents of cell A1 are then hidden; this is why you don't see A1 in this cell. The cells in the top row and leftmost column are title cells. They contain titles:

 | B2 |B     | C | | 2 |      | |    or    | B2 |B    | C    | | 3 |      | |          | 2 |     | | | 4 |      | |

The letters/numbers they show to begin with are just start-up values, so you can change them later to titles of your choice.

Moving around

Press the <right> cursor key once and the display looks like this:

```       |C2  |B     |C     |
|2   |      |_     |    or    |B2  |B    |C    |
|3   |      |      |          |2   |     |_    |
|4   |      |      |```

The cursor has moved to the right and cell C2 is now the current cell, as shown by the cell reference at the left of the top line.

Press <right> again and the screen scrolls one cell to the left, so that the new current cell (D2) comes onto the screen, on the right. The current cell reference is always displayed in the top left corner whatever you do.

• Press <right> and <down> a few times and watch the cell reference change as you go across and down the grid.

Now use the <up> and <left> cursor keys to return to the cell where you started from, B2, so that you can start entering data into the Spreadsheet.

Entering numbers and text

When you enter the Spreadsheet, the keyboard is in numeric mode by default. This means that you can type in numbers directly but have to keep the SHIFT key pressed if you want to type in text.

• To switch the keyboard to alpha (text) mode hold down SHIFT and press NUM. Do this again to return to numeric mode.

When you are in numeric mode, the cursor appears as a bar; in text mode the cursor appears as a flashing block.

• In cell B2, type:    44

As soon as you start to type the number, the rest of the line to the right of the cursor is cleared to accept your input.

• Press EXE to enter the number.

The full display is restored. 44 moves to the right of the cell; this is the way numbers are displayed.

• Press the <up> key to move down to cell B3 and type:     53 EXE
• Move to cell C3. Press SHIFT and NUM together to set the keyboard to alpha mode and type:      sum EXE

You can type this in lower or upper case, whichever you wish. To change between upper and lower case press SHIFT and <up> together.

When you press EXE, sum appears in the current cell. Because it is text rather than a number, sum is aligned left, not right, when accepted.

• Experiment with typing in some more numbers and text. in lower and upper case, to get used to the keys and to moving around the grid.

If you try to enter numbers followed by letters, e.g. 231 lbs. in a single cell, you get an error. This is because the Spreadsheet cannot accept this as a number. If you enter letters followed by numbers, e.g. NO.23, this will be accepted; the numbers are taken as a sort of text.

Alternatives while entering data

• Pressing one of the cursor keys without first pressing EXE moves you to the adjacent cell in the relevant direction, entering your data in the process. This is a timesaver, since you don't have to press EXE, wait for it to finish and then press a cursor key to move to the next cell.
• Press MODE whilst entering data. You can then use the cursor keys to move to any point in the data so far entered to edit it. Press MODE again to return to the usual function of cursor keys.

Deleting

• To correct a mistake whilst you're typing in, use DEL to delete the character to the left of the cursor and SHIFT-DEL to delete the character which the cursor is highlighting.
• To delete the entire contents of a cell just move to that cell with the cursor keys and press DEL once. Alternatively, move to the cell and type in new contents. The old contents aren't actually replaced by what you've typed until you press EXE; if you press ON/CLEAR twice instead of EXE the original contents are restored.

Numbers always appear at the right of the cell (‘ranged right’); text appears to the left of the cell ('ranged left'). The default column titles (A, B, C...) appear ranged left, but so do the row numbers (1, 2, 3 ... ) because these are a special case and are treated like text.

You are allowed to enter numbers or text in any of the title cells, too.

Go to cell B1 and enter the title TOTAL. Run down column B. The title is shown even when cell B1 has scrolled off the screen.

Note that, if you enter text which has trailing spaces, these are discarded by the Spreadsheet when you press EXE. Text which has leading spaces will be left as entered, however. This allows you to centre text for use as headers and so on.

If you want to clear what you've done completely and start afresh, press MODE EXE then Z then Y. (This procedure is explained later; at the moment use it whenever you want to start afresh.)

Entering formulae

Now you can add together the numbers entered with a formula. If you've changed the numbers in the top left corner of the grid, re-enter them so your grid looks like this:

```       |    |total|C    |
|2   |   44|     |
|3   |   53|  sum|
|4   |     |     |```

Formulae are typed into cells in just the same way as numbers and text, but when evaluated by the Spreadsheet their results are displayed, not the formulae themselves.

• Use the cursor keys to move to cell B4 and type:    =B2+B3  EXE

Whether you type in upper or lower case does not matter.

This is a formula which says: 'make the contents of the current cell (B4) become equal to the contents of cell B2 plus the contents of cell B3'. When you press EXE formula is evaluated and the display looks something like this:

```       |B4  |total  |C      |
|4   |_    97|       |    or    |B4  |total|C    |
|5   |       |       |          |4   |_  97|     |
|6   |       |       |```

The formula =B2+B3 is not visible at the moment. It is still stored in memory, but only the value it produces is displayed. The Pocket Spreadsheet contains commands to display and edit the formulae in the grid; but, because the formulae used in these first examples are quite straightforward, they can for the moment be left out of sight.

Extending a sum

The formulae you choose to enter into cells in the Spreadsheet might be very complex. But the principle by which a result is calculated in one cell by referring to data stored in other cells would be the same as in a simple example. A basic operation you would use is to change the entry in one cell and see how it effects the result in another cell. For example

• Go to cell B2 and type:    121  EXE
• Look at cell B4. The new result is displayed.

In effect, what you have done is to program the Organiser without using any conventional programming language. In this case you have created a worksheet which will add any two numbers and display the result; but you could have chosen a more complex mathematical function with more than two variables.

In the next example, the result of one sum is ‘called’ in order to work out the result of another.

Say we wanted to work out the amount of free cash left over for the month after paying the bills. First:

• Clear the grid by pressing MODE EXE Z Y.
• Go to each cell in this list and enter the text shown alongside:

A2  SAL
A3  GAS
A4  ELEC
A5  FOOD
A6  MORT
A7  CAR

• Enter these figures in the stated cells:

B2  850
B3  40
B4  30
B5  100
B6  420
B7  90

• Go to cell C2 and type:      =B2-B3-B4-B5-B6-B7  EXE

When you press EXE the values for all of the bills are deducted from the monthly salary and the result, the amount of free cash left over for the month, is inserted in cell C2. C2 should show 170.

But suppose we first have to calculate the car bill each month, from the amount spent on petrol, maintenance and paying back an original loan.

• Enter the following text and numbers in these cells:

C7  FUEL
C8  MAIN
C9  LOAN
D7  50
D8  20
D9  30

• Go back to cell B7. Type    =D7+D8+D9  EXE.

The completed worksheet will now look like this:

```       |SAL |BILLS|C    |D    |
|GAS |  850|  160|     |
|ELEC|   40|     |     |
|FOOD|   30|     |     |
|MORT|  100|     |     |
|CAR |  100|FUEL |   50|
|8   |     |MAIN |   20|
|9   |     |LOAN |   30|```

Cell B7 displays the result of the calculation of the car bill, but at the same time this is just a number in the calculation of the amount of free cash left at the end of the month.

• Change the number in D7, the fuel bill, to 60 then go to C2 to see how the total has changed.

This is an important characteristic of Spreadsheet cells: they can be both the end result of a calculation at one level and just one of many items in another calculation at a higher level.

In a real worksheet you might have several such levels, and in constructing the sheet it may be helpful to think of the calculation as broken down into a series of smaller calculations adding together to give the end result.

(NB: In the above example, you could change the number in cell B7, the car bill, and see the change registered in the result cell C2. But by changing the entry in cell B7 you would have deleted the formula which connected the sum of FUEL, MAIN and LOAN with the calculation of the cash left over at the end of the month. To reinstate the connection you would have to re-enter the formula.)

The Display

Just as the Organiser's Prog option has a menu, the Spreadsheet has its menu, containing various commands. You are not presented with this menu immediately you enter the Spreadsheet, but can reach It by pressing MODE whilst you are viewing the grid.

```           10.30
Grid File Goto        or     GRID FILE GOTO
Edit Info Titles             EDIT INFO TITLES
Quit                         QUIT```

These options are not all the same kind of command:

Grid and File lead to separate sub-menus. These will be dealt with in the next chapter.

Goto, Edit, Info and Titles enable you to change how you view the grid, and help alter its contents. These are the subject of this chapter.

 Goto Allows you to jump from one part of the grid to another. Edit Allows you to edit a cell's contents. Info Hides/displays cells' contents. Titles Hides/displays column and row titles. Quit Exits from the grid.

To start a new example we need to clear out the old. QUIT is one way to do this.

Quit - clearing the grid

• Select Quit by pressing Q. You are presented with this prompt:

DATA HAS CHANGED

This does not appear if:

• You have not yet entered data into the empty, default worksheet.
• If you have saved your worksheet and made no changes since then.

If you pressed N you would simply be returned to the old, uncleared grid.

• Press Y. You are presented with the Organiser top-level menu.
• Select Plan and you re-enter the Spreadsheet, but on a new grid.

When you confirm Quit, the current worksheet is irreversibly lost. quit discards the old grid from memory.

• To return to the Organiser top-level menu without deleting the current worksheet, just press ON/CLEAR while the grid is displayed. The current worksheet with all its data remains in memory, and you can return to it by selecting Plan at a later date.

When you do return to the Spreadsheet, you are even returned to the last cell you visited. This allows you to leave the Spreadsheet to consult the Diary or look up some other information, then return to the Spreadsheet and continue working just where you left off.

Example - using Info, Titles, Edit and Goto

• Enter this table:
```            |JAN  |FEB  |MAR  |APR  |F
SALE|  184|  190|  117|   38|
COST|  831|  821|  761|  721|
PROF|     |     |     |     |
|     |     |     |     |```
• Then enter these formulae:

B4 =B2-B3
C4 =C2-C3
D4 =D2-D3
E4 =E2-E3

Info

When you have entered the formula in cell E4, the display will look something like this:

```       |E4  |MAR   |APR   |
|SALE|   117|    38|    or    |E4  |MAR  |APR  |
|COST|   761|    72|          |PROF|  411|_ -34|
|PROF|   411|_  -34|```

So far you have been able to see only the results of formulae, not the formulae themselves. With the cursor in E4:

• Press MODE.
```       |E4  |MAR    |APR    |
|PROF|     41|_   -34|    or    |E4  |   41|  -34|
|5   |       |       |          |APR.PROF:=E2-E3 |
|APR.PROF:=E2-E3     |```

On the bottom line are:

• The formula you entered into cell E4 (=E2 -E3)
• The title reference of the cell, made up of the contents of the title cells at the head of column E and the leftmost cell of row 4 (APR.PROF)

The bottom line shows different things for different cells.

• Move up and across the grid to see how the bottom line of the display changes as the content of the current cell changes.

The title reference is always shown. If there is no formula in a cell, the cell's contents are shown as a literal string - a blank (e.g. in C5), a number (e.g. in C3) or a piece of text (with an opening ‘ " ’ to identify it as text - e.g. in C1).

If the details on the bottom line of the display are longer than can fit on the screen, they will scroll from right to left.

Titles

At the moment, you have all the aspects of the cell displayed: numeric or text content, formulae, current cell reference, title reference. It is possible to display a combination of these by selecting the Info and Titles commands.

Both of these act as switches - on or off:

• If Info is 'on' (cell contents are displayed), selecting Info from the Spreadsheet menu will turn it 'off (hiding the formulae).
• When the title reference of the current cell is displayed, selecting Titles will redisplay the current cell without its titles.

Your display may thus have any of the formats below.

• Experiment with Info and Titles to get them on your screen.

Titles on, Info off (default state)

```       |E4  |MAR   |APR   |
|SALE|   117|    38|    or    |E4  |MAR  |APR  |
|COST|   761|    72|          |PROF|  411|_ -34|
|PROF|   411|_  -34|```

Titles on, Info on (select Info)

```       |E4  |MAR    |APR    |
|PROF|     41|_   -34|    or    |E4  |   41|  -34|
|5   |       |       |          |APR.PROF:=E2-E3 |
|APR.PROF:=E2-E3     |```

Titles off, Info on (select Titles)

```       |E4  |     41|_   -34|
|    |       |       |    or    |E4  |   41|  -34|
|f   |       |       |          |=E2-E3          |
|=E2-E3              |```

Titles off, Info off (select Info again)

```       |E4  |     41|_   -34|
|    |       |       |    or    |E4  |   41|  -34|
|    |       |       |          |f   |     |     |
|f   |       |       |```

(f indicates that the current cell contains a formula.)

In all display modes the current cell reference is displayed in the top left corner of the screen.

The default mode when you enter the Spreadsheet has titles displayed, cell contents hidden.

Edit

• Go to cell E4 and set the display mode to give this screen:
```       |E4  |MAR   |APR   |
|SALE|   117|    38|    or    |E4  |MAR  |APR  |
|COST|   761|    72|          |PROF|  411|_ -34|
|PROF|   411|_  -34|```
• Press MODE and select Edit from the Spreadsheet menu. The bottom line of the screen shows this:

=E2-E3_

You can now edit the line. Use:

• <left> and <right> to move the cursor
• DEL to delete the character to the left of the cursor bar
• SHIFT-DEL to delete the character which the cursor bar is highlighting.

You can edit only one cell at a time, the one that was current at the time you selected Edit.

You cannot use the <up> and <down> keys to go to an adjacent cell to alter its contents. When you are altering a cell, <up> and <down> have the function of moving the cursor to the beginning (<up>) or the end (<down>) of the string you are editing.

• Press EXE to enter the edited formula into cell E4, to which you are then returned.

Alternatively, you could press ON/CLEAR to clear the edited formula altogether and press ON/CLEAR again to return to the grid with the cell's original formula intact - cancelling the Edit operation.

Edit is most useful when the formula entered in a cell would be long and complex to retype completely. For example, imagine you should have typed V, not H, in this formula:

=INT(HLOOKUP(G7,G8:L16))*10

Edit saves you having to retype the whole formula for the sake of one character.

You can also use Edit to edit text and numbers in cells not containing formulae.

Goto

In a real worksheet you might need not just to step from cell to cell but to jump from, say, C4 to T83. You could just use the cursor keys, but a quicker method is the Goto command.

Say you are now in cell E4.

• Press MODE and select Goto. The bottom line of the display now shows:

Goto B2

- with a cursor on 'B'. B2 is the suggested cell to which you are going to jump. In most cases B2 is the default suggestion.

To accept the suggestion press EXE. Your display will then show the top left corner of the grid, with B2 the current cell. To return to cell E4:

• Press MODE again and select Goto.
• Type e4 EXE.

The keyboard is automatically in alpha mode, so you will have to press SHIFT to enter the number part of the cell reference. As soon as you start typing, the suggested cell reference disappears. Use the cursor keys and DEL to correct.

Commands

The commands available to you in the Spreadsheet are ordered into three menus, as shown below.

To get familiar with the layout of these menus, step through them In the sequence suggested, using just the ON/CLEAR, MODE, EXE and cursor keys.

The File menu is explained fully in the Menu Reference. Its commands concern storing/retrieving worksheets.

The Grid menu, containing commands to help construct a worksheet, is the subject of this Chapter:

 Copy Copies the contents of one range of cells to another range. Delete Deletes the contents of a range of cells. Zap Clears all data from the grid. Format Sets the numeric display format of a range of cells. Widths Sets the column width for a range of cells. Print Prints out the values of a range of cells. Recalc Sets the formula recalculation mode.

This Chapter uses a worked example to familiarise you with the uses of these menu items. If you want to leave the example temporarily, just press ON/CLEAR to return to the top-level menu. When you select Plan again you return to where you left off.

Zap - clearing the grid

To start our new example the grid must be cleared. Last time you used the Quit command. Zap is the same as Quit except that it returns you to the empty grid, not to the Organiser top-level menu.

From the grid:

• Press MODE.

If you have entered data since last quitting the worksheet you will be asked to confirm the command. Pressing N returns you to the Grid menu.

• Press Y. You return to an emptied worksheet with its default display settings:
```       |B4  |B      |C      |
|2   |       |       |    or    |B4  |B    |C    |
|3   |       |       |          |2   |     |     |
|4   |       |       |```

Example - using Widths, Format and Copy

Set up the following table to work out the value of a portfolio of shares from the day's price-per-share. The value of each group of shares will be calculated and displayed in the sub-total column, and this column will be added and the overall total displayed in cell E3.

```       |    |QTY  |TODAY|SUB-T|TOTAL|F
|PORT|     |     |     |     |
|BT  | 1000| 2.37|     |     |
|TSB |  500|  0.9|     |     |
|GAS |  200| 0.75|     |     |
|BA  |  200|  1.1|     |     |```
• In cell E3 enter the formula:     =D3+D4+D5+D6.

For the moment-the result will of course show as zero, since the cells in column D are empty.

Before entering further data it is necessary to prepare the grid.

Widths

At the moment, the columns on your grid are each 7 characters wide on model L4 or 5 characters wide on model XP (apart from the first column, which is narrower). What if the total value of your portfolio was more than 9999.99, say? Your worksheet couldn't currently display any amount greater than this.

Assuming that the total might reach 10000.00, we need to have column E 8 characters wide. if the total was 12950.00 and the cell was only 7 characters wide, the result would show only as '#######'.

To change the column width:

• Press MODE, and select Grid.
• In the Grid menu select Widths. The bottom line of the display shows: Widths E:E with a cursor flashing on the first E (or whatever the letter is of the column you were in).

You are being prompted to select a range of columns whose width you wish to change. If we wanted to set columns C, D and E we would want to change E:E to C:E. You can do this in two ways.

• Experiment with both methods:

(a) Type in C: E - the suggested range disappears as soon as you press C. Pressing ON/CLEAR clears the line and pressing ON/CLEAR again restores the suggested range. Press EXE to enter the range. The display then suggests a width to which to set the chosen columns. You have a choice:

• To accept the suggested width press EXE.
• To change the width, type in the new one. Use DEL to correct if necessary. Press ON/CLEAR to clear what you've typed. (Pressing ON/CLEAR again returns you to the Grid menu.) Press EXE to enter the width.

(b) Use <left> and <right> to move across the cells just as if you were scanning across the grid in the usual way. Continue pressing the cursor keys until the letter to the left of the colon is that of the leftmost column you wish to effect - C. Then press MODE and use the cursor keys again to change the letter to the right of the colon to that of the rightmost column you wish to effect - E.

If you then want to go back and correct the left hand letter press MODE and then the cursor keys again. Pressing ON/CLEAR moves you back to E:E, or whatever column you were in when you selected Widths. Press EXE to enter the range. The display then prompts as above for the width to which to set the chosen columns.

The maximum width is 12 characters.

• For our example set D:E to 8 characters wide.
• Then select Widths a second time and set B: C to 5 characters wide.

Format

The figures in the portfolio table are monetary, so we need to set the numbers in the relevant columns to appear with two decimal places: 0.90, 1.10.

• From the Grid menu select Format. A sub-menu is presented, offering you a choice of the types of cells you wish to effect:
• Cells affects only a certain area of cells, which you specify. In this area, or 'range', only cells which have contents are affected. (If there are some empty cells in this range and you later enter data into them, the data will be displayed with the Spreadsheet's default format, not the one you selected.)
• Default sets all the empty cells in the grid, but none which have contents. Data later entered into the empty cells will be displayed with the selected format.

We want to set the format of the cells in columns C to E (TODAY, SUB-T and TOTAL). Some of these are empty so must be set with Default. Others already have contents, so must be set with cells. To set the format of the empty cells in column D:

• Select Default.

A menu is presented of types of numeric format:

General Integer
Fixed Scientific

These four types of format are defined in the Reference. For our example:

• Select Fixed.

You are asked for a number of decimal places. The suggested setting is 2, which is what we want for our monetary figures, so we don't need to alter it in the usual way - just:

• Press EXE to switch the suggested format on.

The cells in column D are now prepared to show sub-totals in monetary form.

Now to change the format of the cells in column C:

• Press MODE. Select Grid, then Format, then Cells.

As with the widths command, you are now prompted to define the range of cells you wish to effect. This time, though, the range asked for is not just a matter of columns, but also of rows.

Defining a Cell Range

Say you were in cell E3 when you selected Format. The bottom line of the display would then show:

Format E3:E3

with a cursor flashing on the first E on the lower line.

The area we need to select is the BT, TSB, GAS and BA cells in the TODAY column. That is, C3:C6 - which says 'C3 to C6’. This is a cell range. The figure below shows another range, C3:E6 - the rectangular area which has C3 and E6 as its top left and bottom right corners:

 B C D E F 2 3 4 5 6 7

A cell range must be rectangul
ar, but this could be a single row, a single column (as in our example) or a single cell. In the figure below there are three cell ranges: B2:B9, D2:G2 and D4:D4.

 B C D E F G H 2 3 4 5 6 7 8 9 10

Cell ranges are used in several of the Grid menu commands, so it is important to understand them.

To change the cell range you can use either of the methods you used to change the Widths range - i.e. either:

• Type in C3:C6 (or whatever range you want). Press ON/CLEAR to clear the line and ON/CLEAR again to restore the suggested range. Press EXE to enter the range.
• Or: Use the cursor keys and MODE to change each side of the colon. Note that you can now use <up> and <down> as well as <left> and <right> to change the number and letter parts of the cell reference. Press ON/CLEAR to return to the suggested range. Press EXE to enter the range.
• Experiment with the cursor keys and MODE to obtain different cell ranges.

When the range is C 3: C 6 press EXE.

The menu of types of numeric format is presented.

• Select Fixed. then 2 decimal places.

You are returned to the grid. Run down column C, TODAY, and see that the share prices are now in monetary form: 2.37, 0.90, 0.75, 1.10.

• Now set the format of cell E3 to 2 decimal places, so that the TOTAL will also display correctly. (Because it contained a formula, it wasn't affected by the Default format change.)

Copy

To work out the value of the BT shares in the sub-total column:

• Enter in cell D3 the formula =B 3 * C 3. Press EXE and the result, 2370.00, is displayed.

The sub-total for each of the other shares must be worked out using exactly the same kind of formula: =Bx*Cx where x is the number of the row.

To save typing in the formula four times over, copy the formula in cell D3 into cells D4, D5 and D6:

1. Check that D3 is the current cell. Press MODE, select Grid and then select Copy from the Grid menu.
2. You are asked to select a cell range to be the model to copy from. The suggested range is simply the cell that was current when you selected Copy - D3:D3. The range can be edited in the usual way, but for this example just press EXE.
3. You are then asked to select the cell range to which the model cell(s) are to be copied. For this example the required range is D4:D6. Select this with the cursor keys and MODE or by typing it in, then press EXE.

You are returned to the finished worksheet, which should look like this:

```       |    |QTY  |TODAY|SUB-T   |TOTAL   |F
|PORT|     |     |        |        |
|BT  | 1000| 2.37| 2370.00| 3190.00|
|TSB |  500|  0.9|  450.00|        |
|GAS |  200| 0.75|  150.00|        |
|BA  |  200|  1.1|  220.00|        |

```

Relative and Absolute Cell References

This has completed the example worksheet. But to see how the Copy command works:

• Press MODE and select Info to show formulae.
• Run down the sub-total column and see what has been copied: =B3*C3, =B4*C4,=B5*C5, =B6*C6.

Why hasn't =B3*C3 been copied exactly into cells D4 to W.

If every character of the formula =B3*C3 had been copied exactly, it would have been an absolute cell reference.

A relative cell reference is one where only certain parts of the formula are copied exactly and others are modified In order to maintain the relationship which the original formula defined.

For example, you put the formula =B3*C3 into cell D3; i.e. the cell containing the formula was given the product of the contents of the two cells to Its left. When you copied this cell to the rest of the cells in that column, the same relationship was kept: all the cells display the product of the contents of the two cells to their left.

Or say a formula in cell X (below) contains a reference to the lightly shaded cell. If this formula is copied to cell Y it then refers to the heavily shaded cell. The two cells in each pair have the same relative positions.

 B C D E 2 3 X 4 5 Y 6

By default the Spreadsheet assumes that cell references are relative.

To make a cell reference absolute:

• Prefix the letter and/or number part of the cell reference with a dollar sign, \$.

For example, if a formula in cell E5 (Y in the example above) refers to \$D\$4 and you copy this formula to cell C3 (M that too will refer to cell D4, not to B2. (And if you copied cell C3, the reference to D4 would be copied ,again as being to D4.)

(Actually, when you copy a formula you don't create another modified formula in memory. All cells to which the formula has been copied share the same formula. The formula is stored only once, and contains a note of whether the cell references in it are absolute or relative.)

You can mix absolute and relative cell references within a single cell reference - for example \$C3, C\$3 (see Example 1).

There follow two further example worksheets. The first of these uses mixed cell references, the second some of the Spreadsheet functions listed in the Functions Reference.

Most of the processes involved in building a worksheet have been outlined in this chapter, but for further details of all the commands and settings see the reference section.

Commands which you might like to look up now are:

You will certainly need to be familiar with the last two commands when you start creating your own worksheets.

Example 1: Copying formulae containing mixed cell references

This worksheet calculates the prices of a number of commodities in five different currencies. Each day you can enter the exchange rate for the various currencies based on a standard currency, here the US dollar. Then you enter today's price of each of the commodities in dollars, then go to the appropriate cell to see the price of, for example, Silver in Yen.

This whole example can be built with just one formula which is copied to all the necessary cells: the price is multiplied by the exchange rate to give the price in the particular currency. The formula uses mixed cell references.

1. Clear the grid with Zap.
2. Set columns B to F to eight characters wide with Widths (from the Grid menu).
3. Set all cells to 2 decimal places using Format then Default then Fixed (from the Grid menu).
4. Enter these titles, exchange rates and commodity prices:
5. ```       |    |DOLLAR  |STERLING|YEN     |FRANC   |DMARK   |
|RATE|        |    0.64|  154.00|    6.11|    1.84|
|GOLD|  406.25|        |        |        |        |
|SILV|    5.47|        |        |        |        |
|COPP|  484.37|        |        |        |        |
|TIN | 1414.00|        |        |        |        |```
6. Now enter the formula =C\$2*\$B3 into cell C3 (Sterling. Gold). The result, 260.00, is displayed. The formula takes the exchange rate (in cell \$2) from the top of the current column (cell C, not \$C) and multiplies it by the commodity price (cell \$13) at the left of the current row (cell 3, not \$3).
7. This formula is appropriate for all the price cells, so Copy the formula from C3: C3 to C3: F 6.
8. When you press EXE a 'Busy...' message is displayed while all the various prices are calculated.

Then the worksheet should look like this:

```       |    |DOLLAR  |STERLING|YEN     |FRANC   |DMARK   |
|RATE|        |    0.64|     154|    6.11|    1.84|
|GOLD|  406.25|  260.00|   62563| 2482.19|  747.50|
|SILV|    5.47|    3.50|     842|   33.42|   10.06|
|COPP|  484.37|  310.00|   74593| 2959.50|  891.24|
|TIN | 1414.00|  904.96|  217756| 8639.54| 2601.76|```

The unnecessary decimals in the YEN column have been hidden. To do this:

• Select Format, then Cells. Select D2:D6 and then Integer.

This has completed the worksheet.

• Change the day's rates or prices and see the relevant change in the grid.

Recalc

But say you wanted to change all the day's exchange rates and commodity prices. Then the grid would spend time recalculating every time you press EXE (or an arrow key) to enter a new price or rate. Whereas the grid could calculate just once, after all the new data has been entered.

To do this, change the recalculation mode:

• Press MODE. Select Grid, then Recalc.

You are presented with a menu:

Manual Automatic

BY default the grid calculates automatically.

• Select Manual.

Now when you enter a formula, though it is evaluated immediately, any formulae in other cells referring to this cell are not recalculated. To make the whole grid recalculate, press EXE a further time.

Note: Whilst prices are displayed only to two decimal places, they may be entered to an accuracy of more than two decimal places. The precise figures you type am stored by the Spreadsheet and used in the calculations, then rounded up for display.

If you want to save this worksheet:

• Press MODE and select File. Select Save and give the worksheet a name. Press EXE. Enter a password if you wish and press EXE. This is explained fully on page 6-12.

Two simple improvements could be made to the 'monthly bilIs' example by using Spreadsheet functions.

• Use the SUM function to add up the expenditures, instead of typing in the cell reference of each and every expense cell.
• Use the MONTH, YEAR and TODAY functions to display the current date at the top left of the grid.

Set up this table:

```       |    |BILLS|REM|SAL  |E
|    |     |   |  950|
|MORT|  425|   |     |
|FOOD|  200|   |     |
|ELEC|   70|   |     |
|CAR |  100|   |     |
|7   |     |   |     |```
• In cell C2 enter the formula      =D2-SUM(B3:B6).

The SUM function simply adds up the contents of the cells in the selected range (B3:C6). Quoting a cell range like this is obviously an advantage when you have a large number of cells to add up.

• In cell A2 enter the formula    =MONTH(TODAY)+YEAR(TODAY)/100

This displays the current month number and year number, separated by a dot (provided you have set the time with the Time option from the top-level menu).

The formula uses 3 Spreadsheet functions:

The TODAY function returns the number of days elapsed today since 30.12.1899.

When this number is given to the MONTH function, MONTH returns the integer from 1 to 12 representing the month in which that day number lies.

Similarly, the YEAR function returns an integer from 0 to 178, representing the year after 1899 in which that day number lies.

In the formula, the number returned by YEAR is divided by 100 in order to make it .89 or whatever. Then when this is added to the result of MONTH, the month and year will be separated by the decimal point.

The finished worksheet looks something like this:

```       |    |BILLS|REM|SAL  |E
|5.89|     |155|  950|
|MORT|  425|   |     |
|FOOD|  200|   |     |
|ELEC|   70|   |     |
|CAR |  100|   |     |
|7   |     |   |     |```

Two improvements to the date display

In May 1990, say, the date will show as 5.9, not 5.90. So use Format with Cells to make A2 to display to 2 decimal places.

When the year is after 2000, the YEAR function would mourn a value over 100. This, when divided by 100, would wrongly add 1 to the month number. To prevent this, change the formula in cell A2 to

=MONTH(TODAY)+MOD(YEAR(TODAY),100)/100

The MOD function divides the number returned by YEAR by 100 and then returns the remainder of this division. E.g. in 2012 YEAR returns 112, but MOD returns 12.

For a full explanation of these and all the other available Spreadsheet functions, see the Function reference.

 These pages should be viewed using Netscape 4.03 or Microsoft Internet Explorer 3.02 at 800x600 pixels. Introduction ~ Homepage ~ General Reference