Documentation/Calc Functions/IFNA

From The Document Foundation Wiki
Jump to navigation Jump to search
Other languages:
English • ‎Nederlands



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 Otherwise Value if the evaluation of Then Value proceeds without a #N/A error. For example, the formula =IFNA(23; 1/0) returns 23. While 1/0 might be expected to generate a #DIV0! 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

Note pin.svg

Note:
For convenience, the information in this subsection is repeated on all pages describing 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 Format > Cells 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 OK 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:

Formula Description Returns
=IFNA(2; 5) Here the first argument contains no #N/A error and so the function returns 2. 2
=IFNA(SQRT(-2); 23) Here 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(C8; C9) where cell C8 contains the formula =VLOOKUP("ABC"; A1:E4; 3; FALSE()) and cell C9 contains the value -1. This example assumes that the string "ABC" does not exist within cells A1:E4. Here the first argument contains a #N/A error and so the function returns the value of the second argument. -1
=IFNA(C8; C9) where cell C8 contains the formula =VLOOKUP("ABC"; A1:E4; 3; FALSE()) and cell C9 contains the formula =VLOOKUP("XYZ"; A1:E4; 3; FALSE()). This example assumes that the strings "ABC" and "XYZ" do not exist within cells A1:E4. Here the first argument contains a #N/A error and so the function returns the value of the second argument, which also contains a #N/A error. #N/A

Related LibreOffice functions:

AND

FALSE

IF

IFERROR

IFS

ISNA

NOT

OR

SWITCH

TRUE

XOR

ODF standard:

Section 6.15.6, part 2

Equivalent Excel functions:

IFNA