Formulas are the basic building blocks for analyzing and calculating worksheet data. A formula is a special entry in a cell that tells Formula One for Java to do a particular calculation or comparison. Cells that contain formulas display or return values that are the results of the calculation. The returned values can be numbers, logical values (TRUE and FALSE), or text.
Formulas consist of strings containing numbers, text, logical values, mathematical operators, worksheet functions, cell references, and names. A formula can contain as many as 1024 characters.
To enter a formula in a cell:
Formula One for Java recognizes cell contents beginning with equal signs as formulas.
Usually you can enter a number in a formula by simply typing the number. Number formats do not apply to numbers within formulas, so the rules for entering numbers in formulas are different than the rules for entering numbers as constant values.
3/4 is evaluated as .75).
34% is evaluated as .34).
34E+09 is evaluated as 34,000,000,000).
3^4 is evaluated as three to the fourth power, or 81).
m/d/yy) enclosed in quotation marks. Dates and times entered in this way are considered text, but Formula One for Java recognizes them as dates and internally converts them to their serial number values. For example, "10/10/94"-"10/1/94" is interpreted as 9.
Formulas can manipulate text just as they manipulate numbers. Text in formulas must be enclosed in quotation marks. Without the quotation marks, the formula will return the #NAME? error.
If a number is encountered when text is expected, the number is converted to text. "Quarter "&3 is interpreted as Quarter 3. If text is encountered when a number is expected, the text is converted to a number (1 + "3" is interpreted as 4). If the text cannot be converted to a valid number ( 1 + "banana"), the formula will return the #VALUE! error.
You can use the < and > characters in formulas to determine if a text string is alphabetized before or after another text string.
You may use the ampersand (&) character to concatenate text strings when a text argument from two different sources is required. For example, in the spreadsheet below, cell A3 uses concatenation to create the text first quarter.
The logical value TRUE converts to 1, while FALSE converts to 0.
If a number is encountered when a logical value is expected, 0 is evaluated as FALSE and all other numbers are evaluated as TRUE. If text is encountered when a logical value is expected, "TRUE" is evaluated as TRUE, "FALSE" is evaluated as FALSE, and all other text returns the #VALUE! error.
The following mathematical 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 | :, .., . | 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). |
| , | 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 |
Functions are a particular kind of formula that let you perform complex calculations with very little work. Formula One for Java contains a set of built-in worksheet functions. Refer to the companion manual, "Formula One for Java Function Reference," for additional information about functions.
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:
