Publishers of technology books, eBooks, and videos for creative people

Home > Articles

  • Print
  • + Share This
This chapter is from the book

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.

  • + Share This
  • 🔖 Save To Your Account