Use the logical functions
2000-02-04 Functions 0 196
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) |
=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")