Psion Homepage Logo.

The Spreadsheet Manual

Part Two, Reference

General

Grid layout
Input
Display modes
Cell references
Relative and absolute cell references
Calculation

Functions

Menu Items

The OPL Function

 

Psion bar.

Grid layout

The Spreadsheet grid is 26 columns wide (labelled A to 2) by 99 rows deep (1 to 99). The columns are initially:

The width of columns may be set to between 1 and 12 characters (6 for column A). Rows are one cell deep adjustable).

Titles

A title is the contents of a cell in column A or in row 1. It these cells contain no data they are given the default display values A, B, C... across the top of the grid or 1, 2, 3... down the left of the grid. These are always displayed ranged left.

These values are for display purposes only - the cells are empty and, unless you enter numbers yourself, will evaluate to 0.0 if accessed by a formula elsewhere in the grid.

You can replace the default titles with text or numbers of your choice.

To suppress the titles which are displayed when the Spreadsheet is first run:

To restore a title suppressed in this way:

Psion bar.

Input

To enter data:

The Spreadsheet categorises your input according to the first character of the string entered: as a number, a formula or a text string. Input is regarded as text unless:

Numerical expressions not beginning with = are evaluated and stored as a number.

The Spreadsheet will not accept 2TEXT as an entry because it mixes categories, but it will accept TEXT2, allowing you to number text strings. You can enter 2TEXT as text by entering "2TEXT. The " acts as an identifier - it is not displayed.

Numbers

If a number outside these ranges is entered in a cell, either directly or through a formula, that cell will be filled with asterisk characters. Other cells which reference such a cell will also be filled with asterisks.

If the numeric format of a cell is set such that not all the decimal places of the number in it are shown, the number shown is the number rounded to the nearest next digit. E.g. if a cell is set to display only two decimal places and the contents of that cell is the number 1.237, the value 1.24 will be displayed.

Numbers are displayed ranged right, with the exception of the title numbers in column A (and row 1 if you enter numbers in these cells).

If a numeric string is longer than the cell width, it is displayed only as a series of # signs. Use the Widths command to reset the width of the cell so that the number can be displayed. For example, a cell containing the value 9887.00 is in a column set to only six characters wide. The cell displays '######' until the width of the column is set to 7 or more characters.

When not all of a cell is visible on screen and the cell contains a number, a --> sign is shown to indicate that number is not displayed in full.

Text

The maximum permitted length of any text string stored in a cell is 250 characters. If a text string is longer than the cell width, it overflows into adjacent cells to the right (except for titles). Text is always displayed ranged left. If a formula references a cell containing text, that cell is evaluated to 0.0. The ‘ " ‘ symbol is a flag indicating that a cell's contents are regarded as text. It appears when the display is in 'Info on' mode or when a cell is being viewed under the Edit command.

Editing and deleting cell contents

To delete the contents of a single cell:

To delete the contents of a range of cells:

To delete the contents of all cells and reset the column widths and numeric formats to their default states:

To edit a numerical or text entry while typing it in:

To edit data already entered:

(See also Defining a cell range)

Psion bar.

Display modes

Four display modes are available, controlled by two ‘switches’: the Titles command and the Info command in the Spreadsheet menu. The four modes are:

Titles on/Info off

This is the default display mode, in which the screen typically looks like this:

       |D5  |MAR    |APR    |
       |PROF|_   354|    128|    or    |D5  |MAR  |APR  |
       |6   |       |       |          |PROF|_ 354|  128|
       |7   |       |       |

Here the current cell is D5, and it contains the display 354. The cell at the top of the current column contains the title ‘MAR’; the cell at the left of the current row contains the title 'PROF'.

Titles on/Info on

From the default setting, select the Info command so that any formula in the current cell can be seen on the bottom line, for example:

       |D5  |MAR    |APR    |
       |PROF|_   354|    128|    or    |D5  |_ 354|  128|
       |6   |       |       |          |MAR.PROF:=D3-D4 |
       |MAR.PROF:=D3-D4     |

To the left of the bottom line is a title reference made up of the titles in the cell at the top of the current column and the cell at the left of the current row. Following this is the formula in the current cell.

Titles off/Info on

The first two modes both feature the titles from the top of the current column and the left of the current row. To switch the titles off, select the Titles command. In the example above, the display now shows:

       |D5  |_   354|    128|
       |    |       |       |    or    |D5  |_ 354|  128|
       |*fc |       |       |          |=D3-D4          |
       |=D3-D4              |

If the current cell contains a formula. this mode shows the formula on the bottom line of the display.

Otherwise, any numerical or text entry in the current cell is shown on the bottom line. On model LZ the flags '*', 'f' or 'c' may be shown (see below).

Titles off/Info off

While the titles are switched off, use the Info command again to remove the formula shown on the bottom line. The display now shows:

       |D5  |_   354|    128|
       |    |       |       |    or    |D5  |_ 354|  128|
       |    |       |       |          |*fc |     |     |
       |*fc |       |       |

The current cell, D5, contains the display value 354.

On the bottom line is a status area which can show up to three 'flags'.

