Documentation/Calc Functions/IFS

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

Other languages:

Calc Functions Home | List of functions | Abbreviations

Function name:

IFS

Category:

Logical

Summary:

From multiple conditions passed as arguments, determines the first condition that is met and returns the result that corresponds to that condition.

Syntax:

IFS(Expression 1; Result 1 [; Expression 2; Result 2][; ... ; [Expression 127; Result 127]])

Returns:

Returns a value whose type depends on the nature of the selected result argument.

Arguments:

Expression 1, Expression 2, ..., Expression 127 are expressions, or references to cells containing expressions, that that can be evaluated to a logical value TRUE or FALSE. If an expression is evaluated to a number instead of a logical value, then the value 0 (zero) is treated as FALSE, while any non-zero (numeric) value is treated as TRUE. Any of Calc's comparative operators = (equal), <> (inequality), > (greater than), >= (greater than or equal to), < (less than), and <= (less than or equal to) may be used to create conditions in the Expression n arguments. Each Expression n argument must have a corresponding Result n argument.

Result 1, Result 2, ..., Result 127 are expressions, or references to cells containing expressions, that give the results to be returned when the corresponding Expression n argument is the first to evaluate to TRUE.

If an Expression n argument cannot be evaluated as a logical value, then IFS reports a #VALUE! error.
If an Expression n argument is evaluated as the logical value TRUE but there is no corresponding Result n argument, then IFS reports a #N/A error.
If no Expression n argument is evaluated to TRUE, then IFS reports a #N/A error.

Additional details:

Details specific to IFS function

The IFS function can often be used as an easier-to-read alternative to creating a formula using multiple nested IF function calls.
Unlike the SWITCH function, IFS does not provide a "built-in" default value to use when all other expressions evaluate to false. However, to provide such a default value you can enter TRUE as the final expression, followed by a value to use as a default result.
The formula =IFS(expression1; result1; expression2; result2; expression3; result3) is executed as described by the following pseudocode:

IF expression1 is TRUE

THEN result1

ELSE

IF expression2 is TRUE

THEN result2

ELSE

IF expression3 is TRUE

THEN result3

ELSE

#N/A

A consequence of this order of execution is that once IFS finds an Expression n argument that is true, it evaluates and returns the corresponding Result n argument, without processing any further expression or result arguments. For example, the formula =IFS(TRUE(); "First result"; 3/0; "Second result") returns the string "First result" – while 3/0 might be expected to generate a #DIV/0! error, the division is not attempted in this case.

General information about Calc's logical functions

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
=IFS(A1>5; 100; A1<=5; "too small")	Return the number 100 if A1 is greater than 5, and the text "too small" otherwise. The formula `=IF(A1>5; 100; TRUE(); "too small")` is equivalent.	100 or "too small"
=IFS(A1=1; "Jan"; A1=2; "Feb"; A1=3; "Mar"; A1=4; "Apr"; A1=5; "May"; A1=6; "Jun"; A1=7; "Jul"; A1=8; "Aug"; A1=9; "Sep"; A1=10; "Oct"; A1=11; "Nov"; A1=12; "Dec") where cell A1 contains the formula =MONTH(TODAY()) and today's date is 2021-11-28.	Extract the month value from today's date as an integer and then convert the number to a string giving a three-character abbreviation of the month.	Nov
=IFS(A1; A2; A3; A4) where cells A1:A4 contain the numbers 0, 123, -1, and 456 respectively.	Evaluate the first argument as false and ignore the second argument; then evaluate the third argument as true and return the fourth argument.	456

Related LibreOffice functions:

ODF standard:

None.

Related (or similar) Excel functions:

IFS