IF
The IF function returns one of two values, depending on whether a specified expression evaluates to a boolean value of TRUE or FALSE.
IF(if-expression, if-true, if-false)
if-expression: A logical expression. if-expression can contain anything as long as the expression can be evaluated as a boolean value. If the expression evaluates to a number, 0 is considered to be FALSE, and any other number is considered to be TRUE.
if-true: The value returned if if-expression is TRUE. if-true can contain any value. If if-true is omitted (there’s a comma, but no value) and if-expression evaluates to TRUE, IF will return 0.
if-false: An optional argument specifying the value returned if if-expression is FALSE. if-false can contain any value. If if-false is omitted (there’s a comma, but no value) and if-expression evaluates to FALSE, IF will return 0. If if-false is entirely omitted (there’s no comma after if-true) and if-expression evaluates to FALSE, IF will return FALSE.
Notes
Either or both if-true and if-false can contain additional IF functions (often called nested IF functions).
Examples |
|---|
=IF(A5>=0, “Nonnegative”, “Negative”) returns the text “Nonnegative” if cell A5 contains a number greater than or equal to zero or a nonnumeric value. If cell A5 contains a value less than 0, the function returns “Negative”. =IF(IFERROR(OR(ISEVEN(B4+B5), ISODD(B4+B5)), FALSE), “All numbers”, “Not all numbers”) returns the text “All numbers” if both cells B4 and B5 contain numbers; otherwise the text “Not all numbers.” This is accomplished by testing to see if the sum of the two cells is either even or odd. If the cell is not a number, the EVEN and ODD functions will return an error and the IFERROR function will return FALSE; otherwise it will return TRUE because either EVEN or ODD is TRUE. So if either B4 or B5 is not a number or boolean value, the IF statement will return the if-false expression, “Not all numbers”; otherwise it will return the if-true expression “All numbers.” |
Examples—Avoiding errors from division by zero |
|---|
Sometimes it is not possible to construct a table in a manner that can avoid division by zero. However, if division by zero occurs, the result is an error value in the cell, which is usually not the desired result. This example shows three methods of preventing this error. Assume that cell D2 and E2 each contain a number. It is possible that E2 contains 0. You wish to divide D2 by E2, but avoid a division by zero error. Each of the following three methods will return 0 if cell E2 is equal to zero; otherwise each returns the result of D2/E2. =IF(E2=0, 0, D2/E2) operates by directly testing cell E2 to see if it is 0. =IFERROR(D2/E2, 0) operates by returning 0 if an error occurs. Division by zero is an error. =IF(ISERROR(D2/E2), 0, D2/E2) operates by doing a logical test to see if D2/E2 is TRUE. |
Example using REGEX |
|---|
Let A1 contain "john@appleseed.com" =IF(A1 = REGEX("([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})"), "Contains e-mail", "No e-mail") returns "Contains e-mail". |