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 returns 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 returns 0. If if-false is entirely omitted (there’s no comma after if-true) and if-expression evaluates to FALSE, IF returns FALSE.
Notes
Either or both if-true and if-false can contain additional IF functions (often called nested IF functions).
Use IF to make simple comparisons |
|---|
The table below shows a simple way to track student grades. |
A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
1 | Student | Test Score 1 | Test Score 2 | Final Score | Letter Grade | Final Result |
2 | 1 | 56 | 59 | 59 | F | Fail |
3 | 2 | 75 | 84 | 84 | B | Pass |
4 | 3 | 100 | 93 | 100 | A | Pass |
You can perform a simple IF comparison using this data. The following are some ways to use the IF function: |
Return a text value based on criteria:  In this formula, the following arguments are used:
This formula assigns a "Pass" result if Student 1’s test score is 60 or higher; otherwise, "Fail" is returned. |
Return a numerical value based on criteria:  In this formula, the following arguments are used:
This formula calculates a final score as the higher of two test scores, and can easily be modified for any other student in the table above by changing the cell references to match the correct student’s scores. |
Use a nested IF function to test for more conditions:  The formula above assigns a "Pass" result if at least one of the student’s test scores is 60 or higher.  The formula above assigns a letter grade based on the student’s final score. Note: With nested IF functions, the order of comparisons is important because the formula stops after the first true statement. For example, if you wanted to add a new condition to the letter grade formula "not attempted" if a student has a score of 0, you need to add it as the first condition. Adding it at the end returns "F" even if the score is 0, because it will have already been evaluated as being less than 60. This is also the reason that a separate if-expression isn’t needed for the letter grade "A;" if a student’s score isn’t "B," then it also wasn’t any other letter grade, and must be "A." |
Try it |
|---|
To see the IF function used in a Numbers spreadsheet, create a new spreadsheet with the "Grade Book" template. Remove a name in the Class Overview table and see how that changes the value in the "Final Grade" column. Explore the function used in the "Final Grade" column. |
Use IF with other functions |
|---|
The following formula works with ISBLANK to determine if a cell is blank.  In this formula, the following arguments are used:
For more complex tests, you can also combine IF with AND, OR, and NOT. |
Take it further with IFS |
|---|
The IFS function can be used to evaluate a series of expressions and return a value based on the first condition that is true, replacing multiple nested IF statements.  |
Additional examples |
|---|
=IF(A5>=0, "Nonnegative", "Negative") returns the text "Nonnegative" if cell A5 contains a number greater than or equal to 0 or a nonnumeric value. If cell A5 contains a value less than 0, the function returns "Negative". =IF(A5<>0, "Nonzero", "Zero") returns the text "Nonzero" if cell A5 contains a number that is not 0. If cell A5 contains 0, the function returns "Zero". =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 return an error and the IFERROR function returns FALSE; otherwise it returns TRUE because either EVEN or ODD is TRUE. So if either B4 or B5 is not a number or boolean value, the IF statement returns the if-false expression, "Not all numbers"; otherwise it returns the if-true expression "All numbers." |
Examples—Avoiding errors from division by 0 |
|---|
Sometimes it is not possible to construct a table in a manner that can avoid division by 0. However, if division by 0 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 0 error. Each of the following three methods returns 0 if cell E2 is equal to 0; 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 0 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 "marina@example.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". |