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:
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.
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:
The following spreadsheet shows some more examples of the Excel If function, using different types of logical_test.
The following example shows nesting of the Excel If function (i.e. using the if function within another if function). In each case:
|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.
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 get an error from the Excel If Function, this is likely to be the #VALUE! error:
|#VALUE!||-||Occurs if the supplied logical_test argument cannot be evaluated as TRUE or FALSE.|