Documentation/Calc Functions/IFNA
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
IFNA
Category:
Logical
Summary:
Evaluates one argument and if no #N/A error is found, the value of that argument is returned. If a #N/A error is found, then a second argument is evaluated, and its value is returned.
Syntax:
IFNA(Value; Alternative Value)
Returns:
Returns a value whose type depends on the argument selected as the return value.
Arguments:
Value is an expression, or a reference to a cell containing an expression, that is evaluated and returned if no #N/A error is found.
Alternative Value is an expression, or a reference to a cell containing an expression, that is evaluated and returned only if a #N/A error was found during the evaluation of Value.
- The IFNA function does not evaluate Alternative Value if the evaluation of Value proceeds without a #N/A error. For example, the formula
=IFNA(23; 1/0)
returns 23. While1/0
might be expected to generate a #DIV/0! error, the division is never attempted in this case.
Additional details:
Details specific to IFNA function
=IFNA(X; Y)
is semantically equivalent to =IF(ISNA(X); Y; X)
, except that the former case is more efficient because X
is only computed once.
General information about Calc's logical functions
Calc uses the numbers 0 (FALSE) and 1 (TRUE) to represent logical values. For example, enter 1 in a cell that has the default general number format. Then select
▸ on the Menu bar to display the Format Cells dialog, select the Boolean Value option in the Category area of the Numbers tab, and click the button. Notice that the number 1 is now displayed as TRUE. To revert to displaying the number 1, repeat the process but select Number instead of Boolean Value.To enter a logical value into a cell, simply type either TRUE or FALSE (case-insensitive) into the cell. Calc will recognize the logical value, display it in uppercase letters, and change the cell's format to that appropriate to Boolean values.
Calc functions that produce a logical result return a number, either 0 or 1. In the case of a formula that simply calls such a logical function and is in a cell that has the default general number format, the cell is switched to the Boolean value format and the returned value is displayed as FALSE or TRUE. For example =TRUE()
returns the value 1, which is displayed as TRUE; if you then change the format to a numerical format, it is displayed as the number 1.
Calc functions that test for a logical result always evaluate the expression to be tested and check if it is equal to 0. The value 0 is taken as FALSE and any other value is taken as TRUE. For example, =NOT(0)
returns TRUE and =NOT(57.89)
returns FALSE.
In contrast to Calc, Microsoft Excel has a separate type for logical values - they are not numbers but are sometimes converted to numbers. Take care therefore if you need compatibility.
Examples:
A | B | C | D | |
---|---|---|---|---|
1 | DEF | MNO | =VLOOKUP("ABC"; A1:B3; 2; FALSE())
| |
2 | GHI | PQR | -1 | |
3 | JKL | STU | =VLOOKUP("XYZ"; A1:B3; 2; FALSE())
|
Formula | Description | Returns |
---|---|---|
=IFNA(2; 5) | The first argument contains no #N/A error and so the function returns 2. | 2 |
=IFNA(SQRT(−2); 23) | The first argument contains an invalid argument error (Err:502) but no #N/A error and so the function returns Err:502. | Err:502 |
=IFNA(D1; D2) | In the above data table, the formula in D1 results in #N/A, so the IFNA function returns the value of the second argument. | −1 |
=IFNA(D1; D3) | The formula in D1 results in #N/A, so the IFNA function returns the value of the second argument, which happens to result in #N/A too. | #N/A |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
IFNA since v.2013.