Documentation/Calc Functions/IF

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

Other languages:

Calc Functions Home | List of functions | Abbreviations

Function name:

IF

Category:

Logical

Summary:

Determines whether a single condition is met, returning one value if the condition is met and a different value if the condition is not met.

For more powerful formulas, combine IF with other logical functions (such as AND and OR) and nest IF statements inside each other.

Syntax:

IF(Test[; [Then Value][; [Otherwise Value]]])

Returns:

Returns a value that depends on the arguments, as follows:

If the Then Value and Otherwise Value arguments are omitted, then IF returns either TRUE or FALSE to reflect whether the condition given by the Test argument is met.
If the condition given by the Test argument is met and the Then Value argument is omitted, then IF returns TRUE.
If the condition given by the Test argument is met and the Then Value argument is supplied, then IF returns Then Value.
If the condition given by the Test argument is not met and the Otherwise Value argument is omitted, then IF returns FALSE.
If the condition given by the Test argument is not met and the Otherwise Value argument is supplied, then IF returns Otherwise Value.

Arguments:

Test is an expression, or a reference to a cell containing an expression, that can be evaluated to a logical value TRUE or FALSE. If Test is evaluated to a number instead of a logical value, then the value 0 (zero) is treated as FALSE, while any non-zero 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 Test argument.

Then Value is an expression, or a reference to a cell containing an expression, that is evaluated and returned when Test is evaluated to TRUE.

Otherwise Value is an expression, or a reference to a cell containing an expression, that is evaluated and returned when Test is evaluated to FALSE.

If Test cannot be evaluated as a logical value, then IF reports a #VALUE! error.
The IF function evaluates only one of Then Value and Otherwise Value. For example, the formula =IF(1=2; 1/0; SQRT(4)) returns 2, the square root of 4. While 1/0 might be expected to generate a #DIV/0! error, the division is not attempted in this case.

Additional details:

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:

Basic examples

Formula	Description	Returns
=IF(A1>5; 100; "too small")	Return the number 100 if A1 is greater than 5, and the text "too small" otherwise.	100 or "too small"
=IF(1>2; "nonsense")	Return FALSE, because the Otherwise Value argument has been omitted and 1 is not greater than 2.	FALSE
=IF(2>1)	Return TRUE, because both Then Value and Otherwise Value arguments have been omitted and 2 is greater than 1.	TRUE
=IF(D1; D2; D3) where cells D1, D2, and D3 contain the numbers 123, 456, and 789 respectively.	Determine that Test is a non-zero value and hence is treated as TRUE. The value of the Then Value argument is returned.	456

Nested IFs

Suppose cell A1 contains a number that represents an angle Φ expressed in radians and lying in the range 0 ≤ Φ < 2π. We could write a formula that utilizes nested calls to the IF function and determines the quadrant of a circle in which the current value of Φ lies.

The following formula returns a string describing the quadrant in which the value in cell A1 lies.

=IF(AND(A1>=0; A1<PI()/2); 
     "1st quadrant"; 
      IF(AND(A1>=PI()/2; A1<PI()); 
          "2nd quadrant"; 
           IF(AND(A1>=PI(); A1<3*PI()/2); 
               "3rd quadrant"; 
                "4th quadrant")))

If cell A1 contains the number 0.785398163397448 (π/4, 45°), the formula returns the string "1st quadrant".
If cell A1 contains the number 2.35619449019234 (3π/4, 135°), the formula returns the string "2nd quadrant".
If cell A1 contains the number 3.92699081698724 (5π/4, 225°), the formula returns the string "3rd quadrant".
If cell A1 contains the number 5.49778714378214 (7π/4, 315°), the formula returns the string "4th quadrant".

Using IF in an array formula

The results of the recent Math exam are available, and the lecturer has decided to put the results into a Calc spreadsheet in accordance with the following table. He types the students' names in cells A2 to A11 and their marks into cells B2 to B11.

Unfortunately, students who do not achieve the pass mark of 45% (entered in cell B13) must resit (and pass) the exam before continuing their studies. The lecturer decides that when publishing the results, he needs to emphasize which students will need to sit the exam again and chooses to use Calc's IF function in an array formula to do this.

	A	B	C
1	Student	Exam Score (%)	Resit Needed?
2	Aaron	46
3	Betty	35	YES
4	Brian	43	YES
5	Claudia	72
6	Ethan	80
7	Gary	64
8	Jay	41	YES
9	Kaitlyn	60
10	Samantha	65
11	Shane	41	YES
12
13	Pass Mark	45

The cells C2:C11 are populated as follows:

Select the cells C2:C11 using the cursor.
Type the formula =IF(B2:B11>=$B$13; ""; "YES") into the Input line in the Formula bar. Don't press enter when you finish typing the formula.
When typing is complete, press Control + ⇧ Shift + ↵ Enter (⌘ Command + ⇧ Shift + ↵ Enter on macOS) to enter the formula as an array formula.

Calc places an empty string in column C for students who have passed the exam, while the string "YES" appears in column C for those students who scored less than 45% and will need to resit the exam. Note that each cell in the range C2:C11 contains the formula {=IF(B2:B11>=$B$13; ""; "YES")}, where Calc has added the curly brackets to indicate an array formula. You cannot add these brackets yourself.

Related LibreOffice functions:

ODF standard:

Section 6.15.4, part 2

Related (or similar) Excel functions:

IF