Microsoft Office Tutorials and References

In Depth Information

<=

Logical comparison (less than or equal to)

<>

Logical comparison (not equal to)

*Percent isn't really an operator, but it functions similarly to one in Excel. Entering a percent sign after a number divides the number

by 100. If the value is not part of a formula, Excel also formats the cell as percent.

Reference operators

Excel supports another class of operators known as
reference operators
; see Table 2-2. Reference operators

work with cell references.

Table 2-2: Reference Operators

Symbol

Operator

: (colon)

Range. Produces one reference to all the cells between two references.

, (comma)

Union. Combines multiple cell or range references into one reference.

(single space) Intersection. Produces one reference to cells common to two references.

ample formulas that use operators

These examples of formulas use various operators:

• The following formula joins
(concatenates)
the two literal text strings (each enclosed in quotes) to produce a

new text string:
Part-23A:

=”Part-”&”23A”

• The next formula concatenates the contents of cell A1 with cell A2:

=A1&A2

Usually, concatenation is used with text, but concatenation works with values as well. For example, if cell A1

contains 123, and cell A2 contains 456, the preceding formula would return the value 123456. Note that,

technically, the result is a text string. However, if you use this string in a mathematical formula, Excel treats

it as a number. Some Excel functions will ignore this “number” because they are designed to ignore text.

• The following formula uses the exponentiation (^) operator to raise 6 to the third power, to produce a result

of 216:

=6^3

• A more useful form of the preceding formula uses a cell reference instead of the literal value. Note this ex-

ample that raises the value in cell A1 to the third power:

=A1^3

• This formula returns the cube root of 216 (which is 6):

=216^(1/3)