Reference Property Argument
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).AggregateProperty
Argument | 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.