This is the revised 1989 edition of the Pocket Spreadsheet Manual for the Psion Organiser II LZ and LZ64. The 1987 edition for the XP Organiser is available in English and French.
© Copyright Psion PLC 1989.
All rights reserved. This manual and the programs referred to herein are copyrighted works of Psion PLC, London, England. Reproduction in whole or in part, including utilisation in machines capable of reproduction or retrieval, without the express written permission of Psion PLC is prohibited. Reverse engineering is also prohibited.
The information in this document is subject to change without notice.
Psion and the Psion logo are registered trademarks of Psion PLC. Psion Organiser II, Datapak and Rampak are traddemarks of Psion PLC.
All references in this manual to Macintosh are to the Apple Macintosh range of computers. Apple and Macintosh are registered trademarks of Apple Computer Inc. All references in this manual to PCs are to the IBM PC, IBM XT and IBM AT, which are registered trademarks of International Business Machines Corp. DIF is a trademark of Software Arts Inc. Lotus, 1-2-3 and Symphony are registered trademarks of Lotus Development Corp.
V1(Mar89)
Part No. 6100-0072
Introduction Part One: Tutorial 1 The Grid 2 The Display 3 Commands Part Two: Reference 4 General Grid layout Input Display modes Cell references Relative and absolute cell references Calculation 5 Functions 6 Menu Items The Spreadsheet menu The Grid menu The File menu A The OPL function Index
The Psion Pocket Spreadsheet can be thought of as a large electronic sheet of paper. This sheet is divided into a grid of boxes, rather like a sheet of graph paper.
This simple arrangement, when transformed by the processing power of the Organiser, forms perhaps the most versatile and powerful tool you can add to your computer.
The information produced on your Pocket Spreadsheet is not just for you alone; it can be shared with colleagues. Worksheets produced on the Organiser can be transferred to a desk-top computer to run in Lotus 1-2-3 or Symphony, and worksheets prepared in Lotus 1-2-3 or Symphony (or any other spreadsheets which accept .DIF extension files) can be transferred just as easily to the Organiser.
The Pocket Spreadsheet gives you the power and flexibility of spreadsheets costing many times as much, together with the portability and ease of use of a pocket calculator.
The Psion Pocket Spreadsheet does not run on the Psion Organiser Model CM.
In the same box as this manual is a program pack. Its external appearance is identical to that of Datapaks, which are discussed in the Organiser manual. To insert the pack:
The program pack may be used in either of the side slots.
The Psion Pocket Spreadsheet requires around 8K of memory, but if large worksheets are to be used then more free memory will be required.
To run the Spreadsheet:
Straight away you will see the top left corner of the grid. The 'tutorial' chapters which form Part One explain where to go from here. However, if you are already familiar with spreadsheets, you can go straight to the reference chapters, which contain a summary of the particular features of the Psion Pocket Spreadsheet.
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:
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 |_ | | |3 | | | |4 | | | | or | |B2 |B |C | |2 |_ | | |
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:
| |B2 |B |C | |2 | | | |3 | | | |4 | | | | or | |B2 |B |C | |2 | | | |
- 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:
| |B2 |B |C | |2 | | | |3 | | | |4 | | | | or | |B2 |B |C | |2 | | | |
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 → cursor key once and the display looks like this:
| |C2 |B |C | |2 | |_ | |3 | | | |4 | | | | or | |B2 |B |C | |2 | |_ | |
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 → 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.
Now use the ↑ and ← 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.
When you are in numeric mode, the cursor appears as a bar; in text mode the cursor appears as a flashing block.
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.
The full display is restored. 44 moves to the right of the cell; this is the way numbers are displayed.
You can type this in lower or upper case, whichever you wish. To change between upper and lower case press SHIFT and ↑ 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.
If you try to enter numbers followed by letters, e.g. 23lbs, 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 literally - as a sort of text.
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.
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.)
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.
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| | |5 | | | |6 | | | | or | |B4 |total|C | |4 |_ 97| | |
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.
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
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:
A2 SAL
A3 GAS
A4 ELEC
A5 FOOD
A6 MORT
A7 CAR
These are your titles.
B2 850
B3 40
B4 30
B5 100
B6 420
B7 90
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.
C7 FUEL
C8 MAIN
C9 LOAN
D7 50
D8 20
D9 30
The completed worksheet will now look like this:
|BILLS|C |D |E SAL | 850| 160| | GAS | 40| | | ELEC| 30| | | FOOD| 100| | | MORT| 420| | | 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.
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.)
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 Edit Info Titles Quit | or | GRID FILE GOTO EDIT INFO TITLES 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.
DATA HAS CHANGED Discard Y/N
This does not appear if:
If you pressed N you would simply be returned to the old, uncleared grid.
When you confirm Quit, the current worksheet is irreversibly lost. Quit discards the old grid from memory.
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.
|JAN |FEB |MAR |APR |F SALE| 184| 190| 117| 38| COST| 83| 82| 76| 72| PROF| | | | | 5 | | | | |
B4 =B2-B3
C4 =C2-C3
D4 =D2-D3
E4 =E2-E3
When you have entered the formula in cell E4, the display will look something like this:
| |E4 |MAR |APR | |SALE| 117| 38| |COST| 76| 72| |PROF| 41|_ -34| | or | |E4 |MAR |APR | |PROF| 41|_ -34| |
So far you have been able to see only the results of formulae, not the formulae themselves. With the cursor in E4:
You return to the grid with the display now showing:
| |E4 |MAR |APR | |PROF| 41|_ -34| |5 | | | |APR.PROF:=E2-E3 | | or | |E4 | 41| -34| |APR.PROF:=E2-E3 | |
On the bottom line are:
The bottom line shows different things for different cells.
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.
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:
Your display may thus have any of the formats below.
Titles on, Info off (default state)
| |E4 |MAR |APR | |SALE| 117| 38| |COST| 761| 72| |PROF| 411|_ -34| | or | |E4 |MAR |APR | |PROF| 411|_ -34| |
Titles on, Info on (select Info)
| |E4 |MAR |APR | |PROF| 41|_ -34| |5 | | | |APR.PROF:=E2-E3 | | or | |E4 | 41| -34| |APR.PROF:=E2-E3 | |
Titles off, Info on (select Titles)
| |E4 | 41|_ -34| | | | | |f | | | |=E2-E3 | | or | |E4 | 41| -34| |=E2-E3 | |
Titles off, Info off (select Info again)
| |E4 | 41|_ -34| | | | | | | | | |f | | | | or | |E4 | 41| -34| |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.
| |E4 |MAR |APR | |SALE| 117| 38| |COST| 761| 72| |PROF| 411|_ -34| | or | |E4 |MAR |APR | |PROF| 411|_ -34| |
You can now edit the line. Use:
You can edit only one cell at a time, the one that was current at the time you selected Edit.
You cannot use the ↑ and ↓ keys to go to an adjacent cell to alter its contents. When you are altering a cell, ↑ and ↓ have the function of moving the cursor to the beginning (↑) or the end (↓) of the string you are editing.
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.
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.
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:
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.
The commands available to you in the Spreadsheet are ordered into three menus, as shown overleaf.
The Spreadsheet menu is discussed in Chapter 2.
The File menu is explained fully in the Chapter 6. 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. |
| 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.
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:
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.
| |B4 |B |C | |2 | | | |3 | | | |4 | | | | or | |B4 |B |C | |2 | | | |
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| | |
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.
At the moment, the columns on your grid are each 7 characters wide on model LZ 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:
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.
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:
Use ← and → 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 affect - 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 affect - 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.
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.
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:
A menu is presented of types of numeric format:
General Integer Fixed Scientific
These four types of format are defined in reference Chapter 6.. For our example:
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:
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:
As with the widths command, you are now prompted to define the range of cells you wish to affect. 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:
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 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.
| 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:
The menu of types of numeric format is presented.
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.
To work out the value of the BT shares in the sub-total column:
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:
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:
Why hasn't =B3*C3 been copied exactly into cells D4 to D6?
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:
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 (X) 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 opposite).
There follow two further example worksheets. The first of these uses mixed cell references, the second some of the Spreadsheet functions listed in the Chapter 5.
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. Relevant page numbers are given in the index.
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.
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.
| |DOLLAR |STERLING|YEN |FRANC |DMARK |G |RATE| | 0.64| 154.00| 6.11| 1.84| |GOLD| 406.25| | | | | |SILV| 5.47| | | | | |COPP| 484.37| | | | | |TIN | 1414.00| | | | | |7 | | | | | |
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 $B) at the left of the current row (cell 3, not $3).
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:
This has completed the worksheet.
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:
You are presented with a menu:
Manual Automatic
By default the grid calculates automatically.
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 are stored by the Spreadsheet and used in the calculations, then rounded up for display.
If you want to save this worksheet:
This is explained fully on page 6-12.
Chapter 1 contained a 'monthly bills' example. Two simple improvements could be made to the finished worksheet by using Spreadsheet functions.
Set up this table:
| |BILLS|REM|SAL |E | | | | 950| |MORT| 425| | | |FOOD| 200| | | |ELEC| 70| | | |CAR | 100| | | |7 | | | |
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.
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 return 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 Chapter 5.
1 Grid layout 2 Input 3 Display modes 4 Cell references 5 Relative and absolute cell references 6 Calculation
The Spreadsheet grid is 26 columns wide (labelled A to Z) by 99 rows deep (1 to 99). The columns are initially:
The width of columns may be set to between 1 and 12 characters (6 for column A). Rows are one cell deep (not adjustable).
A title is the contents of a cell in column A or in row 1. If these cells contain no data they are given the default display values A, B, C... across the top of the grid or 1, 2, 3... down the left of the grid. These are always displayed ranged left.
These values are for display purposes only - the cells are empty and, unless you enter numbers yourself, will evaluate to 0.0 if accessed by a formula elsewhere in the grid.
You can replace the default titles with text or numbers of your choice.
To suppress the titles which are displayed when the Spreadsheet is first run:
To restore a title suppressed in this way:
To enter data:
The Spreadsheet categorises your input according to the first character of the string entered: as a number, a formula or a text string. Input is regarded as text unless:
Numerical expressions not beginning with = are evaluated and stored as a number.
The Spreadsheet will not accept 2TEXT as an entry because it mixes categories, but it will accept TEXT2, allowing you to number text strings. You can enter 2TEXT as text by entering "2TEXT. The " acts as an identifier - it is not displayed.
If a number outside these ranges is entered in a cell, either directly or through a formula, that cell will be filled with asterisk characters (*). Other cells which reference such a cell will also be filled with asterisks.
If the numeric format of a cell is set such that not all the decimal places of the number in it are shown, the number shown is the number rounded to the nearest next digit. E.g. if a cell is set to display only two decimal places and the contents of that cell is the number 1.237, the value 1.24 will be displayed.
Numbers are displayed ranged right, with the exception of the title numbers in column A (and row 1 if you enter numbers in these cells).
If a numeric string is longer than the cell width, it is displayed only as a series of # signs. Use the Widths command to reset the width of the cell so that the number can be displayed. For example, a cell containing the value 9887.00 is in a column set to only six characters wide. The cell displays '######' until the width of the column is set to 7 or more characters.
When not all of a cell is visible on screen and the cell contains a number, a → sign is shown to indicate that number is not displayed in full.
The maximum permitted length of any text string stored in a cell is 250 characters. If a text string is longer than the cell width, it overflows into adjacent cells to the right (except for titles).
Text is always displayed ranged left. If a formula references a cell containing text, that cell is evaluated to 0.0.
The ' " ' symbol is a flag indicating that a cell's contents are regarded as text. It appears when the display is in 'Info on' mode or when a cell is being viewed under the Edit command.
To delete the contents of a single cell:
To delete the contents of a range of cells:
To delete the contents of all cells and reset the column widths and numeric formats to their default states:
To edit a numerical or text entry while typing it in:
To edit data already entered:
(See also 'Defining a cell range', Chapter 6.)
Four display modes are available, controlled by two 'switches': the Titles command and the Info command in the Spreadsheet menu. The four modes are:
This is the default display mode, in which the screen typically looks like this:
| |D5 |MAR |APR | |PROF|_ 354| 128| |6 | | | |7 | | | | or | |D5 |MAR |APR | |PROF|_ 354| 128| |
Here the current cell is D5, and it contains the display 354. The cell at the top of the current column contains the title 'MAR'; the cell at the left of the current row contains the title 'PROF'.
From the default setting, select the Info command so that any formula in the current cell can be seen on the bottom line, for example:
| |D5 |MAR |APR | |PROF|_ 354| 128| |6 | | | |MAR.PROF:=D3-D4 | | or | |D5 |_ 354| 128| |MAR.PROF:=D3-D4 | |
To the left of the bottom line is a title reference made up of the titles in the cell at the top of the current column and the cell at the left of the current row. Following this is the formula in the current cell.
The first two modes both feature the titles from the top of the current column and the left of the current row. To switch the titles off, select the Titles command. In the example above, the display now shows:
| |D5 |_ 354| 128| | | | | |*fc | | | |=D3-D4 | | or | |D5 |_ 354| 128| |=D3-D4 | |
If the current cell contains a formula, this mode shows the formula on the bottom line of the display. Otherwise, any numerical or text entry in the current cell is shown on the bottom line. On model LZ the flags '*', 'f' or 'c' may be shown (see below).
While the titles are switched off, use the Info command again to remove the formula shown on the bottom line. The display now shows:
| |D5 |_ 354| 128| | | | | | | | | |*fc | | | | or | |D5 |_ 354| 128| |*fc | | | |
The current cell, D5, contains the display value 354. On the bottom line is a status area which can show up to three 'flags'.
The flags which can appear in the status area are as follows:
| * | The Spreadsheet has been modified since the last calculation. This will be shown only after the contents of a cell have been altered when the Spreadsheet is in Manual recalculation mode (see Recalc). To ensure all displayed values are valid, recalculate the worksheet by pressing the EXE key when the grid is being displayed. |
| f | The current cell contains a formula. If you set Info on the formula will be shown on the bottom line of the display. |
| c | The current Spreadsheet contains one or more circular references. (See 'Circular References' at the end of this chapter.) |
As you use the cursor keys to scroll from cell to cell, the right hand area of the display will show the contents of the current and surrounding cells, and the left hand side will change to show the current cell reference and the status of that cell.
The flags '*', 'f ' and 'c' always appear on the display in the order '*fc' (though all three may not necessarily be present at one time). They may be generated in any order, depending on the contents of the grid or the current cell.
A reference to a single cell consists of two parts: a column and a row reference. Typical cell references are:
B2 G15 Z86
A range reference is made up of two cell references, separated by a colon. You must always type in the colon to separate the two parts of the reference. The first cell reference specifies the top left hand corner of the block and the second the bottom right hand corner. Examples of range references are:
B5:D9 G22:M80
A part of a row or column can be considered as a range that is only one column wide or one row deep. You can therefore use a range reference to specify part of a row or column, such as:
A3:L3 (cells A to L of row 3) D7:D11 (cells 7 to 11 of column D)
The Spreadsheet assumes that, unless otherwise specified, all cell references are relative, i.e. that the difference in position between the cell containing the reference and the cell to which it refers is the important thing.
When you copy such a reference into another cell, the references are modified to keep this relative difference.
For example, imagine that a formula in cell C3 contains a reference to cell B2 (one column to the left and one row above). If the formula in cell C3 is copied into cell E5 it will, in this new location, refer to cell D4 (one column to the left and one row above).
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 make any cell reference absolute by prefixing the letter for the column or the number for the row, or both, with a dollar sign ($). Such a reference will not be modified when the formula is copied to other cells.
For example, if a formula in cell D3 refers to cell $B$2 and you copy this formula to another cell, that too will refer to cell $B$2.
The two types of cell references, absolute and relative, may be mixed freely, giving references which are part absolute and part relative. For example:
$C3 B$4 $R33:Y$34
A formula may consist of allowed combinations of functions, cell references, numbers and arithmetic operators.
Formulae must start with an equals character. They may be up to 250 characters long. Examples are:
=B6 =B6+SIN(0.87) =AVG(B8:C15) =IF(B2=B3,TRUE,FALSE)
If any two formulae in the worksheet are identical, just one copy of the formula is stored and this is shared. The two cells each refer to the one formula. This means that worksheets which contain large numbers of identical formulae are very economical in terms of the Organiser's memory.
| + | add |
| - | subtract |
| * | multiply |
| / | divide |
| ** | raise to the power |
| - | unary minus (make negative) |
| + | unary positive (make positive) |
| > | greater than |
| >= | greater than or equal to |
| < | less than |
| <= | less than or equal to |
| = | equal to |
| <> | not equal to |
NOT
AND
OR
The operators have the following precedence. The precedence numbers merely indicate whether an operator has precedence higher, lower or equal to another operator:
| Operator | Meaning | Precedence |
| ** | power | 7 |
| - | (Unary minus) make negative | 6 |
| + | (Unary plus) make positive | 6 |
| * | multiply | 5 |
| / | divide | 5 |
| + | add | 4 |
| - | subtract | 4 |
| = | equal to | 3 |
| > | greater than | 3 |
| >= | greater than or equal to | 3 |
| < | less than | 3 |
| <= | less than or equal to | 3 |
| <> | not equal to | 3 |
| NOT | logical NOT | 2 |
| AND | logical AND | 1 |
| OR | logical OR | 1 |
Where precedence is equal, calculation is from left to right. Use brackets to override the order of precedence.
The Pocket Spreadsheet supports natural order recalculation, in which formulae which do not depend upon other formulae are recalculated first, followed by formulae which rely on them and so on.
The default recalculation mode is Automatic. This may be changed to Manual with the Recalc command in the Grid command menu. The worksheet can always be recalculated by pressing the EXE key when the grid is displayed.
The presence of a circular reference means in its simplest sense, that the contents of a cell is dependent on its own contents - for example, if cell B2 contains the formula =B2+1.
A circular reference consisting of two cells might appear like this: cell B2 contains the formula =D2+1, and cell D2 contains the formula =B2+1.
Circular references can be far more complex than these simple examples and lead from cell to cell all around the Spreadsheet, finally returning to the cell from which they started. These will always be detected by the Spreadsheet and indicated with the 'c' flag in Titles off/Info off display mode.
The presence of a circular reference is usually an indication that you have made a mistake. In certain cases you may need a circular reference in your application, but note that the values of cells involved in the circle may then alter undesirably when the worksheet is recalculated; their values will mount each time the sheet is recalculated.
When a circular reference has been detected, the default destination cell offered in the Goto command will always be one of the cells contributing to the circular reference.
Note that, if the recalculation mode is Manual, the circular reference indicator 'c' may be unreliable. The Spreadsheet is checked for circularity, and the circularity indicators are updated, only when the worksheet is recalculated. Manually recalculating the worksheet by pressing EXE when the grid is displayed will update the status indicators.
Any of the functions listed here may be included in cell formulae. A function takes the parameters supplied it and returns a value to the appropriate point in the formula.
Functions must be supplied with the correct number and type of arguments. If not, a 'syntax error' will be indicated when you try to enter the formula into a cell.
The arguments must be enclosed in brackets and separated by commas.
Arguments may be literal values or expressions. In the descriptions of the functions:
| <exp> | is either a numeric expression or a reference to a cell displaying a numeric value (the numeric contents of the cell are taken for the operation). |
|---|---|
| <range> | is a cell range in the format B2:G12. |
| <list> | is a list of cell references, cell ranges and numeric expressions, separated by commas, for example B2, D3:F6, 33. |
Any other argument will be either a numeric expression or a reference to a single cell displaying a numeric value.
Example formulae using functions are:
=AVG(B2:G17) =LOG(12) =LOG(B2)
The functions available in the Pocket Spreadsheet are these:
| FUNCTION | VALUE RETURNED |
|---|---|
| ABS(<exp>) | Absolute value of <exp> |
| ACOS(<exp>) | Arc cosine of <exp> |
| ASIN(<exp>) | Arc sine of <exp> |
| ATAN(<exp>) | Arc tangent of <exp> |
| ATAN2(<exp1>,<exp2>) | Arc tangent in correct quadrant |
| AVG(<list>) | Arithmetic mean of parameters in <list> |
| CHOOSE(<exp>, <list>) | Indexed parameter from <list> |
| COS(<exp>) | Cosine of angle <exp> |
| COUNT(<list>) | Number of non-blank cells in <list> |
| DATE(year,month,day) | Returns a day number |
| DAY(<exp>) | Day of the month (1 to 31) |
| ERR | Generates an error value |
| EXP(<exp>) | e to the power of <exp> |
| FALSE | Returns false (0.0) |
| FV(payment,interest,term) | Future value of annuity |
| HLOOKUP(<exp>, <list>,offset) | Horizontal lookup table |
| IF(x,then,else) | True/false value dependent upon x |
| INT(<exp>) | Integer of <exp> |
| IRR(guess, <list>) | Internal Rate of Return |
| ISERR(<exp>) | Tests for ERR |
| ISNA(<exp>) | Tests for NA |
| LN(<exp>) | Natural logarithm of <exp> |
| LOG(<exp>) | Logarithm of <exp> |
| MAX(<list>) | Maximum value in <list> |
| MIN(<list>) | Minimum value in <list> |
| MOD(<exp1>,<exp2>) | Modulo of x/y |
| MONTH(<exp>) | Number of month (1 to 12) |
| NA | Value interpreted as not available |
| NPV(interest, <list>) | Net present value |
| OPL(cell,<list>) | Calls OPL procedure |
| PI | Mathematical constant Pi |
| PMT(principal,interest,term) | Mortgage payments |
| PV(payment,interest, term) | Present value |
| RAND | Random number (0 to 1) |
| ROUND(<exp1>,<exp1>) | Rounds <exp1> to given number (<exp2>) of decimal places |
| SIN(<exp>) | Sine of angle <exp> |
| SQRT(<exp>) | Square root of <exp> |
| STD(<list>) | Standard deviation of items in <list> |
| SUM(<list>) | Sum of items in <list> |
| TAN(<exp>) | Tangent of angle <exp> |
| TODAY | Number of days elapsed since 30th Dec 1899 |
| TRUE | Returns true (1.0) |
| VAR(<list>) | Variance of items in <list> |
| VLOOKUP(<exp>,<list>,offset) | Vertical lookup table |
| YEAR(<exp>) | Number of year given a day number |
A full description of each of these functions follows in alphabetical order.
FV=<payment>*((1+<interest>)**<term>)- 1 /<interest>)
The interest rate is the period interest rate and is expressed in decimal form, i.e. 14% is expressed as .14.
For example, in this worksheet:
| |B |C |D |E |F |2 | 1| 2| 3| 5| |3 | 14| 15| 16| 17| |4 | 18| 19| 20| 21| |5 | 22| 23| 24| 25| |6 | | | | |
| Formula | Result |
|---|---|
| =HLOOKUP(1,B2:E5,3) | 22 |
| =HLOOKUP(1.4,B2:E5,3) | 22 |
| =HLOOKUP(3,B2:E5,2) | 20 |
| =HLOOKUP(4,B2:E5, 1) | 16 |
| =HLOOKUP(5,B2:E5,2) | 21 |
If the value of <offset> is negative or greater than or equal to the number of rows in the range then ERR is returned.
For example, in the next worksheet, the formula =IRR(B3,C3:C8) would return .06402. The interest rate is returned as a decimal, i.e. 14% would be returned as .14.
|B |C |D 2 |Guess|Range| 3 | 0.5|-1500| 4 | | 360| 5 | | 360| 6 | | 360| 7 | | 360| 8 | | 360|
An iterative method is used to calculate the result, and if convergence to a valid result does not occur within 20 iterations, the result ERR is returned. The interest rate returned by IRR is the period interest rate, where that period is the fixed interval between the cash flows. If you require the interest rate for a different period, you can convert the value returned by the IRR function.
Converting one period interest rate to the equivalent interest rate for a different period is not simply a matter of multiplying by the time factor.
For example, a monthly interest rate of 2% is not equivalent to an annual rate of 24%. The correct equation is based on the statement that equivalent interest rates produce the same discount after the same time. A cashflow which occurs after two years must have the same discount as a cash flow which occurs after 24 months.
The formula to convert one period interest rate (i1) into another (i2) is:
i2 = (1+i1)**(t2/t1)-1
where t1 and t2 are the corresponding periods, in the same units.
For example, if i1 is the annual rate and i2 is the monthly rate, then t1 is 12 months and t2 is 1 month, and the formula is:
i2 = (1+i1)**(1/12)-1
Conversely, If i1 is the monthly rate and i2 is the annual rate, the formula is:
i2 = (1+i1)**12 - 1
Using this equation, we can calculate that a 2% monthly rate is equivalent to a 26.8% annual rate. (The results are displayed as a decimal, for example 0.02; 0.268.)
In the UK, the APR (Annual Percentage Rate) is a standard way of quoting interest rates so that consumers can compare interest rates offered by different companies. The IRR function may be used to derive the APR provided the result is converted, if necessary, to an annual rate.
The first cash flow is taken to occur at the end of the first period, and blank cells in the <range> are permissible. Subsequent cash flows are taken to occur at the end of subsequent periods.
For example, in the next worksheet, an initial payment of 4700 is followed by five receipts of 1600 at an interest rate of 14%. The formula =NPV(B2,C3:C7) would return the net present value of 5492.93 (to two decimal places). If you then subtract the initial payment you have the profit of 792.93 on the investment.
|B |C |D 2 | 0.14|-4700| 3 | | 1600| 4 | | 1600| 5 | | 1600| 6 | | 1600| 7 | | 1600| 8 | | |
The interest rate should be given as a decimal, i.e. 14% should be given as .14.
The OPL function adds two facilities to the Spreadsheet:
For further details of the OPL function, see Appendix A, The OPL Function.
The interest rate should be given as a decimal, i.e. 14% should be given as .14.
|B |C |D 2 |Paymt| .95| 3 |Int | .1| 4 |Term | 12| 5 | | |
the formula =PV(C2,C3,C4) returns the value 647.30.
The interest rate should be given as a decimal, i.e. 14% should be given as .14.
| Formula | Result |
|---|---|
| =ROUND(26858.30458,-3) | 27000 |
| =ROUND(26858.30458,-1) | 26860 |
| =ROUND(26858.30458,0) | 26858 |
| =ROUND(26858.30458,1) | 26858.3 |
| =ROUND(26858.30458,2) | 26858.3 |
| =ROUND(26858.30458,5) | 26858.30458 |
Note that the zero in the second decimal place when the second parameter to ROUND is 2 isn't stored, nor displayed unless Format is set to 2 decimal places.
For example, in this worksheet:
|B |C |D |E |F 2 | 1| 14| 18| 22| 3 | 2| 15| 19| 23| 4 | 3| 16| 20| 24| 5 | 5| 17| 21| 25| 6 | | | | |
these are the values returned:
| Formula | Result |
|---|---|
| =VLOOKUP(1,B2:E5,3) | 22 |
| =VLOOKUP(1.4,B2:E5,3) | 22 |
| =VLOOKUP(3,B2:E5,2) | 20 |
| =VLOOKUP(4,B2:E5,1) | 16 |
| =VLOOKUP(5,B2:E5,2) | 21 |
If the value of <offset> is negative or greater than or equal to the number of columns in the range then ERR is returned.
1 The Spreadsheet menu 2 The Grid menu 3 The File menu
When the grid is being displayed, press the MODE key to view the Spreadsheet command menu. This menu contains the following items:
| Grid | Enter the Grid command menu. |
|---|---|
| File | Enter the File command menu. |
| Goto | Go to another cell in the grid. |
| Edit | Edit the contents of the current cell. |
| Info | Switch between hiding/displaying formulae. |
| Titles | Switch between hiding/displaying titles. |
| Quit | Leave the Spreadsheet and free the memory used by it. |
Two Items in this menu, Grid and File, each lead to a sub-menu of commands when selected. These are detailed in sections 2 and 3 o this chapter. The remainder of the commands in the Spreadsheet menu have the following uses:
Puts the display into insert mode. The contents of the current cell - or, if a formula has been entered, the formula which controls the current cell - can then be altered on the bottom line of the display.
Edit can only be used to edit one cell at a time - the cell that was current at the time the command was selected.
Moves you to another cell in the grid. When selected, the bottom line of the display shows the prompt:
Goto: B2
The default cell when titles are displayed is B2; otherwise the default is A1. If there is a circular reference in the current worksheet the default offered is one of the cells which contribute to the circular reference.
Control the Spreadsheet's four display modes (see reference Chapter 4).
The Titles command switches between Titles on and Titles off.
The Info command switches between Info on and Info off.
Exits the Pocket Spreadsheet and frees all memory used by it. If the current worksheet has been modified since it was last saved, the Spreadsheet requests confirmation before execution of the command.
When executed, the effects of this command cannot be reversed. So ensure that the current worksheet can safely be discarded before selecting Quit.
To return to the Organiser top level menu without deleting the whole of the current worksheet, just press ON/CLEAR while the grid is displayed. The current worksheet and all data will remain in memory and you can return to it at a later date. When you return to the Spreadsheet with a worksheet in memory, you are returned to the last cell you visited.
When the Grid command in the Spreadsheet menu is selected a further menu is displayed, containing the following items:
| Copy | Copies the contents of one range of cells to another range. |
|---|---|
| Delete | Deletes the contents of a range of cells. |
| Format | Sets the numeric display format for a range of cells. |
| Prints out the values of a range of cells. | |
| Recalc | Sets the formula recalculation mode. |
| Widths | Sets the column width for a range of columns. |
| Zap | Clears all data from the grid. |
These commands are concerned with processes which can be effected on the grid itself or on the contents of the grid. The menu items Delete, Format, Widths, Print and Copy all prompt in the same way for one or more ranges of cells on which to operate:
First the bottom line of the display shows the name of the command selected, followed by two copies of the current cell reference. For example, if the Delete command is selected when the current cell is H16, the bottom line of the display shows:
Delete: H16:H16
with the cursor flashing over the first 'H'. The two cell references represent the top left cell and the bottom right cell of a rectangular area of the grid. This is the range of cells which will be affected by the command. To change the range either:
Pressing ON/CLEAR while you are editing the range clears your edit and returns it to what it was when the command was first selected - in this example:
Delete: H16:H16
You can now begin altering the cell range again, as described above, or press ON/CLEAR to abandon the command and return to the grid.
The commands in the Grid command menu have the following uses:
Deletes a range of cells from the grid. When Delete is selected the Spreadsheet prompts for a range of cells.
When the cells in the range indicated have had their contents deleted, there is no way to retrieve them. Use this command with care.
Sets the numeric display format for either a specified range of cells or for all blank cells in the grid.
When Format is selected, a two item menu is displayed, containing the items Default and Cells.
Once the cell range has been entered if you select Cells, or immediately if you select Default, this four-item menu is displayed:
General Integer Fixed Scientific
When Fixed or Scientific are selected, the bottom line of the display prompts for the number of decimal places. In both cases, the default 2 is offered and the maximum allowed is 6.
When Integer or General are selected, the display returns immediately to the grid with that numeric display format in effect.
Sets the column width for a range of columns. Only whole columns may be affected; you cannot set, for example, cells 1, 2 and 3 in row F to be 5 characters wide and the rest of column F to be 7 characters wide.
Once you have selected the cell range that the width command is to affect, the bottom line of the display prompts for the new column width.
Any width from 1 to 12 may be entered (6 is the maximum for column A). The default cell width is 7 characters on model LZ and 5 characters on model X
Prints the current worksheet to a printer, one page column at a time; i.e. a worksheet would be printed page by page in this order:
| 1 | 2 | 3 | 4 |
When Print is selected, if there is no data in the grid (i.e. you have just started the Spreadsheet or used the Zap command) you are returned immediately to the grid. Otherwise, a two-item menu is presented:
After selecting either of these, you are prompted for a page width. The default width is 80 characters.
The selected range of cells (or the whole worksheet) is then printed, with a form feed character printed at the end of each page of cells.
The Print command will operate only when a Comms Link cable or Psion Printer II is connected to the Organiser. If neither of these is connected, a DEVICE MISSING error will be displayed. You will then be returned to the grid.
Copies a range of cells to another position in the grid. When Copy is selected, the command prompts for the source cell range and then the destination cell range.
Both display values and formulae are copied to the destination range.
Formulae containing absolute cell references are copied with the same absolute cell references intact. Formulae containing relative cell references are copied with a new relative cell reference modified from the original (see 'Relative and absolute cell references', reference Chapter 4).
Wherever two identical formulae appear in the worksheet, only one formula is stored, and all cells referring to it share the one copy. The Organiser always makes the most efficient use of memory that it can.
If the size of the destination range is greater than the source range, the source range is replicated within the target range for as many complete copies as will fit in that range.
If the source range and destination range overlap, the data will still be copied across cell by cell. This means that some data may be overwritten accidentally.
For example, in the worksheet below there would be a one-cell overlap if the source range was B2:C4 and the destination range was C4:D6
|B |C |D 2 | 1| 2| 3 | 3| 4| 4 | 5| 6|
The Copy command works across the first row, then the next and so on.
So in this example, B2 is copied to C4. However, this is in the source range too, and the value which was there has now been deleted. The next four cells, C2, B3, C3 and B4 are copied without mishap. The final cell, which previously held the value six, has been given the value 1, and this is what gets copied to cell D6.
So the original range of cells with the values:
1 2 3 4 5 6
has now become:
1 2 3 4 5 1
with the bottom right hand cell corrupted.
Sets the recalculation mode. The two-item menu Manual and Automatic is presented.
Clears all data from the worksheet and allows you to begin entering new data. If the current worksheet has been modified since it was last saved, you will be prompted to confirm that you want to delete all data.
Ensure that all important data is saved to a device before using this command, as it cannot be reversed.
All the commands in the File menu are concerned with the transfer of worksheets to/from:
The File menu contains the following items:
| Load | Load a worksheet file. |
|---|---|
| Save | Save a worksheet file. |
| Import | Import a worksheet file from a PC or Macintosh. |
| Export | Export a worksheet to a PC or Macintosh. |
| Dir | Read the directory of worksheet files. |
| Erase | Erase a worksheet file. |
Load replaces any currently displayed worksheet with the new worksheet. So if the current worksheet has been modified since it was last exported or saved, you are prompted to confirm that you want to load in a new file. Press N to abort, or Y to continue the Load.
The file name must be up to 8 alphanumeric characters, starting with a letter. (On model LZ you can press EXE as usual to get a directory list.)
On completion, you are returned to the grid with the loaded worksheet displayed.
The file name must be up to 8 alphanumeric characters, starting with a letter. If the file name already exists on the current device, you are asked to confirm whether or not to delete the old worksheet.
Once you have entered the file name, you are prompted to add a password.
Remember that if you do add a password you will have to enter it each time you wish to load this worksheet.
When you've entered the file name and/or password, if you get an OUT OF MEMORY message, change to another device by pressing MODE - or abandon the Save.
If you get a BATTERY LOW message, press the SPACE key and then ON/CLEAR to leave the Spreadsheet, with your worksheet still intact in memory. Change the battery as explained in your Organiser manual. Then return to the Spreadsheet and Save the worksheet as planned.
On completion of the Save, you are returned to the grid. It is best to save worksheets on a Rampak, or a PC or Macintosh, since:
The saved worksheet remains current until you use Zap, Quit, Load or Import. It is recommended that you Save worksheets and then Quit, rather than just ON/CLEAR out of the Spreadsheet leaving the current worksheet taking up internal memory.
Reads the directory of saved worksheets on the internal memory of the Organiser or on a Datapak or Rampak. When Dir is selected the display shows:
Dir A:
Select Erase and the display shows:
Erase A:
(On model LZ, press EXE as usual to get a directory list.)
The file is deleted and you are returned to the grid.
When you select either Import or Export:
Otherwise an error is reported and you are returned to the File menu.
When you select Import or Export, you are presented with a menu of spreadsheet file types. This is the type either:
The following file types are available:
DIF - Data Interchange Format (an option on most spreadsheet packages) - plain text files containing the evaluated results of formulae at the time the file was saved.
WKS - files from Lotus 1-2-3 Release 1 or 1A only
WK1 - files from Lotus 1-2-3 Release 2 only
WR1 - files from Lotus Symphony Release 1. 1 only
In .WKS, .WK1 and .WR1 files, formulae are transferred intact, provided that:
(Note for Apple Macintosh users: when exporting to or importing from a Macintosh, an extra file type is added to the menu: .WKT. This is because spreadsheet packages on the Macintosh produce file types 'BINA' or 'TEXT'. .WKS produces a 'BINA' file. If you find you can't load this file, try selecting type .WKT; this produces a file of the same format but of the type 'TEXT'. The same restrictions on formulae apply as with the .WK1, .WR1 and .WKS files.)
Include the pathname, or folder, if desired. Use the / character in place of the DOS backslash \. If you are connected to a Macintosh, use / to separate folder from file name.
E.g. to import the file SALES.WKS which is in the SPREAD directory or folder, select WKS from the Import menu and type:
/SPREAD/SALES EXE
You don't have to Include the file extension, provided that you select the correct file type from the menu.
If you don't include a file extension in the file name when Exporting, the default extension for the selected file type (.WKS or whatever) is automatically added.
When the Import is complete you are returned to the grid with the worksheet displayed.
N.B. Imported worksheets must not cover a range greater than 26 columns wide and 99 rows long. Cells outside this range are discarded. Cell width settings are discarded during Import. Some columns may therefore need their widths altered after the file has been transferred.
When the Export is complete you are returned to the grid. Cell width settings are discarded during Export, so some columns may need their widths altered in the spreadsheet on your other computer after the file has been received.
At the end of the Import or Export process, if any errors have occurred:
ERRORS FOUND - not more than 15 errors have
occurred.
TOO MANY ERRORS - more than 15 errors have
occurred.
SERVER FILE ERROR - the other computer failed to
open or write to a file.
LINK FAILED - the link between the computers failed,
e.g. if the cable wasn't properly connected or the
Comms Link program wasn't running.
It is recommended that you use this function only when you have a sound working knowledge of OPL (Organiser Programming Language) and the way in which values are passed back from OPL procedures. Refer to your Organiser manual for further details.
NB. Formulae containing the OPL function cannot be Exported. Each cell referring to the function will display =ERR on the other computer.
The OPL function adds three facilities to the Spreadsheet:
The OPL function is entered into a cell as a formula. Then when the grid is recalculated, in order to work out this formula the grid calls a named procedure, which then returns a value to the cell in which the formula was entered.
The OPL function has this syntax:
OPL(<cell>,<range>)
<cell> is the reference of a cell containing an OPL procedure name.
The procedure name must be a valid one, excluding the trailing colon. The procedure itself:
<range> is a cell range whose contents are used as the input values of the procedure named in <cell>. The cells:
Even if <range> contains only one cell, <range> must still be a fully specified range, in the format B2:B2.
When the current worksheet is recalculated, the procedure named in <cell> takes as its input value the value contained in the first cell in <range>. The procedure runs as normal, then returns a value to <cell>. Then, if there is another cell in <range>, the value in this cell is used as the input value, and the new result returned to <cell>. This continues until the end of <range>.
Two new functions are added to OPL while the Spreadsheet is installed in the Organiser: CELL: and CELL$:.
These functions pass to the procedure named in <cell> the value contained in the first cell in <range>, then in the second and so on until the end of the range. They ignore blank cells.
The values in the cells in <range> are accessed by the named procedure from left to right and from top to bottom, i.e. in the order:
| 1 | 2 |
| 3 | 4 |
Once the contents of one cell have been 'grabbed' from the Spreadsheet, an internal pointer is set to the next non-blank cell in the range.
Unless trapped with OPL's ONERR command, these errors are reported in the usual way, the procedure ends and the OPL function in the Spreadsheet returns the value ERR.
When the OPL function is used in a Spreadsheet, the procedure name(s) present in the grid call their procedures every time the worksheet is recalculated. You should therefore set the recalculation mode to Manual with the Recalc command before setting up the worksheet.
The share portfolio worksheet set up in Chapter 3 can be improved by using the OPL function to:
This means that you can find out the result you designed the Spreadsheet to calculate, but without having to actually see and move around the grid itself.
A first procedure, called ASK:, prompts for a price for each of the shares held and inserts that price into the appropriate cell in the worksheet. Then a second procedure, called TELL:, 'grabs' the grand total from the cell in which it has been worked out and prints it to the screen.
Here is the original worksheet:
|QTY |TODAY|SUB-T |TOTAL |F PORT| | | | | BT | 1000| 2.37| 2370.00| 3190.00| TSB | 500| 0.90| 450.00| | GAS | 200| 0.75| 150.00| | BA | 200| 1.10| 220.00| |
Either load this worksheet if you had originally saved it, or enter it afresh if you hadn't (see tutorial example for full details). Then:
ASK: LOCAL a PRINT "Todays value of" PRINT CELL$:;":"; INPUT a RETURN a TELL: PRINT "Todays total" PRINT "=",CELL: GET
The ASK: procedure will be called from each cell in turn where the price of a share was entered in the original worksheet. The number you then enter (INPUT a) is entered into the cell which called the procedure. This needs to be a different cell for each share price, so:
When you press EXE the formula is evaluated. Enter today's price of the BT shares, as asked:
Todays value of BT:_
The OPL function has as its parameters:
To make the grid automatically tell you today's total:
This function performs the procedure named in cell F3 (TELL:) on the contents of the single-cell range E3:E3 - i.e. the cell displaying the folio total. When you press EXE to enter the formula, the screen tells you the total value of your shares, like this:
Todays total = 3190
To improve this worksheet, make sure that the folio total returned by TELL: is a rounded figure. This is necessary because, though cell E3 displays the result to only two decimal places, it stores it to more than two - and it is the stored number which the TELL: procedure takes and displays on the screen. It would be inappropriate to show a result of more than 2 decimal places.
This takes the total calculated in cell E3 and rounds it to two decimal places.
so that TELL: takes the rounded value in cell E4, not the full value in E3.
A ABS function Absolute cell ref. ACOS function Alignment Alpha (text) mode Arithmetic operators ASIN function ATAN function ATAN2 function Automatic recalculation AVG function B BAD PASSWORD error Battery, low C Cell Current Defined Reference setting width Cell range copying selecting CELL$: function CELL function Cells option (Format) CHOOSE function Circular references 'c' flag defined Column as a cell range defined maximum width setting width titles Command menus File Grid illustration Spreadsheet Commands Copy Delete Dir Edit Erase Export Format Goto Import Info Load Print Quit Recalc Save Titles Widths Zap Comparison operators COS function COUNT function Cursor keys editing formulae editing range selection moving round grid D Data entry Datapaks saving and loading DATE function DAY function Decimal places Default option (Format) Delete command Deleting DEVICE MISSING error DIF files Dir (directory) command Display default mode E Editing a range selection formulae numbers and text Edit command Erase command ERR function Errors imported spreadsheets OPL printing ERRORS FOUND error Examples BILLS COMMODITIES DATE FUNCTIONS OPL PORTFOLIO PROFIT EXP function Export Command F FALSE function File menu File names File types (Export/Import) Fixed numeric format Flags Format command Formulae 'f' flag copying defined displaying editing with Edit entry in relative/absolute cell ref. maximum length Functions ABS ACOS ASIN ATAN ATAN2 AVG CELL$: CELL: CHOOSE COS COUNT DATE DAY ERR EXP FALSE FV HLOOKUP IF INT IRR ISERR ISNA LN LOG MAX MIN MOD MONTH NA NPV OPL PI PMT PV RAND ROUND SIN SQRT STD SUM TAN TODAY TRUE VAR VLOOKUP YEAR FV function G General numeric format Goto command Grid clearing with Quit clearing with Zap layout Grid menu H HLOOKUP function I IF function Import command Info default mode displaying formulae Info command INT function Integer numeric format IRR function ISERR function ISNA function K Keyboard modes L LINK FAILED error LN function Load command LOG function Logical operators Lotus files Low battery M Manual recalculation MAX function Menu items see 'Commands' Menus File Grid illustration Spreadsheet MIN function Mixed cell ref. MOD function MODE enter Spreadsheet menu change use of arrow keys MONTH function Moving around N NA function NPV function Numbers accuracy alignment displayed as ##### numeric format range allowed O Operators arithmetic comparison logical precedence OPL, errors OPL function OUT OF MEMORY error P Passwords Pathnames PI function PMT function Precedence Print command PV function Q Quit command R Rampaks saving and loading RAND function Range cell range ref. copying a cell range selecting Recalc command Recalculation '*' flag Automatic and Manual Relative cell ref. ROUND function Row as a cell range defined titles S Save command Scientific numeric format Screen scrolling Selecting a cell range SERVER FILE ERROR error SIN function Spaces, leading and trailing Spreadsheet menu SQRT function Status area STD function Suggested cell range SUM function T TAN function Text alignment deleting editing entry Titles defined display value removing/displaying suppressing default titles Titles command TODAY function TOO MANY ERRORS error TRUE function V VAR function VLOOKUP function W Width imported spreadsheet cells maximum column width WK1 files WKS files WR1 files Y YEAR function Z Zap command