The flags which can appear in the status area are as follows:

*

The Spreadsheet has been modified since the last calculation. This will be shown only after the contents of a cell have been altered when the Spreadsheet is In Manual recalculation mode (see Recalc). To ensure all displayed values are valid, recalculate the worksheet by pressing the EXE key when the grid is being displayed.

f The current cell contains a formula. If you set Info on the formula will be shown on the bottom line of the display.
c The current Spreadsheet contains one or more circular references.


As you use the cursor keys to scroll from cell to cell, the right hand area of the display will show the contents of the current and surrounding cells, and the left hand side will change to show the current cell reference and the status of that cell.

The flags '*', 'f ' and 'c' always appear on the display in the order '*fc' (though all three may not necessarily be present at one time). They may be generated in any order, depending on the contents of the grid or the current cell.

Psion bar.

Cell references

Single Cells

A reference to a single cell consists of two parts: a column and a row reference. Typical cell references are:

Range References

A range reference is made up of two cell references. separated by a colon. You must always type in the colon to separate the two parts of the reference. The first cell reference specifies the top left hand corner of the block and the second the bottom right hand corner. Examples of range references are:

Row and Column References

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:

Psion bar.

Relative and absolute cell references

Relative Cell References

The Spreadsheet assumes that, unless otherwise specified, all cell references are relative, i.e. that the difference hi position between the cell containing the reference and the cell to which it refers is the important thing.

When you copy such a reference into another cell, the references are modified to keep this relative difference.

For example, imagine that a formula in cell C3 contains a reference to cell B2 (one column to the left and one row above). If the formula in cell C3 Is copied into cell E5 it will, in this new location, refer to cell D4 (one column to the left and one row above).

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

Absolute Cell References

You can make any cell reference absolute by prefixing the letter for the column or the number for the row, or both, with a dollar sign ($). Such a reference will not be modified when the formula is copied to other cells.

For example, If a formula in cell D3 refers to cell $B$2 and you copy this formula to another cell, that too will refer to cell $B$2.

Mixing Absolute and Relative Cell references

The two types of cell references. absolute and relative. may be mixed freely, giving references which are part absolute and part relative. For example:

Psion bar.

Calculation

Formulae

A formula may consist of allowed combinations of functions, cell references, numbers and arithmetic operators.

Formulae must start with an equals character. They may be up to 250 characters long. Examples are:

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.

Permitted Operators

(a) Arithmetic Operators

+

add

-

subtract

*

multiply

/

divide

**

raise to the power

-

unary minus (make negative)

+

unary positive (make positive)


(b) Comparison Operators

>

greater than

>=

greater than or equal to

<

less than

<=

less than or equal to

=

equal to

<>

not equal to


(c) Logical Operators

NOT
AND
OR

Precedence

The operators have the following precedence. The precedence numbers merely indicate whether an operator has precedence higher, lower or equal to another operator:

Operator

Meaning

Precedence

**

power

7

-

(Unary minus) make negative

6

+

(Unary plus) make positive

6

*

multiply

5

/

divide

5

+

add

4

-

subtract

4

=

equal to

3

>

greater than

3

>=

greater than or equal to

3

<

less than

3

<=

less than or equal to

3

<>

not equal to

3

NOT

logical NOT

2

AND

logical AND

1

OR

logical OR

1

Where precedence is equal, calculation is from left to right. Use brackets to override the order of precedence.

Recalculation

The Pocket Spreadsheet supports natural order recalculation, in which formulae which do not depend upon other formulae are recalculated first, followed by formulae which rely on them and so on.

The default recalculation mode is Automatic. This may be changed to Manual with the Recalc command in the Grid command menu. The worksheet can always be recalculated by pressing the EXE key when the grid is displayed.

Circular References

The presence of a circular reference means in its simplest sense, that the contents of a cell is dependent on its own contents - for example, if cell B2 contains the formula =B2+1.

A circular reference consisting of two cells might appear like this: cell B2 contains the formula =D2+1, and cell D2 contains the formula =B2+1.

Circular references can be far more complex than these simple examples and lead from cell to cell all around the Spreadsheet, finally returning to the cell from which they started. These will always be detected by the Spreadsheet and indicated with the 'c' flag in Titles off/Info off display mode.

The presence of a circular reference is usually an Indication that you have made a mistake. In certain cases you may need a circular reference in your application, but note that the values of cells involved in the circle may then alter undesirably when the worksheet is recalculated; their values will mount each time the sheet is recalculated.

When a circular reference has been detected, the default destination cell offered in the Goto command will always be one of the cells contributing to the circular reference.

Note that, if the recalculation mode is Manual, the circular reference indicator 'c' may be unreliable. The Spreadsheet is checked for circularity, and the circularity indicators are updated, only when the worksheet is recalculated. Manually recalculating the worksheet by pressing EXE when the grid is displayed will update the status indicators.

 

These pages should be viewed using Netscape 4.03 or Microsoft Internet Explorer 3.02 at 800x600 pixels.
Left arrow Tutorial ~ Homepage ~ Function Reference Right arrow