Home » Excel-Built-In-Functions » Excel-Logical-Functions » Excel-If-Function

The Excel IF Function

Related Functions:
IFERROR
IFNA

Function Description

The Excel IF function tests a supplied condition and returns one result if the condition evaluates to TRUE, and another result if the condition evaluates to FALSE.

The syntax of the function is:

IF( logical_test, value_if_true, value_if_false )

where the arguments are as follows:

logical_test - The condition that is to be tested and evaluated as either TRUE or FALSE.
value_if_true - The result that is to be returned if the supplied logical_test evaluates to TRUE.
value_if_false - The result that is to be returned if the supplied logical_test evaluates to FALSE.

Note that the If function will still work if the logical_test returns a numeric value. In this case, any non-zero value is treated as TRUE and zero is treated as FALSE.


Excel If Function Examples

If Function Example 1

The following spreadsheet shows two simple uses of the Excel If function. In these examples, the logical_test checks whether the corresponding value in column A is less than zero and returns:

or
 Formulas:
  A B C
1 5 =IF( A1<0, "negative", "positive" )  - returns "positive"
2 -2 =IF( A2<0, "negative", "positive" )  - returns "negative"
 Results:
  A B
1 5 positive
2 -2 negative

If Function Example 2

The following spreadsheet shows some more examples of the Excel If function, using different types of logical_test.

 Formulas:
  A B C
1 5 =IF( A1>=0, A1, -A1 )  - returns the value 5
2 -5 =IF( A2>=0, A2, -A2 )  - returns the value 5
3 0 =IF( ISERROR( 1/A3 ), 1, 1/A3 )  - returns the value 1
4 test =IF( LEN( A4 )<>0, 1, 0 )  - returns the value 1
 Results:
  A B
1 5 5
2 -5 5
3 0 1
4 test 1

If Function Example 3

The following example shows nesting of the Excel If function (i.e. using the if function within another if function). In each case:

  • If the value in column B is equal to 0, a further call to 'If' is made, to test the value in column C. Within this If function call:

    • If the value in column C is equal to 0, the function returns the text string "div by zero";
    • If the value in column C is not equal to zero, the function returns the value in column A divided by the value in column C.
  • If the value in column B is not equal to zero, the function returns the value in column A divided by the value in column B.
  A B C D E
1 5 4 1 =IF( B1=0, IF( C1=0, "div by zero", A1/C1 ), A1/B1 )  - returns the value 1.25
2 5 4 1 =IF( B2=0, IF( C2=0, "div by zero", A2/C2 ), A2/B2 )  - returns the value 1.25
3 5 0 1 =IF( B3=0, IF( C3=0, "div by zero", A3/C3 ), A3/B3 )  - returns the value 5
4 5 0 0 =IF( B4=0, IF( C4=0, "div by zero", A4/C4 ), A4/B4 )  - returns the text string "div by zero"


For further details and examples of the Excel If Function, see the Microsoft Office website.


Nesting the Excel If Function

The If function is frequently 'nested' in Excel. I.e. the value_if_true or the value_if_false argument takes the form of a further call to the If function (see Example 3 above).

Excel 2003 allows up to 7 levels of nested If functions, but more recent versions of Excel allow up to 64 levels of nesting. For Example, the following formula (which has 8 levels of nesting), will result in an error in Excel 2003 but will work correctly in Excel 2007 or later:

=IF(A1=1,"red", IF(A1=2,"blue", IF(A1=3,"green", IF(A1=4,"brown",
IF(A1=5,"purple", IF(A1=6,"orange", IF(A1=7,"yellow",
IF(A1=8,"grey", IF(A1=9,"pink", "black" ) ) ) ) ) ) ) ) )


If you do find yourself using multiple levels of nesting, you should probably consider whether there are other Excel functions that could be used to obtain the same result more succinctly. For example, the above function could be made much simpler by using the Excel Choose function.


IF Function Error

If you get an error from the Excel If Function, this is likely to be the #VALUE! error:

Common Error
#VALUE! - Occurs if the supplied logical_test argument cannot be evaluated as TRUE or FALSE.
Return to the Excel Logical Functions Page
Return to the List of All Built-In Excel Functions