Property arguments consolidate expanded references to a single value that is then used in the calculation. Use property arguments to perform calculations on an aggregate row, column, or cell. The two types of property arguments are:
Aggregate Property Argument (see Aggregate Property Argument)
Reference Property Argument (see Reference Property Argument)
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 the following aggregate properties to a row, column, or cell reference.
Table 50. Aggregate Properties
Properties | Description |
|---|---|
Average | Returns the average of a row, column, or cell. The calculation excludes #MISSING and #ERROR values. |
AverageA | Returns the average of a row, column, or cell. The calculation includes #MISSING and #ERROR values. |
Count | Returns the number of values in a row, column, or cell. The calculation excludes #MISSING and #ERROR values. |
CountA | Returns the number of values in a row, column, or cell. The calculation treats #MISSING and #ERROR values as zero (0). |
Max | Returns the maximum value of a row, column, or cell |
Min | Returns the minimum value of a row, column, or cell |
Product | Returns the product of rows or columns |
Sum | Returns the sum of a row, column, or cell |
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 of #MISSING and #ERROR values with a specific numeric value. The syntax:
AXIS[segment(range)].IfNonNumber(arg).AggregatePropertyArgument | Description |
|---|---|
AXIS | One of these keywords: row, column, or cell Optional |
Segment(range) | Indicates any valid axis reference, such as a row number, column letter |
IfNonNumber | Indicates how to treat missing or error data within the Axis Ref |
(arg) | Indicates what number to use if missing or error data is encountered within the AxisRef |
AggregateProperty | The aggregate function is used for aggregate segments Optional |
For example:
If cell[1,A] = 3 and
cell[1,B] = #MISSING,
The expression:
cell[1,A] / cell[1,B]
returns #ERROR.
The expression:
cell[1,A] / cell[1,B].ifNonnumber(1)
replaces cell[1,B] with 1 and returns a 3.
Note: | If you use suppression for #MISSING in a grid, and the grid contains a formula row or column that uses the IfNonNumber property, #MISSING remains suppressed. |