This article describes the formula syntax and usage of the IF function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.
The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(A1>10,"Over 10","10 or less") returns "Over 10" if A1 is greater than 10, and "10 or less" if A1 is less than or equal to 10.
IF(logical_test, [value_if_true], [value_if_false])
The IF function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
- logical_test Required. Any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.
- value_if_true Optional. The value that you want to be returned if the logical_test argument evaluates to TRUE. For example, if the value of this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, the IF function returns the text "Within budget." If logical_test evaluates to TRUE and the value_if_true argument is omitted (that is, there is only a comma following the logical_test argument), the IF function returns 0 (zero). To display the word TRUE, use the logical value TRUE for the value_if_true argument.
- value_if_false Optional. The value that you want to be returned if the logical_test argument evaluates to FALSE. For example, if the value of this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, the IF function returns the text "Over budget." If logical_test evaluates to FALSE and the value_if_false argument is omitted, (that is, there is no comma following the value_if_true argument), the IF function returns the logical value FALSE. If logical_test evaluates to FALSE and the value of the value_if_false argument is blank (that is, there is only a comma following the value_if_true argument), the IF function returns the value 0 (zero).
Use the embedded workbook shown here to work with examples of this function. You can inspect and change existing formulas, enter your own formulas, and read further information about how the function works.
These examples use the IF function to test the values in a cell and either return a text string or perform a math operation and then return a value based on the result.
These examples use the IF function to test the values in a cell and return a text string based on the result.
These examples "nest" the IF function inside another IF function to return results based on a cell. If the result of the first IF function is True (>89), the first value specified ("A") is returned. If the result is False, the second IF function text to see if the value is >79 and, if so, returns "B," and additional IF functions test for a "C," "D," and "F" grade.
These examples show an alternative to "nesting" IF functions – the LOOKUP function – to return letter grades based on a cell value.
To work in-depth with this workbook, you can download it to your computer and open it in Excel. For more information, see the article Download an embedded workbook from SkyDrive and open it on your computer.