This article describes the formula syntax and usage of the IFfunction 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(logicaltest, value_if_true, value_if_false)
The IF function syntax has the following arguments:
logicaltest 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.
valueiftrue Optional. The value that you want to be returned if the logicaltest argument evaluates to TRUE. For example, if the value of this argument is the text string "Within budget" and the logicaltest argument evaluates to TRUE, the IF function returns the text "Within budget." If logicaltest evaluates to TRUE and the valueiftrue argument is omitted (that is, there is only a comma following the logicaltest argument), the IF function returns 0 (zero). To display the word TRUE, use the logical value TRUE for the valueiftrue argument.
valueiffalse Optional. The value that you want to be returned if the logicaltest argument evaluates to FALSE. For example, if the value of this argument is the text string "Over budget" and the logicaltest argument evaluates to FALSE, the IF function returns the text "Over budget." If logicaltest evaluates to FALSE and the valueiffalse argument is omitted, (that is, there is no comma following the valueiftrue argument), the IF function returns the logical value FALSE. If logicaltest evaluates to FALSE and the value of the valueiffalse argument is blank (that is, there is only a comma following the valueiftrue argument), the IF function returns the value 0 (zero).
Up to 64 IF functions can be nested as valueiftrue and valueiffalse arguments to construct more elaborate tests. (See Example 3 for a sample of nested IF functions.) Alternatively, to test many conditions, consider using the LOOKUP, VLOOKUP, HLOOKUP, or CHOOSE functions. (See Example 4 for a sample of the LOOKUP function.)
If any of the arguments to IF are arrays, every element of the array is evaluated when the IF statement is carried out.
Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF or the COUNTIFS worksheet functions. To calculate a sum based on a string of text or a number within a range, use the SUMIF or the SUMIFS worksheet functions.
Want to master Microsoft Excel and take your work-from-home job prospects to the next level? Jump-start your career with our Premium A-to-Z Microsoft Excel Training Bundle from the new Gadget Hacks Shop and get lifetime access to more than 40 hours of Basic to Advanced instruction on functions, formula, tools, and more.
Other worthwhile deals to check out:
- 97% off The Ultimate 2021 White Hat Hacker Certification Bundle
- 98% off The 2021 Accounting Mastery Bootcamp Bundle
- 99% off The 2021 All-in-One Data Scientist Mega Bundle
- 59% off XSplit VCam: Lifetime Subscription (Windows)
- 98% off The 2021 Premium Learn To Code Certification Bundle
- 62% off MindMaster Mind Mapping Software: Perpetual License
- 41% off NetSpot Home Wi-Fi Analyzer: Lifetime Upgrades