Page 220 - Excel 2007 Bible

16_044039 ch11.qxp 11/21/06 11:04 AM Page 177
Introducing Formulas and Functions
Following are some examples of formulas that use various operators.
What It Does
Formula
Joins (concatenates) the two text strings to produce Part-23A.
=”Part-”&”23A”
Concatenates the contents of cell A1 with cell A2. Concatenation works with
=A1&A2
values as well as text. If cell A1 contains 123 and cell A2 contains 456, this
formula would return the value 123456.
Raises 6 to the third power (216).
=6^3
Returns the cube root of 216 (6).
=216^(1/3)
Returns TRUE if the value in cell A1 is less than the value in cell A2.
=A1<A2
Otherwise, it returns FALSE. Logical-comparison operators also work with
text. If A1 contained Bill and A2 contained Julia, the formula would
return TRUE, because Bill comes before Julia in alphabetical order.
Returns TRUE if the value in cell A1 is less than or equal to the value in cell
=A1<=A2
A2. Otherwise, it returns FALSE.
Returns TRUE if the value in cell A1 isn’t equal to the value in cell A2.
=A1<>A2
Otherwise, it returns FALSE.
Understanding operator precedence in formulas 11
When Excel calculates the value of a formula, it uses certain rules to determine the order in which the vari-
ous parts of the formula are calculated. You need to understand these rules if you want your formulas to
produce the desired results.
Table 11.2 lists the Excel operator precedence. This table shows that exponentiation has the highest prece-
dence (it’s performed first) and logical comparisons have the lowest precedence (they’re performed last).
TABLE 11.2
Operator Precedence in Excel Formulas
Symbol Operator Precedence
^ Exponentiation 1
* Multiplication 2
/ Division 2
+ Addition 3
– Subtraction 3
& Concatenation 4
= Equal to 5
< Less than 5
> Greater than 5
177