Microsoft Office Tutorials and References

In Depth Information

**Using Operators in Formulas**

Sample formulas that use operators

These examples of formulas use various operators:

h
The following formula joins
(concatenates)
the two literal text strings (each enclosed in

quotes) to produce a new text string:
Part-23A:

=”Part-”&”23A”

h
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. Many Excel

functions will ignore this “number” because they are designed to ignore text.

h
The following formula uses the exponentiation (^) operator to raise 6 to the third power,

to produce a result of 216:

=6^3

h
A more useful form of the preceding formula uses a cell reference instead of the literal

value. Note this example that raises the value in cell A1 to the third power:

=A1^3

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

=216^(1/3)

h
The next formula returns TRUE if the value in cell A1 is less than the value in cell A2.

Otherwise, it returns FALSE:

=A1<A2

Logical comparison operators also work with text. If A1 contains Alpha and A2 contains

Gamma, the formula returns TRUE because Alpha comes before Gamma in alphabetical

order.