ERLANDSEN DATA CONSULTING Excel & VBA Tips

# Use the logical functions

Most users take advantage of the logical function IF(logical_test,if_true,if_false) to make Excel take a logical decision. If you want to perform more complex comparisions you can add the logical functions AND(), OR() and NOT() when you use the IF()-function. Here are some examples on how to use these functions :

=IF(logical_test,if_true,if_false) : this function performs a simple logical test, you can use one of the following compare methods to create the logical test :

 Symbol : Logical test : = Equal to <> Not equal to > Greater than >= Greater than or equal to < Less than <= Less than or equal to

 Logical functions : Returns : AND() TRUE if all logical tests returns TRUE OR() TRUE if one logical test returns TRUE NOT() TRUE if the logical test returns FALSE ISERR() TRUE if the cell value is an error value different from #N/A ISERROR() TRUE if the cell value is an error value ISNONTEXT() TRUE if the cell value not is a text ISNA() TRUE if the cell value equals the error value #N/A ISLOGICAL() TRUE if the cell value is a logical value ISREF() TRUE if the cell value is a cell reference ISNUMBER() TRUE if the cell value is a number ISTEXT() TRUE if the cell value is a text ISBLANK() TRUE if the cell is empty (blank cell)

=AND(logical1,logical2...) : this function can perform up to 30 logical test and returns TRUE if ALL of the logical test returns TRUE, else it returns FALSE.

=OR(logical1,logical2...) : this function can perform up to 30 logical test and returns TRUE if ONE of the logical test returns TRUE, else it returns FALSE.

=NOT(logical) : this function will reverse the result from another logical function, and is often used to make it easier to understand the logical function.

Examples:
=IF(A1>=10,"The value in A1 is larger than 10","Not larger than 10")
=IF(ISBLANK(A1),"This cell must be filled in !","")
=IF(ISTEXT(A1),"This cell must be filled in with a number!","")
=IF(AND(A1>10,B1>20,C1>30),"All values are greater than","One or more values is less than")
=IF(OR(A1>10;B1>20;C1>30);"One or more values is greater than";"All values are less than")
=IF(NOT(A1>100),"Less than 100","Greater than 100")

Document last updated 2000-02-04 12:45:07      Printerfriendly version