Use the logical functions

 2000-02-04    Functions    0    78

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 function 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")