A function is a special kind of formula, so a function's arguments must follow the rules for entering formulas. The arguments for a function can be:
A function may require its first argument to be a number, the second argument a text string, and the third argument a cell reference. Some arguments can be of more than one type; for example, the SUM function's argument can be a set of numbers, range references, or array constants. Refer to the documentation on the individual function to determine the type of data required for the function you are entering.
Usually you can enter a number in an argument by simply typing the number. Do not use commas to separate thousands, because the comma will be interpreted as separating two arguments.
Negative numbers are entered using the negative sign (-). Do not use parentheses: the function will interpret the value as a different argument.
Fractions can be entered by using a slash for the fraction separator (3/4 is evaluated as .75).
Percentages can be entered using the percent sign (34% is evaluated as .34).
Scientific notation can be used for very large or very small numbers (34E+09 is evaluated as 34,000,000,000).
Exponents can be entered using the caret sign (3^4 is evaluated as three to the fourth power, or 81).
Dates and times may be entered in two ways: as serial numbers, or in one of the conventional formats (e.g., mm/dd/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.
Text strings in an argument list must be enclosed in quotation marks. Without the quotation marks, the function will return the #NAME? error. When entering multiple text arguments, be sure the closing quotation mark comes before the comma that separates two arguments.
When the argument is a cell reference pointing to a cell containing a text string, the text in the cell does not have to be enclosed in quotes.
If a number is encountered when text is expected, the number is converted to text. "The number is "&3 is interpreted as The number is 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 (e.g., 1 + "Text"), the function will return the #VALUE! error.
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.
For most arguments, you can substitute a cell or range reference for the data required by an argument. A reference is a cell's address. It identifies a cell or range of cells by referring to the column letter and row number of the cell(s). For example, A1 refers to the cell at the intersection of column A and row 1.
The reference tells Formula One for Java to use the contents of the referenced cell(s) as the function's argument. For example, if an argument requires a number, you can substitute a reference to a cell that contains a number. The number in the referenced cell is used in the calculation of the function. The referenced cell must contain the appropriate data for the argument that uses it.
You specify a range of cells by placing a colon (:) between two cell references. For example, A1:C3 refers to the range anchored by cells A1 and C3. The range includes all cells in columns A, B, and C of rows 1, 2, and 3.
Some functions may take more than one cell as an argument. For example, when the AVERAGE function has a range reference as its argument, the function averages the data in all the cells in the range.
Most functions ignore any empty cells found in a range referenced in an argument. However, cells that appear empty and are not may affect the results of the function. For example, cells containing empty text or text consisting only of spaces may be treated as text. Cells that are formatted not to display zero values may contain hidden zeroes that will be treated as numeric values. If your function displays unexpected results, check for empty cells and cells that appear empty.
You can enter cell references in arguments in three ways:
There are two types of cell references: relative and absolute.
SUM(B1:B3) is located in cell A1. When you copy the function and paste it down two rows into cell A3, the function will be adjusted down two rows, to SUM(B3:B5).
References that are part absolute and part relative are called mixed references. The following table lists the reference types.
| Reference | Type |
|---|---|
| A1 | Relative reference pointing to cell A1. |
| $A$1 | Absolute reference pointing to cell A1. |
| $A1 | Absolute column reference, relative row reference pointing to cell A1. |
| A$1 | Relative column reference, absolute row reference pointing to cell A1. |
You can copy and paste absolute, relative, and mixed references to create worksheets that are easy to update and that are smaller than worksheets where each formula is created separately.
For example, in the following worksheet, the values in column A need to be multiplied by the percentages in row 1.
To do these calculations, you could enter the function PRODUCT(A2,B1) in cell B2, PRODUCT(A3,B1) in cell B3, PRODUCT(A4,B1) in cell B4, etc. Besides the fact that this would be a lot of typing, this solution would require Formula One for Java to keep nine separate formulas in memory.
A better way to do it would be to enter the function PRODUCT($A2,B$1) in cell B2, and use the worksheet's Edit > Fill command to fill cells B2 through D4 with copies of that function. When the function is copied in this manner, its relative references change, but the absolute references stay the same.
The resulting worksheet calculates all the figures using multiple copies of that one function. Only one function must be kept in memory.
If you change the percentages in row 1 or the figures in column A, the calculations in the worksheet will automatically change because of the absolute references to those cells. This makes the worksheet easy to update.
You can reference cells in other worksheets in the same workbook by placing an exclamation mark between the sheet name and the reference. The sheet name is the name found on the worksheet tab. For example, Data!A1 refers to the top left cell in a sheet called Data.
Sheet names with spaces. If the sheet name contains spaces, you must enclose the name in single quotes: `1994 sales'!B17.
Cells on two worksheets. You can make a reference to cells on two or more different worksheets by placing a colon between the two sheet names. For example, Sheet1:Sheet2!A1 refers to two cells: cell A1 in Sheet1 and cell A1 in Sheet2.
Order of sheet names. References to more than one worksheet must list the worksheets in the order in which they appear in the workbook.
References that point to cells on worksheets in other workbooks are called external references.
An external reference is created by placing the workbook name in brackets, followed by the worksheet name, an exclamation point, and finally a cell or range reference.
External references will work only if both workbooks are open in the workbook designer. If the referenced workbook is not open when you create the external reference, an Invalid Formula Syntax error message will appear.
Developers who want to use external references when they are not using the Workbook Designer control must create a group of workbooks using the setGroup method in the View class. All workbooks that refer to each other must be added to the same group in order for the external references to work.
The following are examples of external references using absolute, relative, and mixed references.
| Reference | Type |
|---|---|
| [Sales]1987!A1 | Relative reference pointing to cell A1 in a worksheet titled 1987 of a workbook titled Sales. |
| [FY91]January!$A$1 | Absolute reference pointing to cell A1 in a worksheet titled January of a workbook titled FY91. |
| [Q1]Sheet1:Sheet2!$A1 | Absolute column reference, relative row reference pointing to cell A1 in the first and second worksheets of a workbook titled Q1. |
| [Store1]Sheet1:Sheet4!A1:F1 | Relative row and column reference pointing to the range A1 to F1 in the first four worksheets of a workbook titled Store1. |
After you enter an external reference, Formula One for Java will change the format of the reference to show the absolute path to the workbook you referenced. For example, say you entered this reference to a workbook named September in the Payroll directory on your C drive:
After you enter that reference, if you return to the cell where the reference was entered, you will note that Formula One for Java has changed it to:
This absolute path is recorded in the worksheet. If you later move the September workbook, the external reference should still work, as long as you open September.vts in the Workbook Designer at the same time as the workbook that references it.
For many arguments, you can substitute an array constant for the data required. An array constant is a list of numbers enclosed in curly brackets { }. The function treats each item in an array constant as an individual bit of data, just as it treats the data in each cell in a range reference argument individually.
You can enter an array constant that functions like a range reference, with cells and columns. Use commas to separate individual cells on the same row; use semicolons to separate rows.
For example, the array constant {2,4,6,8;10,12,14,16;18,20,22,24} is equivalent to this range reference:
You cannot use array constants as arguments in database functions.
