The FIRSTNONZERO function searches through two or more numeric arguments and returns the first value that is not zero.
For example, you can use the FIRSTNONZERO function to avoid complex conditional formulas you would otherwise have to use to determine the first non-zero value in a collection of numbers.
The two formulas below are equivalent:
FIRSTNONZERO(a, b, c)IF a <> 0 THEN a ELSE IF b <> 0 THEN b ELSE IF c <> 0 THEN c ELSE 0
Syntax
FIRSTNONZERO(Value 1, Value 2, [etc.])
Arguments
| Argument | Data type | Description |
| Value | Number | The FIRSTNONZERO function assesses each instance of this argument and returns the first value that is not zero. This argument can be given multiple times. You must provide a minimum of two values for the FIRSTNONZERO function to compare. The FIRSTNONZERO function assesses values in the order they're provided as arguments. |
The FIRSTNONZERO function returns a numeric result.
Examples
In the example below, five line items that contain numeric values display on rows, named a through e. The Time dimension displays on columns.
Two line items contain formulas that demonstrate the FIRSTNONZERO function.
The Alphabetical order line item searches each line item for a non-zero value in alphabetical order, as the arguments are provided to the FIRSTNONZERO function in that order. The opposite is true for the formula in the Reverse alphabetical order line item.
| Jan 22 | Feb 22 | Mar 22 | |
| a | 5 | 0 | 0 |
| b | 3 | 0 | 0 |
| c | 12 | 2 | 7 |
| d | 4 | 2 | 0 |
| e | 56 | 2 | 4 |
Alphabetical order
| 5 | 2 | 7 |
Reverse alphabetical order
| 56 | 2 | 4 |
