Logical Functions
Comparison & TRUE/FALSE
Excel gives you the ability to compare numerical values or strings of text with the following operators:
| Operator | Description |
|---|---|
| = | Equal |
| <> | Does not equal |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
Note: Only = and <> can be used with text.
Example:
=b1>b2
These comparisons return TRUE or FALSE.
This returns TRUE. If the values did match, it would have returned FALSE.
You might think that TRUE and FALSE are considered text, but actually they are “Boolean” values. Boolean values are very unique. They can actually be used in math formulas. Excel reads TRUE as 1 and FALSE as 0.
Unless you are an advanced Excel user or have a programming background, you will probably not make use of this property of TRUE/FALSE, but we've included it for completeness.
You can also compare strings of text. When comparing strings of text, the “Case” (upper or lower) does not matter. “String” will equal “string”.This might seem obvious, but other Text Functions are case dependent.
Logical Functions
Logical Functions use the TRUE/FALSE (Boolean) logic to evaluate criteria and allow you to do one thing if TRUE or another if FALSE.
IF Function
The most common logical function is the IF Function. The IF function tests if a condition is TRUE or FALSE, does one thing if TRUE, or another if FALSE.
=if(condition to test, do if TRUE, do if FALSE)
Ex:
=if(b1 = “AutomateExcel”, c1+c2,”error”)
Nested IF Functions
You can actually use multiple functions within a single cell formula. When one function is placed inside another function, it is called “nesting”:
=if(b2>b3,if(b2>b4,”b2 is max”,”b4 is max”),”b3 is max”)
Be careful with nested functions! The more complicated your formulas, the harder they are to review, and the greater the chance for errors. If you need to create complicated formulas, think about breaking them into multiple cells.
In the next section we will introduce you to other “helper” functions that are often nested inside an IF Function.
AND, OR, and XOR Functions
The AND, OR, and XOR Functions perform logical tests and return TRUE or FALSE.
| Function | Description |
|---|---|
| AND | Tests if ALL conditions are met |
| OR | Tests if ANY conditions are met |
| XOR | Tests if ONLY ONE condition is met |
The syntax for all of these functions are the same: Each condition should be separated by a comma.
=and(a1>a2,a1>a3,a1>a4)
These functions return either TRUE or FALSE and are often used within IF Functions.
IS and IFERROR Functions
You can perform specific logical tests using the IS Functions:
| Function | Description |
|---|---|
| IsBlank | Checks if a cell is blank |
| IsErr | Checks if a cell is an error, excluding #N/A |
| IsError | Checks if a cell is an error |
| IsEven | Checks if a cell is even |
| IsLogical | Checks if a cell is a logical value |
| IsNA | Checks if a cell is #N/A |
| IsNonText | Checks if a cell is not text |
| IsNumber | Checks if a cell is a number |
| IsOdd | Checks if a cell is odd |
| IsRef | Checks if a cell is a reference |
| IsText | Checks if a cell is text |
IFERROR Function.
The IFERROR Function tests if a calculation results in an error. If not, it returns the original result, if it does result in an error then it returns another value. Example:
IFERROR(VLOOKUP(a1,data,5,false),””)
This example is using a vlookup (we will learn about this function later) to return a value. IF the lookup value can not be found, it returns 'blank' instead of an error.
We recommend using the IFERROR Function to make your spreadsheets look more professional. If you show your manager or a client a spreadsheet filled with errors (even if they are valid), they will be concerned. Instead use the IFERROR to handle valid errors!
There are two particularly common cases when you should use an IFERROR:
- When doing any kind of lookup where a lookup value may (correctly) not be found.
- When dividing numbers where the denominator could equal 0.
ليست هناك تعليقات:
إرسال تعليق