Part One: Tutorial

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

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

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:

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:

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

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.

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

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

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.

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.

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

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

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

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

            |JAN  |FEB  |MAR  |APR  |F
        SALE|  184|  190|  117|   38|
        COST|  831|  821|  761|  721|
        PROF|     |     |     |     |
            |     |     |     |     |

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

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

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

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

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

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

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

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.

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.
• Select Grid from the Spreadsheet menu, then select Zap from the Grid menu.

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:

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.

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

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:

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.

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.

• Go to the Spreadsheet menu and select Grid.
• 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:

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.

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

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:

A cell range must be rectangular, 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.

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.)

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|        |

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.

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:

• Delete
• Print
• Save
• Load

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

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.

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.

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:

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:

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   |     |   |     |

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