The syntax for a numeric argument is:
(numeral1, numeral2,...numeraln)
where numerals 1 through n are any numbers including decimals and negative values. For example, the expression Average(10,20,30) returns the value 20.
The syntax for a row, column, or cell argument is:
FunctionName(GridName.GridElement[segment(range)].Property)
Table 22. Argument Components
| GridName | For example, Difference (grid1.row[5], grid2.row[5]) returns the difference of two rows on grid1 and grid2. If GridName is not specified, the default is the current grid where the formula is entered. |
(Optional) One of the following keywords: row, col, column, or cell. For example, Max(row[1], row[2], row[3]) returns the maximum value of three rows. (The keyword row and column or col is optional.) You must specify row and column segment IDs. For example, cell[2, A] refers to the cell that is the intersection between row 2 and column A. The keyword cell is optional. For example, [5,B] refers to the cell that is the intersection between row 5 and column B. Cell references can use [row, col] syntax or [col, row] syntax. If GridElement is specified, letters represent columns and numbers represent rows; for example, Max ([1,A], [2,A], [3,A]) | |
(Required) Row, column, or cell reference number of a grid. For an expanded row or column, you must specify the segment. For example, row[2] addresses row segment 2. Segments are enclosed in square brackets [ ]. | |
(Optional) Rows, columns, or cells that are expanded from the specified segment. If range is specified, the formula is calculated using only the specified range. For example, row[2(3:5)] uses only the 3rd through 5th rows of expanded segment 2. | |
(Optional) One of the following keywords: average, averageA, count, countA, max, min, product, or sum. Property is used to aggregate the expanded rows, columns, or cells specified. Do not specify property when a reference is an argument. By not specifying property, the function calculates the reference in the most appropriate way. For example, the following expression returns the average of the cells within rows 1 and 2: Whereas the following example first calculates the average of row[1], then the average of row[2], adds these two results, then divides by 2: Average(row[1].average, row[2].average) The default property for a row, column, or cell reference that is not used as a function argument is the sum property. For example, the following expression’s default property is sum: row[2]. See Aggregate Property Arguments and Reference Property Arguments.. |
Because segment is the only required part of a reference, the following references are the same:
AverageA and CountA include #missing and #error cells in the calculation. For example, if row 1 is a segment row that expands to Qtr1 = 100, Qtr2 = 200, Qtr3 = #missing, and Qtr4 = 400, the following function returns the value four (4):
row[1].CountA
All other functions exclude #missing data or #error cells. For example, the previous example of row 1 that expands to Qtr1 = 100, Qtr2 = 200, Qtr3 = #missing, and Qtr4 = 400, returns three in this example:
row[1].Count
An aggregate row, column, or cell contains multiple rows, columns, or cells, respectively.The aggregate property argument is the last argument in the following mathematical function syntax:
FunctionName(GridName.Gridelement[segment(range)].property)You apply aggregate properties to a row, column, or cell reference. Aggregate properties include:
When used as a mathematical function argument, the default for property is the same as the function. In the following example the default property is Average:
Average(row[2])
When not used as a mathematical function argument, the default for property is sum. In the following example the default property is the sum of an aggregate row:
row[2]
A reference property argument specifies how to treat formula reference results and is used in conjunction with the other properties.
There is one reference property argument: IfNonNumber/IFFN.
IfNonNumber specifies a replacement for #Missing and #Error values with a specific numeric value.
The syntax is:
AXIS[segment(range)].IfNonNumber(arg).AggregateProperty
Table 23. Argument Components
| Argument | Description |
|---|---|
AXIS | (Optional) A row, column, or cell keyword. |
Segment(range) | Any axis reference, such as a row number or column letter. |
IfNonNumber | How to treat missing or error data within the AxisRef. |
(arg) | What number to use if missing or error data is encountered within the AxisRef. |
AggregateProperty | (Optional) The aggregate function used for aggregate segments. See Aggregate Property Arguments. |
For example:
If cell[1,A] = 3 and cell[1,B] = #Missing,
The following expression returns #Error:
cell[1,A] / cell[1,B]
The following expression replaces cell[1,B] with 1 and returns 3:
cell[1,A] / cell[1,B].ifNonnumber(1)
Note: | If you use suppression for #Missing or #Error in a grid, and the grid contains a formula row or column that uses the IfNonNumber property, #Missing and #Error remain suppressed. |