This chapter describes how to enter formulae in your worksheet, using the functions and operators offered by Fireworkz. It includes sections on:
Each section describes how you can use these items to build formulae which perform the operations you require.
A formula is anything you enter into a number cell.
You create and edit it in the formula line,
and then enter it into the cell by clicking on the tool button on the toolbar
(or pressing Return↵).
A formula can be as simple as just a number, or it can be very complex and built using
operators, functions, cell references, ranges, arrays, and other types of data.
Here are some examples of formulae:
7+6
B8
A7*15
D20*VAT
SUM(B2:B11)
LOOKUP(A2:A11,MAX(A2:A11))
IF(D12>50,"Yes","No")
Some of these formulae perform quite simple operations, using the standard mathematical symbols for addition, multiplication and so on. These symbols, when used in a formula, are called operators, because they represent an operation which is carried out on the data.
Other formulae include functions. These are more complex operations, which perform an operation which is often well described by their name.
Other elements which can appear in formulae are numbers, cell references, external references, arrays, and names.
To use an operator within a formula, you simply type it into the formula line along with the data it is to operate on (remember that this can be actual data or references to cells containing data).
Fireworkz contains three types of operators;
See also the section Operator precedence.
There are five arithmetic operators available:
+ | Add both sides |
- | Subtract the right-hand side from the left-hand side |
* | Multiply both sides |
/ | Divide the left-hand side by the right-hand side |
^ | Raise the left-hand side to the power of the right-hand side |
Example:
Entering the formula
=A1*C1
multiplies the contents of cells A1
and C1
and places the result in the current cell.
The relational operators compare dates, strings and numbers, comparing the left-hand operand with the right-hand operand.
The result is a Boolean value, TRUE
or FALSE
as appropriate.
Note that both operands must be of the same type (e.g. both numbers) to gain a useful result.
< | Less than |
<= | Less than or equal to |
<> | Not equal to |
= | Equal to |
> | Greater than |
>= | Greater than or equal to |
Example:
B8>10
will compare the contents of cell B8
with the number 10.
If B8
contains a number greater than 10, the result will be the Boolean value TRUE
.
If B8
contains a number which is 10 or less, the result will be FALSE
.
If B8
contains text or a date, the result will also be FALSE
.
Relational operators are useful for establishing the condition for the
IF
function,
and in database functions.
Example:
The following function displays the first text message in the cell if the condition is true, and the second if it is not true:
IF(A1<B1, "A1 less than B1", "A1 not less than B1")
When comparing text strings there are three wildcards you can use in the right-hand side of the comparison.
^? | Matches any single character not including space |
^# | Matches any number of characters including spaces |
^^ | Matches the character ^ |
Example:
"Venice"="^#nice"
will return the Boolean value TRUE
.
The Boolean (logical) operators operate on Boolean values.
& AND
If both sides of an AND operator are TRUE
, the Boolean value TRUE
is returned; otherwise FALSE
is returned.
Note that if the left-hand side of an AND operator in an expression is FALSE
, the right-hand side is not evaluated.
!unary NOT
The NOT operator returns the Boolean negation of the following expression.
|OR
If either side of an OR operator is TRUE
, the Boolean value TRUE
is returned; otherwise FALSE
is returned.
Note that if the left-hand side of an OR operator in an expression is TRUE
, the right-hand side is not evaluated.
In Fireworkz, Boolean values and numeric values are freely convertible:
When numeric values are used in operations that require Boolean values,
Fireworkz always maps the number zero to the Boolean value FALSE
and any non-zero number to the Boolean value TRUE
.
When Boolean values are used in operations or calculations that require numeric values,
Fireworkz always maps the Boolean value FALSE
to the number zero
and TRUE
to the number one.
Example:
Boolean operators are often used to construct the condition required by the IF
function.
The following function adds the contents of cells A2
and B2
only if the contents of both cells A1
and B1
match the specified criteria:
IF(A1=6 & B1=4,SUM(A2, B2),0)
This example shows that it may be a good idea to use spaces in your formulae for clarity!
Operators are often used together to build up complex formulae. In order to do this, you need to know which operator is used first in evaluating the expression. Rather than use layers of brackets to describe the order in which the expression should be evaluated, each operator is awarded a precedence rating. Those at the top of the list are evaluated first, working down towards those at the bottom. If a formula contains two operators of equal precedence, evaluation will be carried out from left to right.
1 | - | Unary minus |
( ) | Brackets | |
! | Unary NOT | |
2 | ^ | Raise to the power |
3 | * | Multiply |
/ | Divide | |
4 | + | Plus |
- | Minus | |
5 | = | Equals |
<> | Not equal to | |
< | Less than | |
> | Greater than | |
<= | Not greater than | |
>= | Not less than | |
6 | & | AND |
7 | | | OR |
Examples:
The following formula shows how operator precedence affects an arithmetic formula using numeric constants; in practice the formula would be more likely to contain cell references than constants:
-3*2-2
evaluates as
((-3)*2)-2
The answer is -8.
The following example shows how operator precedence affects relational and Boolean operators:
IF(A1=3 & B1=4,SUM(A1:A10),0)
The formula first evaluates the expression A1=3
;
if it is TRUE
, the formula then evaluates B1=4
.
If this is also TRUE
, the SUM()
operation is carried out.
Spreadsheet functions are commands which tell Fireworkz to perform a particular operation on the data in a formula.
For example, the function SUM()
adds all the numbers enclosed in brackets,
while the function AVG()
works out the arithmetic mean of all the numbers enclosed in brackets.
You have a choice of methods in entering functions when preparing formulae.
The easiest way to enter functions is to use the function selector.
The Autosum button automatically builds a formula which adds up all the figures in a range on a per column or per row basis.
To add figures automatically:
If you click the
tool button without making a selection first, it simply enters the formula in the formula line, ready for you to supply its parameters.Each formula needs to be carefully considered and written to ensure that it can be interpreted by Fireworkz and that it provides the correct answer. You can, for example, write a formula which Fireworkz understands and processes, but produces a result different to that which you intended.
If you are performing an operation which requires more than one function, split the work across several cells. This ultimately gives you more control over your work, and makes it easier to avoid mistakes.
There are some common problems to avoid when writing formulae and building up your worksheet:
Using the wrong punctuation, or missing out significant symbols, can mean that your formula will not work.
The most important things to watch out for are:
TRUE
,
and the third is the action to take if the condition is FALSE
.
It is vital for many functions that parameters are supplied in the correct order. Information about the correct parameters is given in the function when it is pasted from the function selector, and further information appears in the status line.
If you attempt to perform a numerical calculation on text cells, you will not get a result but instead see an error message. This will probably be String not expected. If you have put numbers into text cells you can convert them into number cells either by editing them individually or by selecting them and using
from the menu.
A circular reference is formed when a formula attempts to
operate on the contents of a cell which is itself dependent on the results of the same formula.
For example, if cell B5
contains the formula SUM(B1:B4)
and cell B1
contains the cell reference B5
.
This creates an insoluble problem; the formula cannot calculate because its data is dependent on its result.
If you create a circular reference, you will see the word Error in the cells which refer to each other, and a warning message in the status line. You will then need to go to the cells and see what the problem is, and solve it.
An array is a one- or two-dimensional table of data stored in a single cell.
Arrays are a facility which enable you to write formulae which process a lot of data at once.
The simplest way to create an array is to enter a reference to a range.
To create an array containing the contents of cells A1 to B3, you would do the following:
It is also possible to type an array directly into a cell as an array constant.
{1,2,3;11,12,13}
If the result of a formula is an array, you only see the top left entry in the array displayed on the worksheet.
If you want to expand an array over a block of cells you can use the SET_VALUE function to see the array on the worksheet in full:
SET_VALUE(E1:F5, A1)This writes the contents of an array held in cell A1 into cells E1 to F5
You can use the INDEX function to extract a section of an array or a single component from it.
Examples:
INDEX(A1, 2, 3)
will return the component of the array held in cell A1 in the second column and third row of the array.
INDEX(A1, 1, 2, 2, 3)
will return a sub-array of two columns and three rows starting with the first column and second row.
If you extract a sub-array, you would still need to use SET_VALUE to display it, for example:
SET_VALUE(C3:D5, INDEX(A1, 1, 2, 2, 3))
You can use arrays for a number of different purposes.
Arrays may be used for entering complex numbers and matrices in your worksheet. For example the matrix
5 | 4 | 2 |
3 | 4 | 8 |
would be expressed as an array:
{5,4,2;3,4,8}
or if the values were entered in cells A1 to C2 you could express it as A1:C2.
Note that the cell containing it would just display the top-left-most element:
5
Many matrix and complex number functions return an array as their result.
Arrays can be used to automate data processing and reduce the number of formulae which you have to create. For example, if a worksheet consists of three columns of numbers, and each row needs to be totalled, you could write a SUM formula in each row, or you could use arrays to simplify the process.
If you used arrays you could write the following formula:
A1:A10+B1:B10+C1:C10
This would create an array of ten rows and one column. Each element would be the result of the formula A1+B1+C1, A2+B2+C2, etc. Again you would need to use SET_VALUE to display the results on the worksheet, so your actual formula would look like:
SET_VALUE(D1:D10, A1:A10+B1:B10+C1:C10)
The advantages of this approach are that you save on the memory required to store all the separate SUM functions which you could have used, and all the individual cell references involved. If you inserted a new row between, say, rows 5 and 6, it would automatically be included in the calculation.
The disadvantage is that if you change any element of the data, the whole block has to recalculate rather than just one row. This slows down recalculation.
Arrays can be used as the parameters to a wide range of functions.
Some functions understand arrays and automatically process them. These are the same functions which can process ranges. For example, if cell A1 holds an array, then:
SUM(A1)
adds all the elements in the array stored in cell A1.
If an array is passed as a parameter to a function which does not expect to receive arrays, Fireworkz calls the function for each array element individually and returns an array of the same size as that passed to the function containing those set of results. For example:
SIN(A1:A10)
returns the array {SIN(A1), SIN(A2) … SIN(A10)}. This is referred to as automatic array processing.
You can use a name to refer to a piece of data, a cell reference, or a range. You can use the name in a formula so that the formula is easier to understand.
You can define a name in one of two ways:
SET_NAME
function, e.g.:
SET_NAME("VAT", 0.175)
In your formulae you can now use your name. For example:
A1*VAT
multiplies the contents of A1 by the value of VAT. This is useful because if you need to change the value of VAT any formulae including the name VAT will automatically be updated to use the new value.
Names must conform to the following rules: