Number cells enable you to perform calculations automatically and to display the result in the document. You can use number cells to perform one or two calculations within a text document. Or, by creating a table of number cells, you can build a large spreadsheet to perform a complete business plan, accounting application, or project forecast. This section first describes how to create number cells. Then it describes the operations available in the formulae entered into number cells, which can include arithmetic between numbers, references to other cells, and a selection of useful functions for working with numbers. You can alter the format in which numbers are displayed and you can even make the value of a number cell appear in the middle of a line of text. Finally, you can transfer values between documents.
You can enter a wide range of mathematical formulae into number cells. Formulae can contain numbers, dates, text strings, conditions, arithmetic operators, functions and references to other cells. Number cells display the result of evaluating the formula rather than the formula itself.
If you want to create a large number of number cells, when building a spreadsheet for example, it is a good idea to set the New cell format option in the Options dialogue box to Numbers.
If you require number cells within text, you can create number cells individually.
To start editing a number cell, click in the formula line (this is at the top of the toolbar).
Clicking in the formula line has the following effects:
Giving the Edit Formula command has the same effects:
Menu | Command | Cmd-sequence | Key |
---|---|---|---|
Edit | Edit formula | X | F2 |
To enter the coordinates of any cell into the formula line or formula window, click in the cell.
For example, if you click in cell A4, A4
will appear on the formula line.
You can also click in a cell in another document to insert its reference into the formula line of the document you are editing.
You can click Select or Adjust to position the caret at a point in the formula line or formula window.
You can use the following keys to edit the number in the formula line or formula window:
→ | Moves the caret one character to the right |
← | Moves the caret one character to the left |
Shift- → | Moves the caret left one word |
Shift- ← | Moves the caret right one word |
Ctrl- ← | Moves the caret to the beginning of the line |
Ctrl- → | Moves the caret to the end of the line |
Delete or ↤ | Deletes the character to the left of the caret |
Copy | Deletes the character to the right of the caret |
Ctrl-Copy | Deletes forward to the end of the line |
Ctrl-U | Deletes the whole formula |
Ctrl-Enter | Enters a newline into the formula in the formula window |
To enter the formula you have typed on the formula line or in the formula window into the document, click the Return↵. Alternatively, you can press ↓ (down arrow) to enter the formula and automatically move down to the next cell. Alternatively, you can abandon editing and leave the cell unchanged by clicking the toolbar button or pressing Escape.
toolbar button or pressIf you want to create a new number cell containing a formula consisting of more than one line, you can press Cmd-EFW or click on the PipeDream icon to open the formula window. A window appears on the screen into which you type your number cell contents. You can use the keys listed above for editing the contents of the formula window.
You can also mark and delete blocks within the formula window. To mark a block, position the caret where you want the marking to start and drag until the block is marked. Pressing Delete or ↤ will delete the marked block. To extend the marked block, position the caret at the point to which you want to extend and click Adjust. If you have a block marked, anything you type will replace the marked block and repositioning the caret will clear the marked block.
In the formula window or formula line, you can insert spaces into your formulae to make them more readable.
Note that in the formula window you need to press Ctrl-Enter to start a new line; pressing Return↵ has the same effect as clicking the toolbar button.
You can paste functions into the formula line or the formula window without having to type them in. Position the caret in the cell in which you want the function to be inserted. Move the pointer onto the
toolbar button. The pointer shape will change. When you click Select, you will see the menu which lists the various types of PipeDream functions. Slide off the type of function you want. You will see a list of functions belonging to that category. Click Select on the function you want. It will be inserted into the formula line or the formula window at the caret position. The caret will be repositioned between the function parentheses ready for you to type parameters to the function.If you are typing a formula into the formula line and decide to use the formula window instead, you can press Ctrl-Enter to transfer the contents of the formula line into the formula window.
Formulae may be composed of the following elements, defined below:
Element |
Example |
Arrays |
{1,2,3}
|
Dates |
5.9.1987
|
Functions |
SIN(3/2)
|
Lists |
1,4,6,9
|
Names |
Fred
|
Numbers |
2
|
Operators |
+
|
Cell references |
A23
|
Ranges |
A1W70
|
Strings |
"January"
|
Times |
19:30
|
An array is a group of items within one number cell. Arrays consist of columns and rows of data.
Enter an array by enclosing the array contents in curly brackets. Data in the same row in an array is separated by commas; rows in the array are separated by semi-colons.
Arrays can contain numbers, dates, strings, names or cell references.
Arrays cannot contain ranges or other arrays.
You can mix different types of data in the same array.
Dates can be displayed in British, American or textual format, which is specified in the Options dialogue box. If the option is set to Text, you must enter the date in British format.
British format: day.month.year
30.4.1991
American format: month.day.year
4.30.1991
Textual format: day.month.year
30 Apr 1991
in a number cell or
30 April 1991
in a text cell
Dates and times can be sorted into chronological order, and PipeDream has functions that manipulate time and date values. The function TODAY returns the current date.
Dates run from year 100 AD onwards. Dates less than 100 are assumed to be relative to the current century. For example, if you enter 1.1.17, PipeDream treats this as 1.1.2017 and displays it thus. If you enter 1.1.100, PipeDream treats this as 1.1.100. 1.1.1756 will be treated by PipeDream as 1.1.1756.
Times run from midnight each day. Enter times in the format 6:30 or 19:20. Seconds are optional, for example 18:20:30.
You can enter a combined date and time in the form
1.6.1991 6:30
Functions perform an operation upon the parameters which follow them and return a result.
INT(A1)
returns the integer part of A1.
Certain functions can take a list of elements, separated by commas. Ranges of cells and arrays can be included among the list elements, in which case each element in the range or array is considered as a separate list element.
1, A1A5, -2
is a list containing 7 elements (not 3), because the range A1A5 refers to 5 cells.
Names can be defined to refer to data items - constants, cells, ranges of cells, or arrays. Then, instead of using the data item itself in formulae, you can use the name, which is probably easier to remember. If you want to change the value of the data item, changing the name is probably easier than finding all the places where you have used the data item.
Names are saved and loaded with the spreadsheet on which they were defined. Names can refer to data items on another spreadsheet.
You define, alter and insert names using the
option on the menu obtained by clicking the toolbar button.Names can be up to 24 characters long.
The characters a-z and _ (underscore) are allowed in names. The digits 0-9 are only allowed after the underscore character.
Valid Names are: Apple box_1 x
Invalid Names are: a1 red63
In both these invalid cases, there is a digit which is not preceded by an underscore character.
Exponential format can be used. All calculations are performed to an accuracy of 15 figures.
1
-1.76576
1.4e3
0.1
Brackets can be included in formulae.
The arithmetic operators take two numbers as operands.
+
add
-
subtract
*
multiply
/
divide
^
raise to the power
The logical operators operate on numeric or logical values.
Logical FALSE is taken as zero, TRUE as non-zero.
A true condition returns the value TRUE (1).
&
AND
|
OR
!
unary NOT
Note that if the left hand side of an AND expression evaluates to FALSE, the right hand side is not evaluated.
Similarly, if the left hand side of an OR expression evaluates to TRUE, the right hand side is not evaluated.
The relational operators can compare dates, strings, and numbers.
Both operands must be of the same type.
The result is a Logical value of FALSE (zero) or TRUE (one).
<
less than
<=
less than or equal to
<>
not equal to
=
equal to
>
greater than
>=
greater than or equal to
When strings are being compared, the following wildcards are allowed in the second string:
^?
matches any single character not including space
^#
matches any number of characters
^^
represents ^
If cell A1 contains Macmillan
, then
A1="M^#cmillan"
has value one (TRUE),
A1="Mc^#"
has value zero (FALSE),
A1="^?^?c^#"
has value one (TRUE).
The order of precedence for the operators mentioned above is:
Group 1 |
-
|
Unary minus Brackets Unary NOT |
Group 2 |
^
|
Raise to power |
Group 3 |
*
|
Multiply Divide |
Group 4 |
+
|
Plus Minus |
Group 5 |
=
|
Equals Not equal to Less than Greater than Not greater than Not less than |
Group 6 |
&
|
AND |
Group 7 | | |
OR |
Certain functions can operate on a block of cells, specified by a range descriptor of the form
cell_ref1
cell_ref2
,
where cell_ref1
(see Cell References) specifies the top left-hand corner of the block,
and cell_ref2
specifies the bottom right-hand corner of the block.
The cells referred to can be in another document, providing you put [filename]
first,
where filename is the name of the document containing the cells being referred to.
As with cell references, ranges are normally ‘relative’ which means that, if the range is replicated or copied from one cell to another, the difference in position between the two cells will be added on to or subtracted from the range. Each half of either cell reference in the range may be ‘fixed’ (made ‘absolute’) by preceding the column letter or the row number by a dollar sign (‘$’) (see Cell References).
A1A100
A1A1
[fred]B2C3
AC6AD10
Q$5R$6
The form of a cell reference is column_label
row_number
where column_label
designates a column,
A
to Z
,
AA
to ZZ
,
AAA
to ZZZ
…
and row_number
is a number starting at 1.
If the reference is to a cell in a different document from the one being edited,
[filename]
is put before the cell reference where
filename is the name of the file containing the cell being referred to.
Cell references are normally ‘relative’ which means that, if the reference is replicated or copied from one cell to another, the difference in position between the two cells will be added on to or subtracted from the reference.
Each half of the cell reference may be ‘fixed’ (made ‘absolute’) by preceding the column letter or the row number by a dollar sign (‘$’), as in:
$A$1
AC$27
External references (i.e. references to cells in other documents) can be fixed in the same way, as in:
[fred]$A$1
The fixed part of a reference is not altered when copied with the Copy Block, Replicate Down, Replicate Right or Replicate commands.
When a cell to which a cell reference refers is deleted, the cell reference is marked with a percent sign (‘%&rsquo); to indicate that the reference is no longer valid.
Cells containing such formulae will display the Bad cell
error message.
Strings are entered between double quotes. Strings may contain spaces but not control characters.
"string with three spaces"
To include double quotes in a string use a pair of double quotes.
"He said,""Hello!"""
Note that a cell reference to a text cell returns a string.