12 Entering and editing formulae

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.

Formula building blocks

A formula is anything you enter into a number cell. Tick tool button You create and edit it in the formula line, and then enter it into the cell by clicking on the Tick 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.

Using operators in a formula

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.

Arithmetic operators

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.

Relational operators

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")

Comparing strings using wildcards

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.

Boolean (logical) operators

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!

Operator precedence

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.

Using functions in formulae

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 function selector

The easiest way to enter functions is to use the function selector.

  1. Click on the cell in which you want to use the function.
  2. Function selector tool button Click the Function tool button on the toolbar to the left of the formula line. The Function menu will appear.
  3. Choose Quick for a shorter list of commonly used functions or All for the full list of functions. A list of functions will appear. Each function is followed by its parameters which are described to help you enter the right kind of data.
  4. Scroll through the list if necessary. As you move from function to function the status line will give you information on each function. Double-click on the function you want to enter it. You can reach a function more quickly by typing in the first letter of its name.
  5. The function will appear on the formula line followed by a set of brackets and text describing the parameters needed. Further details of the function will be shown as text in the status line.
  6. Enter the parameters required. You will see that as you move from parameter to parameter, the parameter description is selected. You can type over this or click on cells to insert cell references.
  7. Tick tool button When you have finished, click the Tick button to enter your formula. The result will be displayed in the cell, and whenever you select the cell you will see the formula on the formula line.
  8. If for any reason the formula you created does not produce a sensible result, or Fireworkz can not interpret your formula, the word Error will appear in the cell, and the status line will describe the error.

Autosum

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:

  1. Select a range with either a blank row at the end or a blank column at the right. If the blank cells are at the bottom of the range, the numbers in the columns will be added; if the blank cells are at the right, the numbers in the rows will be added.
  2. Autosum tool button Click the Autosum tool button on the toolbar.
  3. A formula will be created in the blank row or column at the end of the range, which adds all the numbers in the column or row and displays the result. If you click on one of the cells, you will see the formula on the formula line.

If you click the Autosum tool button without making a selection first, it simply enters the formula in the formula line, ready for you to supply its parameters.

Building and editing a formula

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.

Building complex formulae

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.

Troubleshooting

There are some common problems to avoid when writing formulae and building up your worksheet:

Punctuation

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:

Order of parameters

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.

Cell types

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 Make number from the Edit – Make menu.

Circular references

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.

Arrays

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.

Entering arrays

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:

  1. Type some numbers into the cells A1 to B3. These cells should be number cells.
  2. Select the cell which is to contain the array.
  3. Click in the formula line.
  4. Enter the range A1:B3 either by typing in the reference or by pointing at cell A1 and then dragging to B3 to select the range.
  5. Tick tool button Click the Tick button (or press Return↵).

Typing arrays directly into cells

It is also possible to type an array directly into a cell as an array constant.

  1. Begin the array with an opening brace (curly bracket).
  2. Enter the numbers on the first row of the array, separated by commas.
  3. If the array has more than one row, enter a semi-colon to indicate the end of the row. For example:
    {1,2,3;11,12,13}
  4. Enter further numbers as required.
  5. Enter a closing brace to indicate the end of the array.
  6. Tick tool button Click the Tick button (or press Return↵) to enter the finished array constant in the cell.

Displaying arrays

If the result of a formula is an array, you only see the top left entry in the array displayed on the worksheet.

Displaying the whole array

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:

  1. Select a cell.
  2. Enter a formula using the SET_VALUE function, for example:
    SET_VALUE(E1:F5, A1)
    This writes the contents of an array held in cell A1 into cells E1 to F5
  3. Tick tool button Click the Tick button (or press Return↵).
  4. The array named in the second parameter will be written into the range of cells defined in the first parameter.

Accessing a sub-array or a single array component

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))

Using arrays

You can use arrays for a number of different purposes.

Matrices and complex numbers

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.

Batch processing

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 as parameters to functions

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.

Names

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:

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: