Excel IF Function

Microsoft Excel IF Function

Basic Description

The Excel IF function tests a user-defined condition and returns one result if the condition is true, and another result if the condition is 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 user-defined condition that is to be tested and evaluated as either TRUE or FALSE
value_if_true	-	The result that is to be returned from the function if the supplied logical_test evaluates to TRUE
value_if_false	-	The result that is to be returned from the function if the supplied logical_test evaluates to FALSE

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 is replaced with another call to the If function (see Example 3 below).

Excel 2003 allows up to 7 levels of nested If functions, but Excel 2007 and Excel 2010 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 Excel 2010 :

=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 other Excel functions that can 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.

Excel If Function Examples

If Function Example 1

The following example shows the Excel If function applied to two sets of numbers. In this example, the logical_test checks whether the corresponding value in column B is equal to 0, and the function returns :

• The text string "div by zero" if the value in column B is equal to 0

or

• The value in column A divided by the value in column B if the value in column B is not equal to zero

	A	B	C	D
1	5	4	=IF( B1=0, "div by zero", A1/B1 )	- returns the value 1.25
2	5	0	=IF( B2=0, "div by zero", A2/B2 )	- returns the text string "div by zero"

If Function Example 2

The logical_test within the Excel If function can be any type of expression that returns a TRUE or FALSE result. The following example shows some more examples of the function, using different types of logical_test.

	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, A3 )	- returns the value 1
4	test	=IF( LEN( A4 )<>0, 1, 0 )	- returns the value 1

If Function Example 3

The following example shows nesting of the Excel If function. The outer function has the same logical_test as in Example 1 above. However, in the example, the value_if_true argument is a further If function. Therefore:

• If the value in column B is equal to 0, a further call to 'If' is made, to test 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"