Cell contents = cell contents + 1
This Pocket Spreadsheet Manual for the Psion Organiser II XP is also available in French and in Spanish. There is also a revised 1989 edition for the LZ and LZ64 Organisers.
Introduction 1 The Grid 2 The Display 3 Using The Commands 4 Building A Worksheet 5 Reference 6 Spreadsheet Functions 7 Spreadsheet Commands Appendices
Copyright Psion Limited 1987
All rights reserved. This Pocket Spreadsheet manual and the programs referred to herein are copyrighted works of Psion Limited, London, England. Reproduction in whole or in part, including utilisation in machines capable of reproduction or retrieval, without the express written permission of Psion Limited is prohibited. Reverse engineering is also prohibited.
REGISTERED DESIGN APPLICATION NUMBERS 1019736 & 1019737
Manufacturer's Warranty And Limitation Of Liability
To avoid any possible damage to your Organiser, Program
Packs and Datapaks should not be exposed to extremes of
temperature or humidity. Do not subject them to hard knocks
or excessive force, nor immerse in liquids or use volatile fluids
when cleaning the case.
If within 12 months of purchase this product can be shown to the reasonable satisfaction of Psion to be faulty and not to function substantially as described in the user manual, Psion will (at the option of the purchaser) refund the purchase price or replace the product. Apart from this warranty, Psion will not in any event be liable for any loss, including consequential loss, caused by any error, defect or failure of the computer, or howsoever otherwise arising, including but not limited to loss of use, loss of stored data, loss of profit or loss of contracts.
This limitation of liability shall not apply to consumer transactions nor to any liability for death or personal injury.
Written by Barry Thomas
Typeset by Lotus Reprographic Services, London
Printed by Premier Metropolis, London
Lotus is a trademark of Lotus Development Corporation
1-2-3 is a trademark of Lotus Development Corporation
Symphony is a trademark of Lotus Development Corporation
DIF is a trademark of Software Arts Inc
The Organiser 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.
The boxes on this electronic sheet are called cells, and are arranged in rows and columns. When data is entered in these cells we create a worksheet.
This simple arrangement, when transformed into computing terms forms perhaps the most versatile and powerful tool you can add to your computer.
The information produced on your Organiser 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 can be transferred just as easily to the Organiser!
The Organiser 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 Organiser Pocket Spreadsheet does not run on the Psion Organiser Model CM.
In the same box as this manual you will find a program pack for your Psion Organiser II. The external appearance is identical to that of Datapaks, which are discussed in the main Organiser operating manual. It plugs into the Organiser in exactly the same way.
Remove a Datapak or a cover by pulling on the ribbed outer section of the pack. Now insert the program pack in the empty slot. The program pack may be used in either of the side slots.
The Organiser 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 Organiser Spreadsheet, press the ON/CLEAR key to switch on your Organiser and, if necessary, press the ON/CLEAR key a number of times until the top-level menu appears. Press the ON/CLEAR key once more and the name PLAN will have automatically been added to the end of the top- level menu.
To run the spreadsheet, select PLAN from the main menu in the usual way - by moving the cursor to that item with the cursor keys, or by pressing P, and press EXE.
Straight away you will see the top left corner of the grid. The following chapters explain where to go from here. However, if you are already familiar with spreadsheets, you can go straight to the reference chapter which contains a summary of the particular features of the Organiser 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. Each of the boxes made by these divisions is called a cell. This is a diagram of the top left hand corner of the grid:
---- ----- ----- ----- ----- | | B | C | D | E ----+-----+-----+-----+----- | 2 | | | | ----+-----+-----+-----+----- | 3 | | | | ----+-----+-----+-----+----- | 4 | | | | ----+-----+-----+-----+----- | 5 | | | | ----+-----+-----+-----+----- | 6 | | | | ----+-----+-----+-----+----- | | | | |
Figure 1.1 The Grid
As you can see, the grid is labelled across the top with letters and down the left hand side with numbers. On the Organiser Spreadsheet, the grid continues across to column Z and down to row 99, but the few cells shown in the diagram are enough for us at the moment.
In all spreadsheets, just one cell is current at any one time. The current cell is the one you can enter data into at that moment. To keep track of which cell is current, we use a cell reference, rather like a grid reference on a map. The cell reference is made up of the letter of the column and the number of the row in which the current cell lies. So the cell at the very top left of the grid is cell A1; the next cell to the right is cell B1 and so on.
The Organiser screen can not show the whole of the grid at once so it just shows a few cells at a time.
When you have started up the spreadsheet, as described in the introduction, the display will look like this:
--------------------------- Current cell reference | ---------------------- Column Title Cell (column B) | | ---------------- Column Title Cell (column C) | | | ↓ ↓ ↓ B2 |B |C 2 |_ | ↑ ↑ ↑ ↑ | | | | | | | ---------------- Cell C2 | | --------------------- Current Cell | ----------------------- Cursor --------------------------- Row Title Cell (row 2)
The top line of the display shows the current cell reference, followed by the contents of the cell at the top of the current column (column B), and the contents of the cell at the top of the next column to the right (column C). These cells on the top row of the grid are called Title Cells, and their contents are Titles. Cells are delimited by dotted bars. The current cell is always visible in the display and is marked by a flashing cursor. The bottom line shows the contents of the leftmost cell of the current row (this too is a Title Cell), followed by the current cell and as many other cells as can be displayed on the screen.
Imagine the whole grid is laid out in front of you and you have a piece of card with a window cut in it. You pass this over the grid, seeing a few cells at a time. You can move the window up, down, left or right to see any part of the grid.
Moving around the grid is easy. Press the RIGHT cursor key once and look at the display. It should now look like this:
C2 |B |C 2 |_ | ↑ | ---------------- Cursor
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 will scroll one cell to the left, so the current cell (D2) is still on the right of the screen. The cell reference always stays in position on the display whatever you do, showing you exactly where you are on the grid.
Press the RIGHT and DOWN keys a few times and watch the cell reference change as you go across and down the grid. You will notice that the display only scrolls when necessary to keep the current cell visible. So if two cells are shown on the top line and the cursor is in the left hand one, pressing the RIGHT key just moves the cursor over to the right hand cell.
Now use the UP and LEFT cursor keys to return to the cell you started from, B2, and we will start entering some numbers.
When you run the spreadsheet, the keyboard is in NUMeric mode, so you can type in numbers directly without having to press the SHIFT key. When you have moved back to cell B2, type:
4
You will notice that 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. If you type something wrong, you can delete characters to the left of the cursor with the DEL key. When you finish your entry by pressing EXE the display is restored.
B2 |B |C 2 | 4|
You have entered your first value into the grid, the first step towards making a worksheet. Now press the RIGHT key to move across to cell C2 and type:
5 EXE
The whole top left corner of your worksheet now looks like this:
---- ----- ----- ----- ----- | |B |C |D |E ----+-----+-----+-----+----- |2 | 4| 5| | ----+-----+-----+-----+----- |3 | | | | ----+-----+-----+-----+----- |4 | | | |
Figure 1.2 Entering Numbers
You have seen how easy it is to enter a number into a cell. It is just as easy to put text into a cell, and is done in exactly the same way.
Press the DOWN key once to move to cell C3. Press the SHIFT key and the NUM key to set the keyboard mode to alpha mode and type:
TOTAL EXE
As before, the rest of the line to the right of the cursor will be cleared as soon as you press the first T of TOTAL. When you press EXE the word TOTAL appears in the current cell.
Note that if when you enter text which has trailing spaces, these will be discarded by the spreadsheet. Text which has leading spaces will be left as entered. This allows you to centre text in wide cells for use as headers and so on.
To delete the contents of any cell just move to that cell with the cursor keys and press the DEL key once.
When you are putting data into the spreadsheet, it is categorised in the following way:
If the first character is a digit from 0 to 9, +, -, or a decimal point the input is regarded as a number
If the first character is a ", the input is regarded as text
If the first character is an =, the input is regarded as a formula (these will be covered in detail in later chapters)
otherwise, the input is regarded as text, including the first character.
This means that when you want to enter text, you just type it straight in - unless your text begins with a number or an arithmetic sign. In this case, if you just typed in, for example, 2TEXT, the spreadsheet would see the 2 and attempt to evaluate the input as a number. This would fail and an error message would be displayed.
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 etc) appear left aligned, but so do the row numbers (1, 2, 3 etc) because these are a special case and are treated like text.
The top left corner of the grid now looks like this:
---- ----- ----- ----- ----- | |B |C |D |E ----+-----+-----+-----+----- |2 | 4| 5| | ----+-----+-----+-----+----- |3 | |TOTAL| | ----+-----+-----+-----+----- |4 | | | |
Figure 1.3 Entering Text
Now all you have to do is add the numbers entered above together with a formula.
Manipulating the data in a worksheet is done by means of formulae. These are typed into cells in just the same way as numbers and text but when evaluated by the spreadsheet, their results are displayed rather than the formulae themselves.
Use the cursor keys to move to cell D3 and type:
=B2+C2 EXE
This is a formula which says, 'Make the contents of the current cell (D3) become equal to the contents of cell B2 plus the contents of cell C2'.
When you press the EXE key the formula will be evaluated and the display will look like this:
D3 |D |E 3 | 9|
Notice that the formula is not visible at the moment. It is still there, but only the value it produces is displayed. Later on we will see how to look at and alter the formulae in the grid, but for now, because our first few formulae will be quite straightforward, we will leave them out of sight.
Adding two numbers is a very trivial example of a formula and hardly worth using a spreadsheet for, but wait - go back and alter one of the original two numbers. Go to cell B2 and type:
121 EXE
Now move across to cell D3 and the new value given by the formula is already there!
This is called a what if operation. You are saying, 'what if this figure changes? How will it affect my result?'. In a worksheet of this size, what if operations are not all that necessary, but on a bigger worksheet they are invaluable.
In effect, what you have done is programmed the Organiser without using any conventional programming language. You have created a worksheet which will add any two numbers together and display the result.
The power of the spreadsheet is hardly stretched by this example so let's look at a slightly more complex one.
This time, we will enter a monthly budget list, but first, if necessary, clear the 4+5 example out of the grid by typing:
MODE EXE Z
If there is any data in the grid, you will now have to type Y (Yes) to confirm that you want to erase it altogether and start again with a blank grid. You have now used the ZAP command to clear the entire contents of the grid. More will be said on the subject of the commands later on.
Here is a list of cells. Go to each cell in the list in turn and enter the text shown alongside:
A2 SAL A3 GAS A4 ELEC A5 MORT A6 FOOD A7 CAR
Now enter these figures in the cells B2 to B7 as shown:
B2 850 B3 40 B4 30 B5 420 B6 100 B7 90
Now 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 is inserted in cell C2. The result shows the amount of free cash for the month, after paying the bills.
The completed worksheet will now look like this:
---- ----- ----- ----- | |B |C |D ----+-----+-----+----- |SAL | 850| 170| ----+-----+-----+----- |GAS | 40| | ----+-----+-----+----- |ELEC| 30| | ----+-----+-----+----- |MORT| 420| | ----+-----+-----+----- |FOOD| 100| | ----+-----+-----+----- |CAR | 90| | ----+-----+-----+-----
Figure 1.4 A Budget Example
Again, this is quite a simple example, but the formula used here could be made even more simple by using the SUM function which adds up the contents of a given number of cells. For example, type into cell D2 the formula:
=B2-SUM(B3:B7)
This formula takes the value in cell B2 (salary) and subtracts the SUM of the values in cells B3 down to B7. This gives the same 3 result as the first version, but is shorter - especially when a referring to large numbers of cells.
Functions will be covered in some detail in later chapters.
When entering data or formulae, the input may be ended by pressing either EXE or one of the cursor keys. If you end the input by pressing one of the cursor keys, you will also move one cell in the direction of that cursor key.
The DEL key may be used to delete to the left of the cursor.
Pressing the MODE key while entering data changes the 3 action of the cursor keys. They then allow you to move the cursor to any point in the data entered so far and edit it. Press DEL to delete the character to the left of the cursor. Press SHIFT and DEL to delete the character under the cursor. If you press MODE again, the cursor keys revert to their original action of ending the input and moving the cursor to the next cell. Press EXE to end the input.
So far, only one of a number of possible display modes has been seen. The various modes exist to make the most effective use of the two line display on the Organiser.
Let's now look at another example worksheet which we can use to study the display modes in a little more detail.
If you have just typed in one of the examples from the previous chapter you should first clear the grid by typing MODE EXE Z Y
This worksheet will show the sales versus costs figures for a small company. In the following diagram the row numbers and column letters have been added outside the grid for clarity.
(A) (B) (C) (D) (E) (F) (G) (H) ---- ----- ----- ----- ----- ----- ----- ----- ----- (1) | |JAN |FEB |MAR |APR |MAY |JUN |JUL | ----+-----+-----+-----+-----+-----+-----+-----+----- (2) |SALE| | | | | | | | ----+-----+-----+-----+-----+-----+-----+-----+----- (3) |COST| | | | | | | | ----+-----+-----+-----+-----+-----+-----+-----+----- (4) |PROF| | | | | | | | ----+-----+-----+-----+-----+-----+-----+-----+----- |5 | | | | | | | | ----+-----+-----+-----+-----+-----+-----+-----+-----
Figure 2.1 Sale, Cost, Profit Example
Enter the set of titles in columns B to H along the top row (row 1) and the titles in rows 2 to 4 in the left hand column (column A). These will then act as a meaningful grid reference as you move around the grid.
Enter the following figures into the cells shown:
B2 184 C2 190 D2 117 E2 38 B3 83 C3 82 D3 76 E3 72
(A) (B) (C) (D) (E) (F) (G) (H) ---- ----- ----- ----- ----- ----- ----- ----- ----- (1) | |JAN |FEB |MAR |APR |MAY |JUN |JUL | ----+-----+-----+-----+-----+-----+-----+-----+----- (2) |SALE| 184| 190| 117| 38| | | | ----+-----+-----+-----+-----+-----+-----+-----+----- (3) |COST| 83| 82| 76| 72| | | | ----+-----+-----+-----+-----+-----+-----+-----+----- (4) |PROF| | | | | | | | ----+-----+-----+-----+-----+-----+-----+-----+----- |5 | | | | | | | | ----+-----+-----+-----+-----+-----+-----+-----+-----
Figure 2.2 The Completed Sale, Cost, Profit Example
After you enter the figure 72 in cell E3, the display looks like this:
E3 |MAR |APR COST| 76| 72 ↑ | -------------- Cursor
With just a glance at the areas above and to the left of the current cell, you can see that you are dealing with the Costs figures for April.
The areas above and to the left of the current cell show the contents of the title cells at the top of the current column and the left of the current row. Press the DOWN key a few times and you will see that the APR title is always visible, even if you are in a cell a long way down that column - the column title cell is still visible above the current cell so you can always tell that you are in the column devoted to the figures for April.
Now return to cell E3 and press the RIGHT key a number of times. You will see that the row title, COST, on the left of the screen is still displayed while you are in any cell in row 3.
In this way the titles always let you know where you are in the grid in relation to the design of the particular worksheet.
So far you have been unable to see any formula you have typed in after its entry has been completed. With the cursor on cell E4, type:
MODE D
You will be returned to the same cell in the grid, but the display will have changed. You have just used the DISPLAY command to make the spreadsheet display formulae.
Now enter these formulae into the cells shown:
B4 =B2-B3 C4 =C2-C3 D4 =D2-D3 E4 =E2-E3
The display will now show:
-------------- Cursor | ↓ E4 | 41| -34| APR.PROF:=E2-E3
The top line shows the current cell reference, the current cell and as many other cells as will fit on the screen. The bottom line shows the titles from the top of the current column and the left of the current row, separated by a dot. These go together to form a title reference.
After the title reference is the contents of the current cell; the formula =E2-E3. The formula evaluates to the result shown above in the current cell.
If the current cell is empty, the area to the right of the title reference on the bottom line will be blank. If the current cell contains text or a numeric value, this will be displayed to the right of the title reference. Text appears with a leading quotes character (").
You can now see not only the titles from the title cells relevant to the current cell, but you can see the displayed value of the current cell and its true contents, be it a number, text or a formula.
This mode still allows you to see that you are in the right cell according to the titles, but also allows you to check the formulae and other data in the cells of the grid.
Some spreadsheet applications do not easily fit into a simple rectangular pattern and in these cases, the use of titles may not be suitable. For this reason, it is possible to remove the titles altogether from the display. At the moment, the screen looks like this:
-------------- Cursor | ↓ E4 | 41| -34| APR.PROF:=E2-E3
To remove the titles, select the TITLES command by typing MODE T. The display will now show:
-------------- Cursor | ↓ E4 | 41| -34| =E2-E3
The top line is unchanged but the bottom line now shows just the formula in the current cell. If the current cell contained just a number or a piece of text, this would be shown on the bottom line.
It is possible now to use the DISPLAY command again to prevent formulae being shown, and the screen would show:
-------------- Cursor | ↓ E4 | 41| -34| f | | |
Beneath the current cell reference is a three character status area followed by the cells in the grid which are directly below those shown on the top line.
The characters which may appear in the status are, depending upon the condition of the current worksheet, each have a different meaning. An 'f' indicates that the current cell contains a formula; a '*' indicates that the worksheet has been modified since it was last recalculated and is likely to need recalculating and a 'c' indicates that the current worksheet contains one or more circular references. These three indicators will be covered in more detail in the reference chapter.
Note that the '*' exists because the automatic recalculation of formulae can be switched off, then the grid is not recalculated every time you enter a value into a cell. When this is done, the circular reference indicator ('c') may be unreliable as this is only checked when the worksheet is recalculated. The whole worksheet can be recalculated by pressing EXE when the grid is displayed.
In a worksheet where titles are not used, this display mode has the advantage of showing more cells at one time than is possible when the titles are displayed.
In all display modes the current cell reference is displayed at the top left of the screen.
When you first enter the spreadsheet, titles are displayed but formulae are not.
Use the DISPLAY command, by typing MODE D, to display formulae or to stop displaying formulae if they are already visible.
Use the TITLES command, by typing MODE T, to stop displaying titles or to display them if they are not currently visible.
When neither titles nor formulae are displayed, a three character status area is displayed at the bottom left of the screen. The presence of these characters indicates the following:
| 'f' | The current cell contains a formula |
| '*' | The worksheet has been modified since it was last recalculated |
| 'c' | The current worksheet contains one or more circular references |
See the reference chapter for further details of recalculation and circular references.
You have already learned how to move around the spreadsheet grid and how to enter numbers, text and formulae into cells. This chapter will briefly show some of the commands available to you and how to negotiate the command menus. The commands which are not covered in detail here are fully explained in the Commands chapter.
The spreadsheet has a number of commands which are arranged in menus. To make a selection from the menu, move the cursor to that item and press EXE. Alternatively, press the first letter of that item. If there is no other item in the menu with the same initial letter, the item will be selected immediately. If there is another item in the menu with the same initial letter you must press EXE to select the item.
There are three command menus in the Organiser spreadsheet. The main command menu has seven items; two of these lead on to further command menus.
This is best explained by a map of the three menus:
THE GRID | ↓ Press MODE | ↓ ALTER GOTO TITLES The MAIN DISPLAY QUIT spreadsheet GRID FILE command menu / \ The Grid DELETE FORMAT LOAD SAVE The FILE command WIDTHS PRINT IMPORT EXPORT command menu COPY RECALC DIR ERASE menu ZAP
Figure 3.1 The Command Menus
To see the MAIN command menu, press the MODE key. The first items in this menu are GRID and FILE. When you select either of these, they will each lead to another command menu. To enter the GRID or FILE menus, simply select the appropriate item from the MAIN command menu.
To return from the GRID or FILE menus to the MAIN command menu, press the ON/CLEAR key. Press the ON/CLEAR key again to return to the grid from the MAIN command menu.
Note that if you press ON/CLEAR too many times, you will leave the spreadsheet altogether and be returned to the Organiser's top-level menu (as distinct from the spreadsheet MAIN command menu). If this happens, do not worry, none of your data is lost. You simply have to select PLAN again from the top-level menu to go back into the spreadsheet. You will even be returned to the cell you were in when you left the spreadsheet.
Although you can step freely from the MAIN command menu down to either the GRID or FILE menus and back up again, you can not step straight from the grid menu to the FILE menu - you must go via the MAIN command menu.
After using a command, you are usually returned directly to the grid (depending upon the action of the particular command).
These are the commands in the MAIN command menu:
| GRID | Enter the GRID command menu |
|---|---|
| FILE | Enter the FILE command menu |
| ALTER | Alter the contents of the current cell |
| GOTO | Go to another cell in the grid |
| TITLES | Switches Titles on or off |
| DISPLAY | Switches between formula or cell display |
| QUIT | Leaves the spreadsheet and frees the memory used by it |
The first two items in this menu, GRID and FILE, will be discussed in a moment; the rest of the commands in this menu are those most often needed when using the spreadsheet.
If the contents of a cell is long and complex, there are two ways of altering it; retype the whole contents or use the ALTER command. For example, imagine you have just typed the following formula into cell B2:
=INT(HLOOKUP(G7,G8:L16))*10
and you find that you should have typed VLOOKUP instead of HLOOKUP. It would be rather inconvenient to have to type in the whole thing again, just for the sake of one character, and this is where the ALTER command becomes useful.
To use ALTER, press the MODE key to see the MAIN command menu. Press A to select ALTER and the display will show the formula you typed in, on the bottom line of the display. The formula plus the title reference, B.2, is longer than the 16 character width of the display. The result is that only the end of the formula is visible, with the cursor positioned at the end, like this:
E3 | | | B.2:=INT(HLOOKUP(G7,G8:L16)*10_ ↑ | ---------- Cursor
You can now press the LEFT key until the cursor is to the right of the H in HLOOKUP. Press the DEL key to delete the H and type V.
Press the EXE key to complete the alteration of the formula and to insert the edited into the current cell, alternatively press ON/CLEAR to clear the formula altogether and press ON/CLEAR again to restore the formula to its original state.
One way to move from one cell to another is simply by using the cursor keys. This is fine for short distances, but when you have to move from, for example, cell C4 to cell T83, a faster way is to use the GOTO command.
Press the MODE key to enter the MAIN command menu and select GOTO by pressing G EXE. The top line of the display will not change, but the bottom line of the display will show GOTO:, followed by a suggested cell. The suggested cell depends on the current display mode and the state of the grid:
If the current worksheet contains a circular reference, one of the cells involved in the circular reference will be suggested. Otherwise the suggested cell is either B2, if titles are displayed, or A1 if titles are not displayed. The bottom line of the display will show:
GOTO: B2
In this example, the cell offered by GOTO is B2. Just press EXE to accept or type the reference of another cell - the suggested cell reference will be replaced by what you type. Press EXE to go to the cell you typed in.
Note: A circular reference may be thought of as a loop in the order the formulae are evaluated. See the Reference chapter for more details of circular references.
When you have finished using a worksheet and you want to erase it from memory, you should use the QUIT Command. QUIT discards the current worksheet and trees all the memory used by it, then returns to the Organisers top-level menu. All the Organiser's memory is then available for other uses.
Press the MODE key to enter the MAIN command menu and select QUIT by pressing Q. If the current worksheet has been modified since it was last saved to a device (see the SAVE command) QUIT requests confirmation before discarding your data and leaving the spreadsheet.
When QUIT is executed, the effects of the command can not be reversed. Make sure that the current worksheet can safely be discarded before using QUIT.
To return to the Organiser's 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 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. You can then return to the spreadsheet and continue working just where you left off.
Press the MODE key to enter the MAIN command menu and select GRID by pressing EXE.
These commands are concerned with processes which can be effected on the grid itself or the contents of the grid.
| 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 |
The commands you have seen so far have all acted either on just one cell (ALTER, GOTO) or on the whole grid (QUIT, ZAP). There are, however, a number of commands which can operate on a range of cells at one time.
A cell range is a rectangular area of cells which is specified by giving the top left cell followed by a colon and the bottom right cell, for example: C7:G12.
This is illustrated in figure 3.2:
B C D E F G H 2 3 4 5 6 7 8 9 10 11 12 13 14
Figure 3.2 A Cell Range
As you can see, both the top left cell and bottom right cell are inclusive in the given range.
There are sometimes cases when you want to specify just cells in one column or in one row as a cell range, or even just a single cell. For example, consider this range of cells:
B C D E F G H 2 3 4 5 6 7 8 9 10
Figure 3.3 A Single Row Cell Range
The range of cells shown above would be given as C7:G7. Similarly, cells from a single column may be specified:
B C D E 2 3 4 5 6 7 8 9 10 11 12 13
Figure 3.4 A Single Column Cell Range
The range shown above would be given as C4:C11.
The simplest cell range of all consists of a single cell and is given as, for example, G4:G4.
The menu items DELETE, FORMAT, PRINT and COPY all prompt in the same way for one or more ranges of cells on which to operate.
When you select one of these commands, the bottom line of the display shows the name of the command, followed by a range reference which refers to just the current cell. 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 part of the range reference. You can accept the suggested range by pressing EXE or you can make your own range selection in one of two ways:
The first way is to type in a range reference which will replace the suggested one. Press EXE to accept the range you typed in.
The second way is to use the cursor keys to alter the range suggested by the spreadsheet. So in the example above you can use the cursor keys to change the first cell reference, just as if you were scanning across the grid in the usual way. Continue pressing the cursor keys until the first cell reference shows the cell at the top left of the block of cells you wish the command to affect.
Notice that as you change the cell reference in this way, the current cell, still shown on the top line of the display, changes with the first cell reference to help make sure you select the right cell.
When the first cell reference is set to the start of the desired range of cells, press the MODE key. The cursor will move across to the second cell reference. This may now be changed with the cursor keys in the same way as the first until it shows the end cell in the desired range. (At this point, if you press the MODE key again, the cursor will move back to the first cell reference, allowing you to change it again if necessary.)
Press EXE and the range of cells indicated will be affected by the command. In the case of, for example, DELETE, the contents of that whole block of cells will be deleted.
If you have typed a new cell range:
| ON/CLEAR | clears the text |
| ON/CLEAR | with no text reverts to original suggested range and |
| ON/CLEAR | again exits the command and returns to the GRID menu |
If you are using the cursor keys to change the suggested range:
| ON/CLEAR | with no text reverts to original suggested range and |
| ON/CLEAR | again exits the command and returns to the GRID menu |
The commands in the FILE command menu are concerned with saving and loading worksheets to and from the internal memory of the Organiser, Datapaks, Rampaks and to and from other computers connected to the Organiser via the Comms Link package.
When the grid is displayed, press the MODE key to enter the MAIN command menu, then press F to enter the FILE menu. This menu contains the following items:
| DIR | Read the directory of worksheet files |
|---|---|
| ERASE | Erase a worksheet file |
| EXPORT | Export a worksheet to a PC |
| IMPORT | Import a worksheet file from a PC |
| LOAD | Load a worksheet file |
| SAVE | Save a worksheet file |
Worksheets may be saved to the Organiser's internal memory or to Datapaks or Rampaks by using the SAVE command. When SAVE is selected, the display shows:
SAVE A:
You may press the MODE key to change the current device. Type in the name, of up to eight characters, with which you want the worksheet to be saved. For example, suppose you want to save the current worksheet under the name FOLIO. When you are prompted for the SAVE name, type:
FOLIO EXE
The display will give the message SAVING. When this process is finished you will be returned to the grid. The current worksheet will be unaltered and you can continue using the worksheet as before.
If the current device already contains a worksheet under the specified name, you will be prompted to confirm that this name is to be used. The old worksheet with this name will be deleted and the new one saved. If there is insufficient memory on the current device to save the current worksheet you will be given the chance to change to another device or abandon the SAVE.
When saving a file, after the file name you may add a password of up to six characters. The password should be separated from the file name by a semicolon. For example, suppose you want to save a file under the name FOLIO with the password CRUNCH. When prompted for the file name you should type:
FOLIO;CRUNCH
Should the battery become low while saving a worksheet to a Datapak, the BATTERY LOW message will be displayed and you will be prompted to press the SPACE key. You will then be returned to the grid with the data intact. You should then press ON/CLEAR to leave the spreadsheet.
SWITCH THE ORGANISER OFF BEFORE ATTEMPTING TO CHANGE THE BATTERY. If you do not do so then all data in the machine, including the current worksheet, will be lost.
When the Organiser is safely switched off, change the battery as described in chapter 2 of the Organiser Operating Manual and return to the spreadsheet. Select SAVE from the FILE menu and continue.
Using a mains adaptor prevents this problem and makes the process of saving to Datapaks faster. Contact your supplier for details of the Psion Mains Adaptor.
The LOAD command is used to reload a worksheet previously saved to the Organiser's internal memory or to a Datapak or a Rampak. Using LOAD overwrites any worksheet currently in use, so ensure that the current worksheet can be safely overwritten before loading in another one. If the current worksheet has been modified since it was last SAVEd or EXPORTed, you will be prompted to confirm that you want to overwrite it.
When LOAD is selected, the display shows:
LOAD A:
You may press the MODE key to change the current device. Type the name of the worksheet file to be loaded and press EXE. If the worksheet you want to load was saved with a password, that password must be given along with the filename, separated from it by a semicolon. For example: FOLIO;CRUNCH loads the file FOLIO which was saved with the password CRUNCH. Passwords of up to six characters are allowed.
If you do not give the password or you type an incorrect password, the error message BAD PASSWORD will be displayed.
Note that file passwords are not visible or readable in any way. You must remember the password used when saving a file to be able to retrieve it with the LOAD command.
The DIR command is used to read the directory of saved worksheets in the Organisers internal memory or on a Datapak or Rampak. When DIR is selected the display shows:
DIR A:
You may press the MODE key to change the current device. Press the EXE key and the name of the first worksheet saved on the current device is displayed. Subsequent presses of the EXE key show other worksheet files saved on the same device until the last has been shown, when an END OF PACK message is displayed. Press ON/CLEAR at any stage to return to the FILE menu.
Worksheets which were saved with a password will just be shown under the main file name - the password will not be visible.
This command is used to erase a saved worksheet file from the Organiser's internal memory or from a Datapak or Rampak. When ERASE is selected, the display shows:
ERASE A:
You may press the MODE key to change the current device. Type the name of the worksheet file you wish to erase from the specified device and press the EXE key. The file will be deleted and you will be returned to the grid.
If no worksheet file of that name is found on the specified device a FILE NOT FOUND error is reported. Press ON/CLEAR to return to the grid.
The ERASE command does not accept passwords and so password-protected files can be erased without giving the password. The password is used to prevent unauthorised access to your data, but you are responsible for the safety of the data itself.
In the last few chapters you learned how to enter data into a worksheet, enter formulae, use the commands and make the most of the display. Now it is time to put all of this to some practical use and build some real worksheets.
The first worksheet is an application which adds up the total value of your share portfolio. Once you have entered the quantity of each issue of shares you own, you just have to put the latest price for each of the issues into the worksheet. The share portfolio total is calculated and displayed in a cell at the top of the worksheet where it can easily be viewed.
Before you can begin building the worksheet, you should clear out any old data and formulae from the whole grid with the ZAP command as described earlier, perhaps removing any of the small examples from previous chapters that you may have typed in. Then you can get down to work.
Here is a diagram of the Titles we will use to find our way around the share portfolio worksheet. These should be entered as they appear here:
---- ----- ----- ----- ----- ----- ----- ----- | |FOLIO|TODAY|QTY |SUB-T|F |G |H ----+-----+-----+-----+-----+-----+-----+----- |NOW | | | | | | | ----+-----+-----+-----+-----+-----+-----+----- | | | | | | | | ----+-----+-----+-----+-----+-----+-----+----- |BT | | | | | | | ----+-----+-----+-----+-----+-----+-----+----- |TSB | | | | | | | ----+-----+-----+-----+-----+-----+-----+----- |GAS | | | | | | | ----+-----+-----+-----+-----+-----+-----+----- |BA | | | | | | | ----+-----+-----+-----+-----+-----+-----+----- |8 | | | | | | | ----+-----+-----+-----+-----+-----+-----+----- |9 | | | | | | | ----+-----+-----+-----+-----+-----+-----+----- | | | | | | | |
Figure 4.1 Share Portfolio Example
The titles in cells A4 to A7 are the names of the shares held. British Telecom, Trustee Savings Bank, British Gas and British Airways respectively. If you own any shares, feel free to use the actual prices and names - the worksheet will otherwise be the same as this example.
Note in the diagram showing the titles, to avoid confusion, the row title cell whose default value is '3' has been cleared. To do this, go to that cell (A3) and press type MODE A EXE. This uses the ALTER command to change the cell so that the title is no longer visible. To restore a title, just go to that cell and press the DEL key. This restores the cell to its original condition, allowing the title to be seen.
Since the figures entered and the results from calculations will be monetary, we should set the numeric format for those cells containing prices to 2 decimal places.
Go to the MAIN command menu and then the GRID menu as described earlier and select FORMAT.
A sub-menu will be displayed, containing the items DEFAULT and CELLS. You should select DEFAULT. This option sets the cell format to be used when entering data into empty cells. The CELLS option changes the numeric format of cells which already contain data.
Another submenu will now be displayed, containing the items FIXED, INTEGER, SCIENTIFIC and GENERAL. Select FIXED to set a fixed number of decimal places.
You will be prompted for the range of cells to affect. Select the range B2 to C99. When you press EXE the display will prompt for the number of decimal places. The default is 2 so just press EXE. You will return to the worksheet with that number of decimal places set for the cells specified.
Repeat this process for the cells in column E (the sub-total column).
Enter the following formulae into the cells as indicated:
Cell Title Reference Formula B2 Folio.Now =SUM(E4:E7) E4 Sub-T.BT =C4*D4
These formulae calculate the current value of the portfolio (Folio.Now) and the current subtotal for the first of the shares held, based on today's price (Sub-T.BT).
Type in the two formulae, and remember to start each one with an equals sign, or they will be interpreted as plain text.
The next task is to enter into the rest of the cells in column E (under Sub-T) a formula to calculate the day's total for each of the rest of the shares held. This would be a very tedious process if we had more than a few different shares. Fortunately, there is an easier way; the COPY command.
When you have entered the formulae shown above, go to cell E4, press the MODE key to view the MAIN command menu, select GRID to enter the GRID menu and select the COPY command.
The bottom line of the display will show:
COPY: E4:E4
You should accept this as the range to be copied by pressing EXE. This cell has already had a formula entered, and it is this formula which must he copied to enough cells in column E to have one copy for each share hold. When this is done, you can enter the share names and their prices.
The bottom line of the display will now show:
COPY TO: E4:E4
Press the MODE key to move the cursor over to the second part of the range reference and press the DOWN key until the range reference reads E4:E7. Press the EXE key and the formula in E4 will be copied to all the cells in the range.
The default cell width is five characters, which may not be enough to show the price of your shares, so you should change the width of the cells with the WIDTHS command.
Press the MODE key to view the MAIN command menu, select GRID to view the GRID command menu and press W to select WIDTHS.
The bottom line of the display will show:
WIDTHS: B:B
The WIDTHS command sets the cell width for a range of columns. Only whole columns may be affected. We need columns B to E to be wider than the default of 5 characters, so press the MODE key to move the cursor over to the second B and press RIGHT three times. The display will now show:
WIDTHS: B:E
Press EXE to accept the range of columns shown. The display will now prompt for the new width of this range of columns:
WIDTHS: 5
You may now type a number from 1 to 12 for the new width of the selected range of columns. Type:
9 EXE
to set the cell width to nine characters.
The display will now look like this:
B2 |FOLIO :T→ NOW:=SUM(E4:E7)
To top line shows the current cell reference, followed by the title cell from the top of the current column. The width of columns B to E is nine characters, so only one whole title cell can be fitted on the top line after the cell reference. The remainder of the top line shows as much of the next title cell as possible.
Any cell which is only partly displayed in this way may end with a right pointing arrow. This means that the partly displayed cell contains data which cannot be displayed fully on the screen. To see the full contents of that cell, press the RIGHT key once to step across to that column.
The arrow will be displayed when:
Now put some prices and quantities into the appropriate cells and watch the current total appear in the result cell. You can enter as many different shares as will fit in the remainder of column C, all the way to the last row of the grid (row 99) but make sure you copy the formula from E4 to cover all of the shares entered. Change some of the prices and the new results will be calculated immediately.
---- --------- --------- --------- --------- ----- -- | |FOLIO |TODAY |QTY |SUB-T |F |G ----+---------+---------+---------+---------+-----+-- |NOW | 3190.00| | | | | ----+---------+---------+---------+---------+-----+-- | | | | | | | ----+---------+---------+---------+---------+-----+-- |BT | | 2.37| 1000| 2370.00| | ----+---------+---------+---------+---------+-----+-- |TSB | | 0.90| 500| 450.00| | ----+---------+---------+---------+---------+-----+-- |GAS | | 0.75| 200| 150.00| | ----+---------+---------+---------+---------+-----+-- |BA | | 1.10| 200| 220.00| | ----+---------+---------+---------+---------+-----+-- |8 | | | | | |
Figure 4.2 The Completed Share Portfolio Example
Don't worry about the amount of memory used by copying a formula to a few more cells than you need at the moment because when formulae are copied, whenever possible, the formulae are shared in the memory of the Organiser. So wherever two identical formulae appear in the worksheet, only one formula is stored, and both cells share the one copy. You don't need to be aware of the details of this process, just that the Organiser always makes the most efficient use of memory that it can.
When you start to enter the day's prices for each of your shares, each time you enter one, the whole worksheet is recalculated and the results placed in the appropriate result cells. This is because the spreadsheet is by default in AUTO- RECALCULATE mode. Every time you change the contents of a cell, the whole worksheet is recalculated.
If you have a lot of formulae in a worksheet, and in this example you may have up to 94, there may be a delay while the results are calculated. It is sensible therefore to turn off automatic recalculation while you are making changes to a worksheet containing many formulae with the RECALC command in the GRID command menu.
Regardless of whether automatic recalculation is turned off, you can always force a recalculation of the worksheet by pressing EXE when the grid is displayed.
The RECALC command presents a sub-menu containing the items MANUAL and AUTOMATIC. Select the type of recalculation most suited to your application and you will be returned to the grid with that mode in operation.
Now let's have a more detailed look at how COPY works.
The spreadsheet assumes that unless you specify otherwise, cell references are relative, i.e., 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 (again one column to the left and one row above).
This is illustrated below. A formula in cell X 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 F G H 2 3 4 ↑___ X 5 6 ↑___ Y 7 8 9 10
Figure 4.3 Relative Cell References
In your share portfolio worksheet, you put the formula =C4*D4 into cell E4. So the cell containing the formula is given the product of the contents of the two cells to its left.
When you used the COPY command to copy this one cell to the rest of the cells in that column, the same relationship applies - all of the cells in the specified range display the product of the contents of the two cells to their left.
Take a look at cells E4 to E7, you will find that the formulae are as follows:
| Cell: | Contents: |
| E4 | =C4*D4 |
| E5 | =C5*D5 |
| E6 | =C6*D6 |
| E7 | =C7*D7 |
Note that you may have to use the DISPLAY command to see the formulae on the bottom line of the display depending on the display mode currently in use.
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 formulae in cell D3 refers to cell $B$2, if you COPY this formula to another cell, that too will refer to cell $B$2.
This is illustrated below:
A formula in cell X contains an absolute reference to the shaded cell. A copy of the formula in cell Y refers to the same cell.
B C D E F G H 2 3 4 ↑ ↑ 5 | |__ X 6 |__________ Y 7 8
Figure 4.4 Absolute Cell References
Looking again at the share portfolio example, if instead of the formula =C6*D6, you put the formula =$C$6*$D$6 into cell E4 and copied it to the other cells in column E, the cells would contain the following formulae:
| Cell: | Contents: |
| E6 | =$C$6*$D$6 |
| E7 | =$C$6*$D$6 |
| E8 | =$C$6*$D$6 |
| E9 | =$C$6*$D$6 |
| E10 | =$C$6*$D$6 |
and so on down to the bottom of the column.
Above we saw relative cell references, for example: B2; C3. We also saw absolute cell references, for example: $B$2; $C$6*$D$6.
These are the clear cut cases where the whole reference is either relative or absolute. However, you can mix these two types, giving references which are part absolute and part relative. For example:
If the cell B2 contains the formula =$C3 and this is copied to, for example, the cell range D4 to E6, the results would be as follows:
D E 4 =$C5 =$C5 5 =$C6 =$C6
If the cell 82 contains the formula =B$3 and this is copied to, for example, the cell range D4 to E5, the results would be as follows:
D E 4 =E$3 =F$3 5 =E$3 =F$3
Here is an example of using 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, for example, the US Dollar.
You will also enter today's price of each of the commodities in Dollars. You will then be able to 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 then copied to all of the necessary cells where the price multiplied by the exchange rate is calculated. The formulae uses mixed cell references.
The benefit of doing this is that however many different shares you have, you still only have to enter one formula to calculate a daily total for each of them. Another benefit is that only one formula is stored for all of the shares, so less memory is used than if there was a different formula for each one.
First clear the whole grid with the ZAP command as described earlier. Use the WIDTHS command, also described earlier, to set the widths of columns B to G, to eight characters wide, then enter the titles as shown below:
---- --------- --------- --------- --------- --------- -- | |DOLLAR |STERLING |YEN |FRANC |DMARK |G ----+---------+---------+---------+---------+---------+-- |RATE| | | | | | ----+---------+---------+---------+---------+---------+-- |GOLD| | | | | | ----+---------+---------+---------+---------+---------+-- |SILV| | | | | | ----+---------+---------+---------+---------+---------+-- |COPP| | | | | | ----+---------+---------+---------+---------+---------+-- |TIN | | | | | | ----+---------+---------+---------+---------+---------+-- |7 | | | | | | ----+---------+---------+---------+---------+---------+-- |8 | | | | | |
Figure 4.5 Commodity Price Example
Now switch off automatic recalculation with the RECALC command. You should also set the numeric format with the DEFAULTS option of the FORMAT command. Select FIXED format with 2 decimal places as these cells are dealing will monetary figures.
Prices may be entered to an accuracy of more than two decimal places, and the full accuracy quoted will be stored by the spreadsheet and used in the calculations, but the result will only be displayed to two decimal places.
Now enter the formula =C$2*$B3 into cell C3 and then use the COPY command to copy this formula to cells C3 to F6. You can now enter the day's exchange rates and commodity prices. The formula takes the exchange rate from the top of the current column and multiplies it by the commodity price at the left of the current row. This happens in all the cells the formula has been copied to.
The exchange rates should be entered in row 2 in the appropriate columns. Use the (US) Dollar as the reference. So if the exchange rate of Pounds for Dollars is .639, enter that figure in cell C2 (STERLINGRATE), and so on across the list of currencies.
The commodity prices are entered in column B. If the price of gold is (US) $406.26 per fine ounce, enter that figure in cell B3 (DOLLARGOLD), and so on down the list of commodities. Use the quoted prices in J8 Dollars for whatever unit that commodity is usually traded in. For example, gold is quoted in fine ounces, silver in troy ounces, lead in tonnes and so on.
When all of the exchange rates and prices have been entered, recalculate the grid by pressing EXE. All the prices in the various currencies can then be read from the relevant cells.
You may now change the numeric format of the Yen column with the CELLS option of the FORMAT command to INTEGER. This will avoid the rather unnecessary decimals in this column.
---- --------- --------- --------- --------- --------- -- | |DOLLAR |STERLING |YEN |FRANC |DMARK |G ----+---------+---------+---------+---------+---------+-- |RATE| | 0.64| 154| 6.11| 1.84| ----+---------+---------+---------+---------+---------+-- |GOLD| 406.25| 259.59| 62441| 2482.59| 746.69| ----+---------+---------+---------+---------+---------+-- |SILV| 5.47| 3.50| 841| 33.43| 10.06| ----+---------+---------+---------+---------+---------+-- |LEAD| 484.37| 309.51| 74447| 2959.97| 890.27| ----+---------+---------+---------+---------+---------+-- |ALUM| 1414| 903.53| 217328| 8640.81| 2598.89| ----+---------+---------+---------+---------+---------+-- |7 | | | | | | ----+---------+---------+---------+---------+---------+--
Figure 4.6 The Completed Commodity Price Worksheet
Now you just have to change one or more of the exchange rates or commodity prices to see how the prices in the other currencies are affected.
This example shows how to use the COPY command to copy a range of cells to another range. The worksheet lets you request a particular table and then displays it.
Here is how the whole worksheet would look for, for example, the 7 times table:
---- ----- -- -- --- | |B |C |D |E | ----+-----+--+--+--- | 2 |TIMES| 7| | | ----+-----+--+--+--- | 3 | | | | | ----+-----+--+--+--- | 4 | | 1| 7| 7| ----+-----+--+--+--- | 5 | | 2| 7| 14| ----+-----+--+--+--- | 6 | | 3| 7| 21| ----+-----+--+--+--- | 7 | | 4| 7| 28| ----+-----+--+--+--- | 8 | | 5| 7| 35| ----+-----+--+--+--- | 9 | | 6| 7| 42| ----+-----+--+--+--- | 10 | | 7| 7| 49| ----+-----+--+--+--- | 11 | | 8| 7| 56| ----+-----+--+--+--- | 12 | | 9| 7| 63| ----+-----+--+--+--- | 13 | |10| 7| 70| ----+-----+--+--+--- | 14 | |11| 7| 77| ----+-----+--+--+--- | 15 | |12| 7| 84| ----+-----+--+--+---
Figure 4.7 A Multiplication Table
Start by entering the text, 'TIMES' in cell B2. The number of the table you want will be entered next to this in cell C2.
As you can see from the table above, the widths of the column need only be a few characters each because the numbers being displayed are not very big. Set the width of columns C and D to 2 characters and the width of column E to 3 characters as described in the last chapter.
Now enter these formulae into the appropriate cells:
Cell Formula C4 =C3+1 D4 =$C$2 E4 =C4*D4
Now copy the cell range C4: E4 to the range D5:D15 with one use of the COPY command. These will provide the numbers 1 to 12, 12 copies of the number of the table and the results of the multiplication respectively.
When the table is finished, you can enter any number up to 83 in cell C2 for the number of the table. (Above the 83 times table, the results can not be displayed in 3 characters.)
This chapter contains the general reference section for the spreadsheet. The command menus, commands and functions are covered in detail in the following two chapters.
The spreadsheet grid is 26 columns wide (A to Z) by 99 rows deep (1 to 99). The columns are initially 5 characters wide. The width of columns may be set at 1 to 12 characters.
The following operators are provided:
| + | add |
| - | subtract |
| * | multiply |
| / | divide |
| ** | power |
| Unary minus | make negative |
| Unary plus | 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 have no direct meaning, but give an indication of 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 |
All numbers are stored as floating point values to a precision of 12 significant decimal digits. Numbers in the range 1E-99 to 1E+99 are allowed. If a number outside this range 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 is set on a cell such that the full number of decimals are not shown, the visible part of the number is rounded to the nearest next digit. For example, 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.
Text is always displayed ranged left. If a formula references a cell containing text, that cell is evaluated to 0.0. The maximum length of any text string stored in a cell is 250 characters.
If the current cell is not wide enough to display the full length of a text string or a number, the cell will be displayed containing hashes (#). If the length of the item is 12 characters or less, the WIDTHS command must be used to reset the width of the cell to be able to display its contents in full.
For example, a cell containing the value 123456, is in a column of only five characters wide. The display value of that cell is then #####. Set the width of that column to 6 (or more) characters and the display value will be 123456.
A cell containing a formula displays the evaluated result of that formula, not the formula itself.
Any cell whose contents extend beyond the right hand end of the display may end with a right pointing arrow. This means that the partly displayed cell contains data which cannot be shown fully on the screen. To see the full contents of that cell, press the RIGHT key once to step across to that column.
The arrow will be displayed when:
To delete the contents of any single cell, go to that cell and press the DEL key once. To delete the contents of a range of cells use the DELETE command in the GRID command menu. To delete the contents of all cells in the grid and reset the column widths and numeric formats to their states when the spreadsheet was first run, use the ZAP command in the GRID command menu
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 one identifies 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 (again one column to the left and one row above).
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, if 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
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 etc down the left of the grid. These are always displayed ranged left.
Note that 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.
It is possible to replace the default titles with those of your own choosing. You may use text or numbers.
To suppress the titles which are displayed when the spreadsheet is first run, move to the title cell you want to make blank and type MODE A EXE. This uses the ALTER command to prevent the display of the default title. To restore a title suppressed in this way, go to the appropriate cell and press the DEL key. The cell will be restored to its default condition and the title will now be visible once more.
A formula may consist of allowed combinations of functions, cell references, numbers and arithmetic operators. Formulae must start with an equals character and 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.
The spreadsheet commands are arranged in a menu and two sub-menus. Press the MODE key when the grid is displayed, to see the MAIN command menu. The items GRID and FILE in the MAIN command menu lead to the two sub-menus. Select either of these items to view the sub-menus.
When viewing either of the sub-menus, press ON/CLEAR to return to the MAIN command menu and press ON/CLEAR again to return to the grid.
This is best explained by a map of the three menus:
THE GRID | ↓ Press MODE | ↓ ALTER GOTO TITLES The MAIN DISPLAY QUIT spreadsheet GRID FILE command menu / \ The Grid DELETE FORMAT LOAD SAVE The FILE command WIDTHS PRINT IMPORT EXPORT command menu COPY RECALC DIR ERASE menu ZAP
Figure 5.1 The Command Menus
To make the most of the Organiser's two line display, four display modes are available which are controlled by two 'switches'; the TITLES command and the DISPLAY command. The four modes are;
In the default display mode, the screen would typically look like this:
--------------------------- Cursor Cell Reference | | ---------------------- Column Titles | | ---------------- | | | ↓ ↓ ↓ D5 |MAR |APR PROF| 354| 128 ↑ ↑ | | | --------------------- Current Cell | --------------------------- Row Title
Here the current cell is D3 which contains the display value 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'.
By using the DISPLAY command, the spreadsheet display can be switched so that any formula in the current cell can be seen on the bottom line. With the display shown above, select the DISPLAY command by typing MODE D and the screen shows, for example:
--------------------------- Cursor Cell Reference | | ---------------------- Current Cell | | ↓ ↓ D5 | 354| 128 MAR.PROF:=D3-D4 ↑ ↑ ↑ | | | | | ----------------- Formula Controlling | | Current Cell | | | ----------------------- Row Title | --------------------------- Column Title
This mode shows on the top line, the current cell reference followed by the current cell and as many cells to its right as will fit on the display.
To the left of the bottom line is a title reference made up from 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.
This mode is useful when entering formulae into a worksheet before entering the data. When a formula has been entered it can be seen on the bottom line and checked before going on to the next one.
The mode discussed so far have both featured the titles from the top of the current column and the left of the current row. These, however, may be switched off. Some spreadsheet applications do not easily fit into a simple rectangular pattern and in these cases the use of titles may not be suitable.
To switch the titles off, select the TITLES command by typing MODE T. Using the example above with FORMULA DISPLAY / TITLES OFF, the display shows:
--------------------------- Cursor Cell Reference | | ---------------------- Current Cell | | ↓ ↓ D5 | 354| 128 =D3-D4 ↑ | --------------------------- Formula in Current Cell
If the current cell contains a formula, this mode shows the formula on the bottom line of the display. Otherwise the display value of the current cell is shown on the bottom line.
While the titles are switched off, it is possible to use the DISPLAY command again to remove the formula shown on the bottom line. Continuing with the example above, the display would then show:
--------------------------- Cursor Cell Reference | | ---------------------- Current Cell | | ↓ ↓ D5 | 354| 128| f*c| 106| 68|
The current cell, D3, contains the display value 354. Below the current cell reference is a status area which can show up to three flags.
The flags which can appear in the status area are as follows:
The current cell contains a formula. If you select FORMULA DISPLAY with TITLES ON or OFF, the formula will be shown on the bottom line of the display.
The spreadsheet has been modified since the last calculation. This will only be shown after the contents of a cell has been altered when the spreadsheet is in MANUAL RECALCULATION mode. The default recalculation mode is AUTOMATIC. This means that all formulae in the worksheet are recalculated every time the contents of any cell is altered.
To ensure all displayed values are valid, recalculate the worksheet by pressing the EXE key when the grid is displayed.
Note that if the recalculation mode is MANUAL, the circular reference indicator 'c' (see below) may be unreliable. This is only updated when the worksheet is recalculated, so manually recalculating the worksheet by pressing EXE when the grid is displayed will update the status indicators.
The current spreadsheet contains one or more circular references. See the section later in this chapter under the heading Circular References.
As you use the cursor keys to scroll from cell to cell, the contents of 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 '*', 'f' and 'c' may be generated in any order, depending on the contents of the grid or the current cell, but they will always appear on the display in the order '*fc' although all three may not necessarily be present at one time.
The Organiser spreadsheet supports natural order recalculation, in which formulae which do not depend upon other formulae are recalculated first, followed by formulae E 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 has been effected.
|
| Cell contents = cell contents + 1 |
Figure 5.2 A One Cell Circular Reference
A circular reference consisting of two cells might appear like this:
Cell B2 contains the formula =D2, and cell D2 contains the formula =B2.
Figure 5.3 A Two Cell Circular Reference
Circular references can of course 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 CELLS DISPLAY / TITLES OFF display mode described earlier in this chapter.
The presence of a circular reference is usually an indication that you have made a mistake. Make sure that you really need a circular reference for your application as the values in the worksheet will usually depend on the number of times the worksheet has been recalculated.
Note that when a circular reference has been detected, the default destination cell offered in the GOTO command will be one of the cells contributing to the circular reference.
Any of the following functions may be included in cell formulae. The functions may require one or more parameters or no parameters. The parameters are acted upon by the function and a value is returned by that function to the appropriate point in the formula containing it.
Functions must be supplied with the correct number and type of arguments. 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 |
|---|---|
| <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. |
Any other argument may be regarded as a numeric expression or a reference to a cell displaying a numeric value.
A list of all of the functions available in the Organiser spreadsheet is included below with a brief description of the value returned by each function.
A full description of each of the functions follows in alphabetical order.
ABS(<exp>)
Returns the absolute value, i.e., without any sign, of <exp>. For
example, the formula =ABS(-10) returns the value 10.
ACOS (<exp>)
Returns the angle, in radians, whose cosine is <exp>.
ASIN(<exp>)
Returns the angle, in radians, whose sine is <exp>.
ATAN(<exp>)
Returns the angle, in radians, whose tangent is <exp>.
ATAN2(<exp>,<exp2>)
Returns the angle, in radians, whose tangent is <exp2>/
<exp>, using the signs of <exp2> and <exp> to return a
result in the correct quadrant, taking account of the sign of the
two arguments.
AVG(<list>)
Returns the arithmetic mean (average) of the values in <list>.
The items in <list> may be literal values, cell references or cell
ranges. Specifically, this function returns COUNT (<list>)/SUM
(<list>).
CHOOSE(<exp>,<list>)
Returns the item at position <exp> in the <list>. The index,
<exp>, may be a literal value, a cell reference or a formula. The
values in <list> may be literal values or cell references.
COS(<exp>)
Returns the cosine of the (radian) angle <exp>.
COUNT(<list>)
Returns the number of non-blank cells in <list>. Ranges max
be included in the list. If the literal value 0 appears in <list>,
this is evaluated as non-blank and therefore counted.
DATE(yy,mm,dd)
Returns the number of days elapsed between 30th Dec 1899
and the specified date (1.1.1900 = day 2). In Lotus 1-2-3 1900
is incorrectly taken to be a leap year, so values returned by this
function will differ from those of Lotus 1-2-3 by 1 until 1.3.1900.
DAY(<exp>)
Given a number (<exp>) of elapsed days since 30.12.1899,
returns the day of the month in which that day lies. For
example, the formula =DAY(31941) returns 13 because 31941
days after 30.12.1899 is 13th June 1987.
ERR
Returns an illegal value interpreted as ERRor. For example,
using the IF function, the formula =IF(G12<3,ERR,B2) returns
ERR if the contents of G12 is less than 3 and returns the
contents of B2 otherwise. This can then be tested with the
ISERR function.
EXP(<exp>)
Returns the value of the arithmetic constant e (2.71828...)
raised to the power of the expression inside the brackets.
FALSE
Returns the value 0.0. For example, cell B2 might contain 1 to
denote a certain event, so the formula =IF(B2=FALSE,D2,G2)
would return the contents of cell D2 if cell B2 contains 0 or the
contents of G2 otherwise.
FV(<payment>,<interest>,<term>)
Returns the future value of an annuity given the <interest>
rate, the <payment> amount per period and the number of
periods (<term>) according to the formula:
<payment> *((1+ <interest>)^<term>)-1 FV = ------------------------------------- <interest>
The interest rate is the period interest rate and is expressed in decimal form, i.e., 14% is expressed as .14.
HLOOKUP(<exp>,<range>,<offset>)
Performs a horizontal lookup on the values in the range of cells
<range>. The comparison values in the first row of the range
must be in increasing order with no duplicates. The function
finds the last cell in this row whose contents is not greater than
the given index <exp> (unless this is the first cell in the range
when ERR is returned). The function then returns the contents
of the cell which lies <offset> rows below it. Non-integer
values of <exp> are truncated to their integer part.
For example, in this worksheet:
B C D E 2 1 2 3 5 3 14 15 16 17 4 18 19 20 21 5 22 23 24 25
These are the values returned:
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.
IF(<exp>,<then>,<else>)
If the condition <exp> returns TRUE (1.0), the value of
<then> is returned, otherwise, the value of <else> is
returned. For example, the formula =IF(B2=7,D2,G2) would
return the contents of cell D2 if cell B2 contains 7 or the
contents of G2 otherwise.
INT(<exp>)
Returns the integer (i.e., the whole number part) of <exp>.
Negative numbers are rounded towards zero, so the formula
=INT(-5.7) returns the value -5.
IRR(<guess>,<range>)
Returns the approximate internal rate of return from a series of
discounted cash flows (i.e., the interest rate which would give a
net present value of zero on those cash flows). The required
arguments are an initial <guess> at the result and a cell
<range>. The <range> contains a series of positive (income)
and negative (payment) figures. Blank cells in <range> are
permissible. The cash flows are taken to occur at the start of
the period.
For example, in this worksheet:
B C D E 2 Guess Range 3 0.5 -1500 4 360 5 360 6 360 7 360 8 360 9
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.
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 rates 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-EXP((t2/t1)*LN(1+i1))
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, t1 is 12 months and t2 is 1 month, and the formula is:
i2 = 1-EXP(LN(1+i1)/12)
Conversely, if i1 is the monthly rate and i2 is the annual rate, the formula is:
i2 = 1-EXP(12*LN(1+i1))
Using this equation, we can calculate that a 2% monthly rate is equivalent to a 26.8% (to one decimal place) annual rate.
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.
ISERR(<exp>)
Returns TRUE (1.0) if <exp> returns ERR, otherwise returns
FALSE (0.0). For example, if cell B2 contains the formula =D2/0
(illegal divide by zero) the formula =ISERR(B2) returns TRUE
(1.0).
ISNA(<exp>)
Returns TRUE (1.0) if <exp> returns NA, otherwise returns
FALSE (0.0). For example, if cell B2 contains the formula
IF(B3>99,NA,B3), the formula =ISNA(B2) returns TRUE (1.0) if
the contents of B3 is greater than 99.
LN(<exp>)
Returns the natural (base e) logarithm of <exp>.
LOG(<exp>)
Returns the base 10 logarithm of <exp>.
MAX(<list>)
Returns the largest value in <list>. The <list> may be literal
values, cell references or cell ranges. Text cells are evaluated
to 0.
MIN(<list>)
Returns the smallest value in <list>. The <list> may be literal
values, cell references or cell ranges. Text cells are evaluated
to 0.
MOD(<exp>,<exp2>)
Returns the remainder from <exp> divided by <exp2>
(modulo). The arguments may be numbers or cell references.
MONTH(<exp>)
Given a number (<exp>) of elapsed days since 30.12.1899,
returns the month of the year in which that day lies. For
example, the formula =MONTH(31941) returns 6 because
31941 days after 30.12.1899 is 13th June 1987.
NA
Returns NA (Not Available). For example, the formula
=IF(B4>99,NA,B4) returns NA if the contents of B4 is greater
than 99 and returns the contents of B4 otherwise.
NPV(<interest>,<range>)
Returns the Net Present Value of a series of future discounted
cash flows for a given <interest> rate per period. The given
<range> of cells contains the positive (income) and negative
(payment) cash flows.
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 this worksheet:
B C D E 2 10.75 3 -1500 4 360 5 360 6 360 7 360 8 360 9
representing an initial payment of 1500 followed by five receipts of 360 at an interest rate of 10.75%, the formula =NPV(B2,C3:C8) would return a net present value of 1.248095E+02. This, when printed to two decimal places gives a profit on the investment of 124.80 units (pounds, thousands etc).
The interest rate should be given as a decimal, i.e., 14% should be given as .14.
OPL(<cell>,<range>)
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 main
Organiser user manual for details of this subject.
The OPL function adds two facilities to the conventional use of the spreadsheet. The first is the ability to call OPL procedures from any cell in the spreadsheet. The call is effected when the current worksheet is recalculated.
The second is the ability, when running an OPL procedure in the above way, to 'grab' the contents of cells in the worksheet from which the procedure was called.
For further details of the OPL function, see the appendix, The OPL Function.
PI
Returns the value of the mathematical constant Pi (3.142...).
PMT(<principal>,<interest>,<term>)
Returns the mortgage payment per period given the amount of
the loan (<principal>), the <interest> rate per period and the
number of periods (<term>).
The interest rate should be given as a decimal, i.e., 14% should be given as .14.
PV(<payment>,<interest>,<term>)
Returns the present value of an annuity, given the <payment>
per period, the <interest> rate per period and the number of
periods (<term>). For example, in this worksheet:
B C D E 2 3 Paymt 95 4 Int .1 5 Term 12 6
the formula =PV(D3,D4,D5) returns the value 647.30.
The interest rate should be given as a decimal, i.e., 14% should be given as .14.
RAND
Returns a random floating point number in the range 0.0
(inclusive) to 1.0 (exclusive).
ROUND(<exp>,<exp2>)
Returns the value or cell contents <exp> rounded to <exp2>
decimal places. The value of <exp2> must be in the range -11
to +11. If the value of <exp2> is less than 0 it returns the
value of <exp> rounded to the nearest ABS(<exp2>)^10.
Examples:
Formula Returns =ROUND(26858.30658,-3) 27000 =ROUND(26858.30658,-1) 26860 =ROUND(26858.30658,0) 26858 =ROUND(26858.30658,1) 26858.3 =ROUND(26858.30658,2) 26858.3 =ROUND(26858.30658,5) 26858.30658
SIN (<exp>)
Returns the sine of the (radian) angle <exp>.
SQRT(<exp>)
Returns the square root of <exp>.
STD(<list>)
Returns the standard deviation of the numbers in <list>. If all
cells in <list> are empty, ERR is returned. Blank cells in <list>
are ignored. Text cells are evaluated to 0.
SUM(<list>)
Returns the sum of the values or contents of cells in <list>.
Cells containing text are counted as 0.
TAN(<exp>)
Returns the tangent of the (radian) angle <exp>.
TODAY
Returns the number of days elapsed since 30.12.1899. For
example, if the Organiser's date is set to 26 August 1987, the
formula =TODAY returns the value 32015 because on that date
there are 32015 elapsed days since 30.12.1899.
TRUE
Returns the value 1.0. For example, cell B2 might contain 1 to
denote a certain event, so the formula =IF(B2=TRUE,D2,G2)
would return the contents of cell D2 if cell B2 contains 1 or the
contents of G2 otherwise.
VAR(<list>)
Returns the variance of the items in <list>. The items in <list>,
may be literal values, cells or cell ranges. If there are no non
blank cells or values in <list>, ERR is returned. Text cells are
evaluated to 0.
VLOOKUP(<exp>,<range>,<offset;>~)
Performs a vertical lookup on the values in the range of cells
<range>. The comparison values in the first column of the
range must be in increasing order with no duplicates. The
function finds the last cell in this column whose contents is not
greater than the given index <exp> (unless this is the first cell
in the range when ERR is returned). The function then returns
the contents of the cell which lies <offset> columns to the
right. Non-integer values of <exp> are truncated to their
integer part.
For example, in this worksheet:
B C D E 2 1 14 18 22 3 2 15 19 23 4 3 16 20 24 5 5 17 21 25
These are the values returned:
Formula Result =VLOOKUP(1,B2:E5,3) 22 =VLOOKUP(1.4,B2:E5,3) 22 =VLO0KUP(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.
YEAR(<exp>)
Given a number (<exp>) of elapsed days since 30.12.1899,
returns the year in which that day lies. For example, the formula
=YEAR(31941) returns 87 because 31941 days after
30.12.1899 is 13th June 1987.
When the grid is being displayed, press the MODE key to view the main spreadsheet command menu. This menu contains the following items:
| ALTER | Alter the contents of the current cell |
|---|---|
| DISPLAY | Switches between formula or cell display |
| FILE | Enter the FILE command menu |
| GOTO | Go to another cell in the grid |
| GRID | Enter the GRID command menu |
| QUIT | Leaves the spreadsheet and frees the memory used by it |
| TITLES | Switches Titles on or off |
Two items in this menu, GRID and FILE, when selected, each lead to a sub-menu of commands. These will be detailed later in this chapter. The remainder of the commands in the main spreadsheet command menu have the following uses:
Puts the display into INSERT mode. The contents of the current cell, or the formula which controls the current cell, if such a formula exists, can then be altered on the bottom line of the display by using the cursor keys and the DEL key and by typing new data. Press the EXE key to complete the alteration of the current cell and to insert the new contents in that cell.
Used to move 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. Press EXE to select this cell, or edit this to another cell reference. Press EXE to select the edited cell reference. The current cell is then that cell specified.
If there is a circular reference in the current worksheet when GOTO is selected, the default offered is one of the cells which contribute to the circular reference.
The spreadsheet has four display modes. The four modes are controlled by two commands DISPLAY and TITLES.
The TITLES command switches between TITLES ON and TITLES OFF. The DISPLAY command switches between FORMULA DISPLAY and CELLS DISPLAY.
The two commands together produce the four display modes which may be regarded as follows:
The default display mode is 1) in the list above, CELLS DISPLAY / TITLES ON. For further details of the display modes, see chapter 2, The Display.
Exits the Organiser 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 can not be reversed so ensure that the current worksheet can safely be discarded before selecting QUIT.
To return to the Organiser's 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 even returned to the last cell you visited.
When the GRID command in the main level 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 all 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 of the two cell references. 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.
Use the cursor keys to change the first cell reference, just as if you were scanning across the grid in the usual way, to the cell at the top left of the block of cells you wish the command to affect.
Press the MODE key to move the cursor across to the second cell reference. This may now be changed in the same way as the first.
Press EXE and the range of cells indicated will be affected by the command. Alternatively, press ON/CLEAR and the bottom line of the display will revert to that when the command was first selected; in this example:
DELETE: H16:H16
You may now begin altering the cell range again, as described above, or press ON/CLEAR to abandon the command and return to the grid.
When a cell range is displayed as above, if you press any of the alphabetic keys the range presented will be replaced by that character. This enables you to enter the whole cell range (including the separating colon) yourself. This will then be the range of cells on which the command will operate.
To leave the GRID command menu, press the ON/CLEAR key when the menu is displayed. This will take you back to the main spreadsheet command menu. From here, press the ON/CLEAR key again to return to the grid.
The commands in the GRID command menu have the following uses:
Used to delete 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.
Used to set the numeric display format for a range of cells or to set the default format for any blank cells in the grid.
When FORMAT is selected, a two item menu is displayed, containing the items DEFAULT and CELLS. When CELLS is chosen, the spreadsheet prompts for the cell range on which the command is to operate. Selecting DEFAULTS indicates that the command will operate on all blank cells in the grid. Cells already containing data will not be affected.
When the cell range has been entered as described earlier under the heading CELL RANGES, the four item menu FIXED, INTEGER, SCIENTIFIC and GENERAL is displayed.
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. GENERAL format tries to represent numbers in INTEGER format, then as a decimal (FIXED), then SCIENTIFIC.
INTEGER format is the same as FIXED format with 0 decimal places.
Used to set the column width for a range of columns. Only whole columns may be affected, you can not set, for example, cells 1, 2 and 3 in row 8 to be 5 characters wide and the rest of column B to be 7 characters wide.
When WIDTHS is selected, the bottom line of the display prompts for a range of columns. For example, if the current cell is in column F, the bottom line of the display will show:
WIDTHS: F:F
with the cursor flashing over the first F. Press the LEFT and RIGHT keys to change the leftmost F to the start column of the required range. Press the MODE key to move the cursor over to the second F. Now the LEFT and RIGHT keys may be used as above to set the end column of the required column range.
Press EXE and the bottom line of the display will prompt for the new column width. Any width from 1 to 12 may be entered. Press EXE to effect the change on the columns specified. At any time during the above process, one or more presses of the ON/CLEAR key will return you to the grid with no change having been made to the widths of cells in the grid. The default cell width is 5 characters.
Prints the current worksheet to a printer, one page column at a time, i.e., the worksheet below would be printed page by page in the order below:
----- ----- ----- ----- | | | | | | | | | | | | | | | | 1 | 2 | 3 | 4 | | | | | | | | | | | | | | | | ----- ----- ----- -----
Figure 7.1 The PRINT Command
This order means that when you print worksheets on continuous stationery, the vertical strips can be joined together to make a complete printout of your worksheet.
When PRINT is selected, if there is no data in the grid (i.e., you have just run the spreadsheet or used the ZAP command) you are returned immediately to the grid. Otherwise, the two item menu, ALL and RANGE is presented.
ALL prints the whole worksheet, i.e., a rectangular area of the grid which contains all the cells containing data or formulae. The results from formulae are printed, not the formulae themselves.
RANGE prompts for the range of cells to be printed. In either case, the display then prompts 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 being printed at the end of each page of cells.
This command will only operate when a Psion RS232 cable is connected to the Organiser. If no RS232 cable is connected to the Organiser, 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.
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.
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 from the original (see the chapter on Building a Worksheet for a detailed description of absolute and relative cell references).
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 this worksheet:
---- ----- ----- ----- ----- | | B | C | D | E ----+-----+-----+-----+----- | 2 | 1| 2| | ----+-----+-----+-----+----- | 3 | 3| 4| | ----+-----+-----+-----+----- | 4 | 5| 6| | ----+-----+-----+-----+----- | 5 | | | | ---- ----- ----- ----- -----
Figure 7.2 The COPY Command
if the source range is B2 to C4 and the destination ranges C4 to D6, there is a one cell overlap.
The COPY command works across the first row, then the next and so on. So in the above example, the first cell to be copied, B2 is copied to cell C4, the first cell in the destination range. 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 already been given the value 1, which is then duly copied to cell D6. So the original range of cells with the values:
1 2 3 4 5 6
have now become:
1 2 3 4 5 1
with the bottom right hand cell corrupted.
Don't worry about the amount of memory used by copying a formula to a few more cells than you need at the moment because when formulae are copied, whenever possible, the formulae are shared in the memory of the Organiser. So wherever two identical formulae appear in the worksheet, only one formula is stored, and both cells share the one copy. You don't need to be aware of the details of this process, just that the Organiser always makes the most efficient use of memory that it can.
When RECALC is selected, the two item menu MANUAL and AUTOMATIC is presented. AUTOMATIC sets the recalculation mode to automatic, so all formulae in the worksheet are recalculated every time the contents of any cell is changed.
When MANUAL is selected, formulae are recalculated only if the EXE key is pressed when the worksheet is being viewed.
This command 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.
If you do so, the latest modifications to your data which have not been saved to a device will be lost. Ensure that any important date is saved to a device before using this command as it can not be reversed.
All of the commands in the FILE command menu are concerned with the transfer of worksheets to Datapaks, Rampaks or a PC connected via the Psion Comms Link package.
When the FILE command in the main level spreadsheet command menu is selected, a further menu is displayed containing the following items:
| LOAD | load a worksheet file |
|---|---|
| SAVE | Save a worksheet file |
| IMPORT | Import a worksheet file from a PC. |
| EXPORT | Export a worksheet to a PC. |
| DIR | Read the directory of worksheet files |
| ERASE | Erase a worksheet file |
Used to load a saved worksheet from a device into memory, replacing any current worksheet.
When LOAD is selected, if the current worksheet has been modified since it was last exported or saved, you will be prompted to confirm that you want to load in a new file. If you type Y (yes), the display will show:
LOAD A:
Press the MODE key to change the current device. Type the name of the worksheet file to be loaded and press the EXE key. If the worksheet you want to load was saved with a password, that password must be given along with the filename, separated from it by a semicolon. For example: FOLIO;CRUNCH loads the file FOLIO which was saved with the password CRUNCH. Passwords of up to six characters are allowed.
Used to save the current worksheet to a device. When SAVE is selected, the display shows:
SAVE A:
Press the MODE key to change the current device. Then type in the name the worksheet should be saved with and press EXE.
You may also add a password of up to six characters when you save the worksheet. When you type the name the worksheet is to be saved under, add a semicolon and the password of your choice. For example, to save the current worksheet under the name FOLIO with the password CRUNCH, when you are prompted for the SAVE name, type:
FOLIO;CRUNCH EXE
The display will give the message SAVING. When this process is finished you will be returned to the grid. To load this worksheet the same password must be given with the filename in the same format when you follow the LOAD process.
If the current device already contains a worksheet under the specified name, you will be prompted to confirm that this name is to be used. The old worksheet with this name will be deleted and the new one saved. If there is insufficient memory on the current device to save the current worksheet you will be given the chance to change to another device or abandon the SAVE.
If the battery becomes low while saving a worksheet to a Datapak, the BATTERY LOW message will be displayed and you will be prompted to press the SPACE key. You will then be returned to the grid with the data intact. You should then press ON/CLEAR to leave the spreadsheet. Your worksheet is still in memory, and it will remain there while you fit a new battery. You will then be able to save the worksheet as planned.
SWITCH THE ORGANISER OFF BEFORE ATTEMPTING TO CHANGE THE BATTERY. If you do not do so then all data in the machine, including the current worksheet, will be lost.
When the Organiser is safely switched off, change the battery as described in chapter 2 of the Organiser Operating Manual and return to the spreadsheet. Select SAVE from the FILE menu and continue.
Using a mains adaptor prevents this problem and makes the process of saving to Datapaks faster. Contact your supplier for details of the Psion Mains Adaptor.
The IMPORT command is used to load a worksheet from a PC, replacing any current worksheet. Before you select this command from the FILE command menu, the Organiser must be connected to a PC with the Psion Comms Link cable. The PC must be running the Comms Link program supplied with the Comms Link cable. If the Comms Link cable is not connected when IMPORT is selected, an error is reported and you are returned to the FILE menu.
If the current worksheet has been modified since it was last saved or exported, the IMPORT command will first prompt you to confirm that you want to overwrite it. If you reply Y (yes), a menu containing the types of file you can import will be presented. See the Import/Export File appendix at the back of this manual for the file types supported. Select the file type you want to import. The display will show:
IMPORT :
You should then type the name of the file to import, including an optional pathname and an optional file name extension. If you do not give the extension, it will be assumed to be the default extension for the selected file type, as specified in the Import/Export File appendix.
In the pathname, use the slash character in place of the DOS backslash. For example, to import the file SALES.WKS which is in the directory \SPREAD, select WKS from the IMPORT menu and type:
/SPREAD/SALES
When you press EXE the message IMPORTING is displayed while the file is loaded Into memory via the Comms Link. When the IMPORT is complete you are returned to the grid with the imported worksheet in place.
Imported worksheets must not cover a range greater than 26 columns wide and 99 rows long. Cells outside this range will be discarded.
If any errors are detected during the IMPORT process, an ERRORS FOUND message will be displayed on the Organiser screen at the completion of the transfer. The types of error and the cell references where they occurred are displayed on the screen of the PC. The formulae generating the errors are replaced in the imported worksheet with the formula =ERR.
If more than 15 errors occur during an IMPORT the command will be aborted and you will be returned to the grid. The partially imported file will still be retained in the Organiser's memory.
Formulae longer than 250 characters will be replaced by the formula =ERR and the error total on the PC will be incremented by one.
The EXPORT command is used to save the current spreadsheet to a PC. Before you select this command from the FILE command menu, the Organiser must be connected to a PC with the Psion Comms Link cable. The PC must be running the Comms Link program supplied with the Comms Link cable. If the Comms Link cable is not connected when EXPORT is selected, an error is reported and you are returned to the FILE menu.
The command first presents a menu showing export file types. Select from the menu the file type you want the exported PC file to be. The screen will then show:
EXPORT :
You should then type the name of the file to export, including an optional pathname and an optional file name extension. If you do not give the extension, it will be assumed to be the default extension for the selected file type, as specified in the Import / Export File appendix.
When you press EXE the message EXPORTING is displayed while the file is saved to the PC via the Comms Link. When the EXPORT is complete you are returned to the grid with the worksheet still in place.
If the exported worksheet contains formulae which use the OPL function, each of these will be replaced with the formula =ERR. Each one is reported on the screen of the PC, giving the cell reference where it occurred. If more than 15 errors are generated, the transfer will be aborted and you will be returned to the grid with the worksheet still in place.
Reads the directory of saved worksheets on the internal memory of the Organiser or a Datapak or Rampak. When DIR is selected the display shows:
DIR A:
Press the MODE key to change the current device. Press the EXE key and the name of the first worksheet saved on the current device is displayed. Subsequent presses of the EXE key show other worksheet files saved on the same device until the last has been shown, when an END OF PACK message is displayed. Press ON/CLEAR to return to the FILE menu.
Erases a saved worksheet file from a device. When ERASE is selected, the display shows:
ERASE A:
Press the MODE key to change the current device. You should now type the name of the worksheet file you wish to erase from the specified device and press the EXE key. The file will be deleted and you will be returned to the grid.
If no worksheet file of that name is found on the specified device a FILE NOT FOUND error is reported. Press ON/CLEAR to return to the grid.
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 main Organiser user manual for details of this subject.
The OPL function adds two facilities to the conventional use of the spreadsheet. The first is the ability to call OPL procedures from any cell in the grid. The call is effected when the current worksheet is recalculated.
The second is the ability, when running an OPL procedure in the above way, to 'grab' the contents of cells in the worksheet from which the procedure was called.
The syntax of the OPL function is:
OPL(<cell>,<range>)
The first argument, <cell>, is the reference of a cell which must contain text. This text must be a valid procedure name excluding the trailing colon. The procedure must be stored in either the internal memory of the Organiser, or a Datapak or Rampak fitted to the machine.
The second argument, <range>, is a cell range, whose cells may contain numbers, formulae, text or they may be blank.
When the current worksheet is recalculated, the OPL function passes the name of the procedure to OPL where it is run in the usual way. The procedure may return an integer or a floating point value. If the specified procedure is a string procedure an error will be generated and the OPL function will return ERR.
The named procedure must not have any declared parameters. Values are passed to the procedure from the spreadsheet by means of two new functions which are added to OPL.
Two new functions are added to OPL while the spreadsheet is installed in the Organiser:
CELL: and CELL$:.
These functions return the contents of the next non-blank cell in <range>.
CELL: returns a floating point value from the 'next' cell
CELL$: returns a string value from the 'next' cell
The values in the cells in <range> may be accessed by the named procedure from left to right and from top to bottom, e in the order:
---- ---- | 1 | 2 | ----+---- | 3 | 4 | ---- ----
Figure A.1 OPL Parameter Order
Once the contents of a cell have been accessed from the spreadsheet, an internal pointer is set to the next non-blank cell in the range. You must use the appropriate function for numbers and text, depending upon the contents of the next non-blank cell in the range. For example, if you use the CELL$: function in the procedure when the next cell contains a number, an error will be generated.
The error, unless trapped with OPL's ONERR command, will be reported in the usual way, the procedure will end and the OPL function in the spreadsheet will return the value ERR.
If you attempt to access cells in <range> more times than there are non- blank cells, an error will be generated, the procedure will end and the OPL function will return ERR. No error is generated if you do not access all the non-blank cells in <range> before returning to the grid.
If the procedure ends successfully, the value returned by it will then be returned to the cell containing the OPL function.
If <range> contains only one cell, <range> must still be a fully specified range in the format B2:B2.
When the OPL function is used, the named procedures are called every time the worksheet is recalculated. You should therefore set the recalculation mode with the RECALC command to MANUAL with the RECALC command before setting up the worksheet.
The share portfolio worksheet set up in chapter 4 can be improved by using the OPL function to prompt for each of the new daily share prices. Here is the worksheet as seen earlier:
---- --------- --------- --------- --------- | |FOLIO |TODAY |QTY |SUB-T | ----+---------+---------+---------+--------- |NOW | 3190.00| | | | ----+---------+---------+---------+--------- | | | | | | ----+---------+---------+---------+--------- |BT | | 2.37| 1000| 2370.00| ----+---------+---------+---------+--------- |TSB | | 0.90| 500| 450.00| ----+---------+---------+---------+--------- |GAS | | 0.75| 200| 150.00| ----+---------+---------+---------+--------- |BA | | 1.10| 200| 220.00| ----+---------+---------+---------+--------- |8 | | | | | ---- --------- --------- --------- ---------
Figure A.2 Share Portfolio Example
The OPL function can be used to call a procedure called ASK:, which prompts for a price for each of the shares held and to insert that price into the appropriate cell in the worksheet.
First the procedure ASK: must be written in the usual way under the PROG option in the Organiser's top-level menu.
This is the procedure:
ASK: LOCAL a PRINT "Todays value of" PRINT CELL$:;":"; INPUT a RETURN a
This procedure will be called from each cell where the price of a share was entered in the original worksheet.
Set the recalculation mode to AUTOMATIC with the RECALC command; enter the text ASK into cell F2, then enter the formula =OPL($F$2,A4:A4) into cell C4. Now use the COPY command to copy this formula into cells C5 to C7, the other a price cells.
The formula uses the OPL function, whose two parameters are:
Recalculate the worksheet by pressing EXE when the grid is displayed and the formulae in cells C4 to C7 will each call the a procedure called ASK: and prompt for the latest price like this:
Todays value of BT
You may now type in today's price for the share shown on the bottom line. When you press EXE the price will be entered into the appropriate cell. Each of the price cells will prompt for the latest price in the same way. When the last price has been entered, you will be returned to the display of the grid and the final result can be read from cell B2.
If an error occurs while using the spreadsheet, the message displayed may be one of the Organiser's error messages, which are detailed in the main Organiser user manual, or it may be one of the following:
More than 15 errors have been recorded during the transfer of a file between the spreadsheet and a PC.
When transferring worksheets to or from a PC, errors may occur due to an illegal function being present in the file in transfer. For example, when exporting a worksheet file to a PC, if the worksheet contains formulae which use the OPL function, each occurrence of the function will generate an error. Similarly, when importing a Lotus 1-2-3 worksheet, if the file contains formulae which use the database statistical functions, errors will be reported.
If more than 15 such errors are recorded during the transfer of a file, the transfer will be aborted and the TOO MANY ERRORS message will be displayed on the Organiser.
The errors and the references of cell where they occurred are displayed on the screen of the PC.
See the IMPORT and EXPORT commands in the Commands chapter and the Psion Comms Link manual for further details on this error.
While saving a file to the PC, the PC failed to open or write to a file. A further message is displayed on the screen of the PC. See the Psion Comms Link manual for further details on this error.
The link between the Organiser and the PC either failed to occur when IMPORT or EXPORT was selected, or failed during execution of one of these commands. Check the Comms Link cable is connected and that the ORGC program is running on the PC; check the cable is plugged into the Organiser and press they ON/CLEAR key at the Organiser's top-level menu to load the communications software into the Organiser.
See the TOO MANY ERRORS error above. The ERRORS FOUND message will be displayed if not more than 15 errors were detected during importing or exporting a file.
An attempt has been made to save a file with a password of more than six characters. Passwords may contain any characters which can be typed in on the Organiser's keyboard. See the Commands chapter for details of passwords under the headings SAVE and LOAD.
When IMPORT or EXPORT is selected from the FILE command menu, a CONNECTING message is displayed on the Organiser. Assuming the connection is made with the PC as described in the Comms Link manual, a menu is then presented on the Organiser, showing the types of spreadsheet file which can be transferred between the PC and the Organiser.
Most spreadsheets have an option to save .DIF files. These are plain-text files which may then be imported to other spreadsheets, including the Organiser spreadsheet. These files contain only text and the evaluated results of the formulae at the time the file was saved. The default file extension is DIF.
This option enables you to import files created by Lotus 1-2-3 release 1 and 1A. These files may be imported into the Organiser spreadsheet and used as if they had been typed directly into the Organiser. The default file extension is .WKS.
This option enables you to import files created by Lotus 1-2-3 release 2 and Lotus Symphony release 1.1. These files may be imported into the Organiser spreadsheet and used as if they had been typed directly into the Organiser. The default file extension is .WK1.
In both WKS and WK1 files, formulae are transferred intact provided they are not longer than 250 characters and do not contain any illegal functions. The functions which are not allowed on the Organiser spreadsheet are the database statistical functions, DAVG, DCOUNT, DMAX, DMIN, DSTD, DSUM and DVAR.
If formulae are encountered which use these functions, an error message will be displayed on the screen of the PC. Cell width settings on Lotus worksheets are discarded during the IMPORT process, so some columns may need their width altered in the Organiser spreadsheet before using the worksheet.
A ABS function Absolute cell references, (2) ACOS function ALTER command, (2) Arithmetic operators ASIN function ATAN function ATAN function (2) AVG Function C Cell current reference Cell ranges, (2) Cell reference, (2) Cell references absolute, (2) mixed, (2) relative, (2) CHOOSE function Circular reference, (2) Column Column titles, (2), (3) Command menu FILE, (2), (3) GRID, (2), (3) MAIN, (2), (3) Command menus Command selection Commands ALTER COPY DISPLAY, (2), (3), (4) FORMAT GOTO, (2) QUIT RECALC, (2) TITLES, (2) WIDTHS, (2) ZAP, (2), (3) Comms Link Comparison operators COPY command, (2) COS function COUNT function Cursor movement D Data entry numbers, (2) text Data types DATE function DAY function DELETE command Deleting cell contents, (2) Deleting characters DIR command, (2) Display hashes (#) DISPLAY command, (2), (3), (4), (5) Display modes, (2) E Editing input ERASE command, (2) ERR function EXP function EXPORT command F FALSE function FILE command menu, (2), (3) FIXED numeric format FORMAT command, (2) Formula, (2), (3), (4) removal sharing, (2), (3) viewing Functions ABS ACOS ASIN ATAN ATAN (2) AVG CHOOSE COS COUNT DATE DAY ERR EXP FALSE FV HLOOKUP IF INT IRR ISERR ISNA LN LOG MAX MIN MOD MONTH NA NPV PI PMT PV RAND ROUND SIN SORT STD SUM, (2) TAN TODAY TRUE VAR VLOOKUP YEAR FV function G GENERAL numeric format GOTO command, (2), (3) Grid clearing, (2) size, (2) GRID command menu, (2), (3) H HLOOKUP function I IF function IMPORT command Initial display Input mode, (2) INT function INTEGER numeric format IRR function ISERR function ISNA function J Justification K Keyboard mode L LN function LOAD command, (2) LOG function LOW BATTERY, (2) M MAIN command menu, (2), (3) MAX function MIN function Mixed cell references, (2) MOD function MONTH function Multiplication tables N NA function NPV function Numbers position Numeric accuracy Numeric format FIXED GENERAL INTEGER SCIENTIFIC O Operators P PI function PMT function PRINT command PV function Q QUIT command, (2) R RAND function Range identifiers Range References RECALC command, (2), (3) Recalculation Recalculation modes Relative cell References, (2) ROUND function Row references Row titles, (2), (3) S SAVE command, (2) SCIENTIFIC numeric format Screen scrolling Selecting commands Share portfolio SIN function Spreadsheet commands ALTER COPY DELETE DIR, (2) DISPLAY ERASE, (2) EXPORT FORMAT GOTO IMPORT LOAD, (2) PRINT QUIT RECALC SAVE, (2) TITLES WIDTHS ZAP SQRT function Status area, (2), (3) STD function SUM function, (2) T TAN function Text position Title reference, (2) Titles, (2), (3) column, (2) removal, (2) row, (2) suppressing TITLES command, (2), (3) TODAY function TRUE function V VAR function VLOOKUP function W WIDTHS command, (2), (3) Worksheet Y YEAR function Z ZAP command, (2), (3), (4)