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 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 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. |
rad | Converts a value in degrees to one in radians. |
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.
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.
Formula for cell A1
= 1 + 2 + 3
This evaluates to the value 6.
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
".
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.
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
" .