Tips for Excel, Word, PowerPoint and Other Applications
Introduction to Logical Statements and Logical Math
Why It Matters To You
Logical Statements are both underutilized and used inefficiently. Understanding why and how they work can greatly improve the integrity of your analysis and streamline the flow of your worksheets. Today, we'll focus on the former and address the latter another day. As a note, this article is intended to introduce you to some new ways using logical functions. It will not walk you step-by-step through each function, form tool, and calculation.
By logical statements, I mean functions that contain an IF arguments, that return one value when the evaluated test is TRUE and another result when the test comes back FALSE. Most of the time I see IF statements, they are used to check a calculated result against a static criteria. In the case of nested IF statements, the author may be checking up to 6 static criteria. However, there are tons of other ways you can use logical statements.
- Check to see if a cell is blank (ISBLANK)
- Check to see if a cell is returning an error (ISERROR)
- Check to see if a cell contains text or a value (combined with CELL)
- Make the text subject to a dynamic condition, such as the current day of the week
- ... and much much more
Another use I rarely see are calculations based on the return of TRUE or FALSE. Recognizing that TRUE and FALSE are equivalent to 1 and 0 mathematically gives you some very nice means to create inclusion or exclusion in your worksheets, for example, "only total sales from Wednesdays".
Getting Started
Logical statements generate or test for whether an argument (or set of arguments) is TRUE or FALSE, then returns the value of TRUE or FALSE. When testing an argument, if no criterion is specified, Excel defaults to testing whether the value is 0 (FALSE) or not 0 (TRUE). Therefore, the test:
=F5=5
will return TRUE if cell F5 is 5 and FALSE for all other values. In the test
=AND(F5, F6, F7)
Since no criteria are specified, Excel tests the cells F5, F6, and F7 to see if they are 0. If every cell is 0, then the test returns TRUE, and FALSE for any other combination.
So What?
Sometimes you will want to run a calculation only if several criteria are met or want the ability to model whether several companies will participate in a project or not or you're only willing to consider candidates who meet x criteria. Logical statements provide you the ability to do this.
Logical Functions
There are several logical statements available in Excel. Here’s a short recap:
IF. Tests an argument and generates one result if TRUE, another if FALSE.
Example: IF(logical_test,value_if_true,value_if_false)
AND. Tests a series of arguments. If all are TRUE, then the function returns TRUE. If any argument returns FALSE, the entire function returns FALSE.
Example: AND(logical1,logical2, ...)
OR. Tests a series of arguments. If any argument is TRUE, then the function returns TRUE. Only when all arguments are FALSE does the function return FALSE.
Example: OR(logical1,logical2,...)
NOT. Reverses the logic of a statement. Basically, any result that would return TRUE now returns FALSE and visa versa.
Example: NOT(logical)
Other TRUE/FALSE Generating Functions (Not specifically addressed today)
| Function | Returns TRUE if: |
|---|---|
| ISBLANK | Value refers to an empty cell |
| ISERR | Value refers to any error value except #N/A |
| ISERROR | Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!) |
| ISNA | Value refers to the #N/A (value not available) error value |
| ISNONTEXT | Value refers to any item that is not text (Note that this function returns TRUE if value refers to a blank cell) |
| ISNUMBER | Value refers to a number |
| ISREF | Value refers to a reference |
| ISTEXT | Value refers to text |
TRUE/FALSE results can be used in conditional formatting, formulas, and calculations. When used in calculations, TRUE takes a value of 1 and FALSE takes a value of 0. For example:
- TRUE + TRUE = 2
- Any number x FALSE = 0
- Any number x TRUE = the original number.
See the ‘Logical statements’ and ‘Calculation’ tabs in logical_math.xls under the logical statements tab for more examples of how these functions can be used.
Notes
| Last updated | 9/2/07 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | None |
| Related Tips | SUMPRODUCT, COUNTIF, SUMIF |