Documentation/Calc Functions/IFNA

    From The Document Foundation Wiki
    Other languages:


    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. While 1/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

    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:

    Data for 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:

    AND

    FALSE

    IF

    IFERROR

    IFS

    ISNA

    NOT

    OR

    SWITCH

    TRUE

    XOR

    ODF standard:

    Section 6.15.6, part 2

    Related (or similar) Excel functions:

    IFNA since v.2013.