Comparison Operator

Excel and Google Spreadsheet Six Comparison Operators

Comparison Operators in Excel and Google Spreadsheets
© Ted French

Operators, in general, are symbols used in formulas to specify the type of calculation that is to be carried.

A comparison operator, as the name suggests, carries out a comparison between two values in the formula and the result of that comparison can only ever be either TRUE or FALSE.

Six Comparison Operators

As shown in the image above, there are six comparison operators used in spreadsheet programs such as Excel and Google Spreadsheets.

These operators are used to test for conditions such as:

  • numerical equality, such as 5 = 5,
  • numerical inequalities,
    • such as 4 < > 5 (does not equal)
    • or 5 >= 4 (greater than or equal to).

Use in Cell Formulas

Excel is very flexible in the way that these comparison operators can be used. For example, you can use them to compare two cells, or compare the results of one or more formulas. For example:

  • =A1=A2
  • =A1=(A2*5)
  • =(A1*10)<=(A2/5)

As these examples suggest, you can type these directly into a cell in Excel and have Excel calculate the results of the formula just as it would do with any formula.

With these formulas, Excel will always return either TRUE or FALSE as the result in the cell.

Conditional operators can be used in a formula that compares the values in two cells in a worksheet.

Again, the result for this type of formula will only ever be either TRUE or FALSE.

For example, if cell A1 contains the number 23 and cell A2 contains the number 32, the formula =A2 > A1 would return a result of TRUE.

The formula =A1 > A2, on the other hand, would return a result of FALSE.

Use in Conditional Statements

Comparison operators are also used in conditional statements, such as the IF function logical test argument to determine equality or difference between two values or operands.

The logical test can be a comparison between two cell references such as:

 A3 > B3 

Or the logical test can be a comparison between a cell reference and a fixed amount such as:

 C4 < = 100 

In the case of the IF function, even though the logic test argument only ever evaluates the comparison as being TRUE or FALSE, the IF function does not normally show these results in worksheet cells.

Instead, if the condition being tested is TRUE, the function carries out the action listed in the Value_if_true argument.

If, on the other hand, the condition being tested is FALSE, the action listed in the Value_if_false argument is executed instead.

For example:

 =IF(A1 > 100,"More than one hundred","One hundred or less")

The logic test in this IF function is used to determine whether the value contained in cell A1 is greater than 100.

If this condition is TRUE (the number in A1 is greater than 100), the first text message More than one hundred is displayed in the cell where the formula resides.

If this condition is FALSE (the number in A1 is less than or equal to 100), the second message One hundred or less is displayed in the cell containing the formula.

Use in Macros

Comparison operators are also used in conditional statements in Excel macros, especially in loops, where the result of the comparison decides whether execution should proceed.