Documentation/Calc Functions/IFERROR

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



Function name:

IFERROR

Category:

Logical

Summary:

Evaluates one argument and if no error is found, the value of that argument is returned. If an error is found, then a second argument is evaluated, and its value is returned.

Syntax:

IFERROR(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 error is found.

Alternative Value is an expression, or a reference to a cell containing an expression, that is evaluated and returned only if an error was found during the evaluation of Value.

  • The IFERROR function does not evaluate Otherwise Value if the evaluation of Then Value proceeds without an error. For example, the formula =IFERROR(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 IFERROR function

=IFERROR(X; Y) is semantically equivalent to =IF(ISERROR(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
=IFERROR(2; 5) Here the first argument contains no error and so the function returns 2. 2
=IFERROR(SQRT(-2); 23) Here the first argument contains an invalid argument error (Err:502) and so the function returns the value of the second argument. 23
=IFERROR(ASIN(45); 3/0) Here the first argument contains a #NUM! error and so the function returns the value of the second argument. However, the second argument contains a division by 0 error. #DIV/0!
=IFERROR(C8; C9) where cell C8 contains =1/0 and cell C9 contains the text "Error found". Here the first argument contains a #DIV/0! error and so the function returns the value of the second argument. "Error found"
=IFERROR(C8; C9) where cell C8 contains =13 and cell C9 contains the text "Error found". Here the first argument contains no error and so the function returns its value. 13

Related LibreOffice functions:

AND

FALSE

IF

IFNA

IFS

ISERROR

NOT

OR

SWITCH

TRUE

XOR

ODF standard:

Section 6.15.5, part 2

Equivalent Excel functions:

IFERROR