Websheet Formulae

# Constants

Websheet's cells can contain simple numerical or text values that are interpreted according to the format type of the cell. These are constants in the sense that they need no further calculation. Constants may also appear within formulae where they supply fixed values as part of a calculation.

Number types, implied by values such as 7, 1.3, -4 or 6.02e+23 (6.02×1023), have a numerical value and can be operated on by arithmetical operators. Text types are implied by non-numeric characters entered into cells or by being "quoted" in a cell formula. Quoted text is enclosed symmetrically in leading and trailing quotes: either ' or ", thus clearly distinguishing all the text that is to be treated as the text value from any surrounding space or other part of the formula. Text values can be operated on by the concatenation operator.

Constants are entered into cells by clicking on a cell to open its value field or by starting to type while the cell is highlighted. The value entered is validated according to its format type. The value must not begin with an equals sign (=), because that denotes the start of a formula rather than a constant.

The appearance of a cell's value is determined by its format. The internal form of a value may be different to that displayed. For example, dates are stored internally as the number of milliseconds since midnight on January 1, 1970.

# Operators

Operators are applied to values, either constants or the values of referenced cells, to calculate new values. Websheet's formulae can include the following operators:

Operator Description
+ Addition of two numbers. E.g. 2 + 3 = 5.
Subtraction of the second number from the first. E.g. 7 – 2 = 5.
* Multiplication (×) of two numbers. 2 * 3 = 6.
/ Division (÷) of the first number by the second. E.g. 6 / 2 = 3.
% Remainder after dividing the first number by the second. E.g. 7 % 3 = 1, because 7 ÷ 3 is 2 with remainder 1.
^ Raising the first number to the power of the second. E.g. 2 ^ 3 = 8, because 23 is 2 × 2 × 2 = 8.
. Concatenation of two text values. E.g. "ABC"."xyz" gives "ABCxyz".

Operators have the usual order of precedence from mathematics, e.g. multiplication is higher priority and carried out before addition. Sometimes this order is not what is required. You can override the natural order of calculation by enclosing expressions involving the operators in parentheses (brackets: ( _ ).) For example: 2 * 3 + 4 = 10, because two times three is six and six plus four is ten, but, 2 * ( 3 + 4 ) = 14, because three plus four is seven and seven times two is fourteen.

# Functions

Functions are somewhat similar to operators in that they are applied to values to produce new values. The available functions are listed here:

Function Name Description
arccos Generates the arc cosine of a value in radians.
arcsin Generates the arc sine of a value in radians.
arctan Generates the arc tangent of a value in radians.
ceil Ceiling - the smallest integer not less than the value specified.
cosine Generates the cosine of a value in radians.
count Counts the number of cells in a specified span of cells.
deg Converts a value in radians into one in degrees.
floor Floor - the largest integer not greater than the value specified.
int Converts a floating point value to integer.
max Produces the maximum value within a specified span of cells.
min Produces the minimum value from a specified span of cells.
round Rounds to the nearest integer.
sin Generates the sine of a value in radians.
sum Calculates the sum of all the values in a specified span of cells.
tan Generates the tangent of a value in radians.

The value or values that a function is to be applied to are enclosed in parentheses (brackets: ( _ )) following the name of the function. E.g. `max(A2:B3)` evaluates the largest number in the span of cells A2:A3.

# Cell Formulae

A cell formula differs from a constant value in that it is calculated. While the calculation could be as simple as a single constant, the real power of Websheet comes from being able to apply operators and functions to both constants in the formula itself and the values of other cells by reference. Those values in turn can be calculated by further formulae and so on and so forth.

A formula is entered in a similar way to a constant, except that formulae always begin with an equals sign (=). The formula then consists of constants and/or references to cells whose values should be used to calculate a value for the current cell by applying the operators and functions above.

## Example 1

Formula for cell `A1`      `= 1 + 2 + 3`

This evaluates to the value 6.

## Example 2

Formula for cell `A4`      `= A1 * (A2 - A3)`
where cell `A1` contains `7`, `A2` contains `8` and `A3` contains `9`.

This evaluates to the value –7.

If the formula does not conform to a valid expression Websheet will replace the cell's value with the warning "`!Error`". If it contains a text constant where a numeric one is expected or refers to a cell that contains a text value where a numeric one would be expected, Websheet will replace the cell's value with the warning "`!Type`". If a formula refers to a non-existent cell (for example, in a column beyond the rightmost column in the sheet,) Websheet replaces the value with the warning "`!Ref`".

# Order Of Evaluation

When you change a cell's value or formula, Websheet first checks to see if it now contains a formula that refers to any other cell. If it does, then the referenced cell's value is obtained (possibly by re-evaluating its formula) in order to calculate this cell's value. As a result of calculation, this cell's value may have changed, which may affect the value of any cell whose formula references it. Websheet updates any such dependent cell automatically.

# Cyclic References

Cell formulae may not contain circular references, directly or indirectly, because then Websheet wouldn't know when to stop the calculation and could keep going forever. An example of a direct circular reference is a formula for cell `A1` that refers to the value of cell `B2` and a formula for cell `B2` that refers to the value of cell `A1`. Another example would be a cell that refers to itself! An indirect cycle occurs when there are more than two cells involved. For example, cell `A1` refers to cell `B2` that in turn refers to cell `C3`, which finally refers back to cell `A1`. When Websheet detects such a cycle it replaces the value of the affected cell(s) with the warning "`!Cyclic`" .