- Formula Basics
- Summarizing Data Quickly
- Elements of Formulas
- Entering Formulas
- Determining the Order of Operations
- Understanding Cell References
- Using Cell References
- Understanding Comparison Operators
- Using Comparison Operators
- Understanding Functions
- Using Functions
- Copying and Moving Formulas
- Handling Errors
Understanding Comparison Operators
It’s a common practice to base a formula’s result on whether a certain condition is satisfied. The comparison operators, listed in Table 4.5, compare two values and evaluate to TRUE or FALSE (that is, to a Boolean value). The data type determines how values are compared:
- Numbers compare arithmetically. < means smaller, and > means larger. (To compare floating-point numbers for equality, use the DELTA function.)
- Text strings compare lexicographically. < means precedes, and > means follows. Text comparisons are case-insensitive. (To do a case-sensitive comparison, use the EXACT function.)
- Dates and times compare chronologically. < means earlier, and > means later. Date and times must have the same fields (year, month, day, hour, and so on) to be compared meaningfully.
- Durations compare by length. < means shorter, and > means longer.
- For Boolean values, TRUE > FALSE (and FALSE < TRUE) because TRUE is interpreted as 1 and FALSE is interpreted as 0.
Table 4.5 Comparison Operators
Operator |
Determines Whether |
Example |
Result |
= |
Two values are equal |
ABC = abc |
TRUE |
≠ |
Two values are not equal |
1 ≠ 1 |
FALSE |
< |
The first value is less than the second value |
able < baker |
TRUE |
≤ |
The first value is less than or equal to the second value |
1-Feb-2010 ≤ 1-Jan-2011 |
TRUE |
> |
The first value is greater than the second value |
6 days > 1 week |
FALSE |
≥ |
The first value is greater than or equal to the second value |
0 ≥ - 1 |
TRUE |
It’s usually a bad idea to compare values of different data types. Numbers typically flags such comparisons as errors, but there are a few situations where such comparisons are valid:
- Text strings compare greater than numbers. For example, “text” > 5, “5” > 5, and “” > 0 all return TRUE.
- Boolean values compare unequally to numbers. For example, TRUE = 1 and FALSE = 0 both return FALSE. TRUE ≠ 1 returns TRUE.
Boolean values compare unequally to text strings. TRUE = “text” and FALSE = “FALSE” both return FALSE. TRUE ≠ “TRUE” returns TRUE.