IF function

Description

IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE.

Syntax

IF(logical_test, [value_if_true], [value_if_false])

Parameters

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 thevalue_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).

Remarks

  1. Excel 2003 allows up to 7 levels of nested If functions, but Excel 2007, Excel 2010 and Excel 2013 allow up to 64 levels of nesting. Alternatively, to test many conditions, consider using the LOOKUPVLOOKUPHLOOKUP, or CHOOSE functions.
  2. If any of the arguments to IF are Arrays, every element of the array is evaluated when the IF statement is carried out.
  3. 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.

Examples

Classic Examples

The example may be easier to understand if you copy it into cell A1 of a blank worksheet. (not include the header and index number)
1
2
3
4
5
6
7
A B C
Data
110 0
Formula Result Description
=IF(A2>100,"Over 100","100 or less") Over 100
=IF(A2>B2,"Yes","No") Yes
=IF(5>1,"Yes","No") Yes
=IF(C2<>0,"Not Zero","Zero") Zero C2 is empty

Advanced Examples

We can use IF function to build new arrays.

1
2
3
4
5
6
7
8
9
A B C D
Score Name
100 Tom
95 Marry
89 Justin
 Result
=IF({1,0},B1:B4,A1:A4) =IF({1,0},B1:B4,A1:A4) Name Score
=IF({1,0},B1:B4,A1:A4) =IF({1,0},B1:B4,A1:A4) Tom 100
=IF({1,0},B1:B4,A1:A4) =IF({1,0},B1:B4,A1:A4) Marry 95
=IF({1,0},B1:B4,A1:A4) =IF({1,0},B1:B4,A1:A4) Justin 89
This is a array formula, copy the formula: =IF({1,0},B1:B4,A1:A4), select A6:B9, active the formula bar, paste it, and press Ctrl+Shift+Enter to end. in the formula bar, this formula will display as: {=IF({1,0},B1:B4,A1:A4)}

Video training

Leave a Reply

Your email address will not be published. Required fields are marked *