Saturday, November 13, 2004

Using Logic Functions in Excel

The logical function =IF(logical_test,if_true,if_false) can be used in Excel to make 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.

When using the IF function to perform a logical test, you can use one of the following compare methods:
= Equal to
<> Not equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to

Definitions of the logical functions in Excel.
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 tests and returns TRUE if ALL of the logical tests returns TRUE. If one or more logical tests returns FALSE it returns FALSE.

=OR(logical1,logical2...) This function can perform up to 30 logical tests and returns TRUE if at least ONE of the logical test returns TRUE. If none of the logical tests returns TRUE 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 of logical functions in Excel
=IF(A1>=10,"The value in A1 is larger than 10","Not larger than 10")
=IF(ISBLANK(A1),"A1 must be filled in!","OK")
=IF(ISTEXT(A1),"A1 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")


Posted by: Jethro | 7:27 AM |