ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

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

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2024    Ole P. Erlandsen   All rights reserved
E-mail Contact Address