Documentation/Calc Functions/IF

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  returns 2, the square root of 4. While   might be expected to generate a #DIV0! error, the division is not attempted in this case.

Nested IFs
Suppose cell A1 contains a number that represents an angle Φ expressed in radians and lying in the range 0 ≤ Φ < 2&pi;. 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 cell A1 contains the number 0.785398163397448 (&pi;/4), the formula returns the string "1st quadrant".
 * If cell A1 contains the number 2.35619449019234 (3&pi;/4), the formula returns the string "2nd quadrant".
 * If cell A1 contains the number 3.92699081698724 (5&pi;/4), the formula returns the string "3rd quadrant".
 * If cell A1 contains the number 5.49778714378214 (7&pi;/4), 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.

The cells C2:C11 are populated as follows:


 * 1) Select the cells C2:C11 using the cursor.
 * 2) Type the formula   into the Input line in the Formula bar. Do NOT press enter when you finish typing the formula.
 * 3) 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, where Calc has added the curly brackets to indicate an array formula. You cannot add these brackets yourself.

Related LibreOffice functions:
AND

FALSE

IFERROR

IFNA

IFS

NOT

OR

SWITCH

TRUE

XOR

ODF standard:
Section 6.15.4, part 2

Equivalent Excel functions:
IF