You may use functions with numbers, cell references, names, and mathematical operators to create complex formulas.
The following operators let you specify the type of calculation or evaluation to be performed in the formula.
| Operator Type | Operator | Description |
|---|---|---|
| Arithmetic | + | Addition |
| - | Subtraction | |
| / | Division | |
| * | Multiplication | |
| % | Percentage | |
| ^ | Exponentiation | |
| Text | & | Concatenation |
| Comparison | = | Equal to |
| > | Greater than | |
| < | Less than | |
| >= | Greater than or equal to | |
| <= | Less then or equal to | |
| <> | Not equal to | |
| Reference | :, .., . | In function arguments: Decimal.In range references: Range. Produces a reference that includes all the cells between the two references (e.g., A1:A5 includes cells A1, A2, A3, A4, and A5). |
| , | In function arguments: Argument separator. In range references: Union. Produces one reference that includes the two references (e.g., A1:A10,C1:C10). |
When more than one of the operators appears in a formula, Formula One for Java uses a specific order of precedence to calculate the formula. The operators listed first in the following table are evaluated before the operators below them.
| Operator | Description |
|---|---|
|
|
Parentheses |
|
|
Range |
|
|
Union |
|
|
Negation (when used in front of a constant or variable) |
|
|
Percentage |
|
|
Exponentiation |
|
|
Multiplication and Division |
|
|
Addition and Subtraction (when used between two constants or variables) |
|
|
Text concatenation |
|
|
Comparison |
When two or more operators on the same line in the table above appear in a formula, Formula One for Java evaluates them from left to right.
Use parentheses to change the order of evaluation. The following example illustrates how the result of a formula can be altered by adding parentheses to change the order of precedence.
| Formula | Result |
|---|---|
| =1+2*37 | 75 |
| =(1+2)*37 | 111 |
In general, Formula One for Java evaluates formulas the way Microsoft Excel does. The Lotus 1-2-3 spreadsheet program evaluates formulas in a slightly different way. Lotus 1-2-3's rules always interpret the logical value TRUE as 1, FALSE as 0. Also, text in cells referred to by formulas and in function arguments is always evaluated as 0 (zero).
You can use Lotus 1-2-3's evaluation rules instead of Microsoft Excel's on any worksheet. You can even have different worksheets in the same workbook use different evaluation rules.
To use Lotus 1-2-3 evaluation rules:
