Microsoft Office Tutorials and References
In Depth Information
EX 92
Excel Chapter 2 Formulas, Functions, Formatting, and Web Queries
Arithmetic Operations
Table 2–2 describes multiplication and other valid Excel arithmetic operators.
Table 2–2 Summary of Arithmetic Operators
Arithmetic
Operator
Meaning
Example of
Usage
Meaning
Negation
–34
Negative 34
%
Percentage
=72%
Multiplies 72 by 0.01
^
Exponentiation
=4 ^ 6
Raises 4 to the sixth power
*
Multiplication
=22.6 * F4
Multiplies the contents of cell F4 by 22.6
/
Division
=C3 / C6
Divides the contents of cell C3 by the contents of cell C6
+
Addition
=7 + 3
Adds 7 and 3
Subtraction
=F12 – 22
Subtracts 22 from the contents of cell F12
Troubling Formulas
If Excel does not accept
a formula, remove the
equal sign from the
left side and complete
the entry as text. Later,
after you have entered
additional data or
determined the error,
reinsert the equal sign to
change the text back to
a formula and edit the
formula as needed.
You can enter the cell references in formulas in uppercase or lowercase, and you can
add spaces before and after arithmetic operators to make the formulas easier to read. The
formula, =d4*e4, is the same as the formulas, =d4 * e4, =D4 * e4, or =D4 * E4.
Order of Operations
When more than one arithmetic operator is involved in a formula, Excel follows
the same basic order of operations that you use in algebra. Moving from left to right in a
formula, the order of operations is as follows: fi rst negation (–), then all percentages (%),
then all exponentiations (^), then all multiplications (*) and divisions (/), and fi nally, all
additions (+) and subtractions (–).
You can use parentheses to override the order of operations. For example, if Excel
follows the order of operations, 5 * 9 + 8 equals 53. If you use parentheses, however, to
change the formula to 5 * (9 + 8), the result is 85, because the parentheses instruct Excel
to add 9 and 8 before multiplying by 5. Table 2–3 illustrates several examples of valid
Excel formulas and explains the order of operations.
Table 2–3 Examples of Excel Formulas
Formula
Meaning
=K12
Assigns the value in cell K12 to the active cell.
=10 + 4^2
Assigns the sum of 10 + 16 (or 26) to the active cell.
=3 * C20 or =C20 * 3 or =(3 * C20)
Assigns three times the contents of cell C20 to the active cell.
=50% * 12
Assigns the product of 0.50 times 12 (or 6) to the active cell.
– (H3 * Q30)
Assigns the negative value of the product of the values contained in cells H3 and Q30 to the active cell.
=12 * (N8 – O8)
Assigns the product of 12 times the difference between the values contained in cells N8 and O8 to the
active cell.
=M9 / Z8 – C3 * Q19 + A3 ^ B3
Completes the following operations, from left to right: exponentiation (A3 ^ B3), then division (M9 / Z8),
then multiplication (C3 * Q19), then subtraction (M9 / Z8) – (C3 * Q19), and fi nally addition (M9 / Z8
– C3 * Q19) + (A3 ^ B3). If cells A3 = 2, B3 = 4, C3 = 6, M9 = 3, Q19 = 4, and Z8 = 3, then Excel assigns
the active cell the value 18; that is, 3 / 3 – 6 * 4 + 2 ^ 4 = -7.
C6161_EXC_02.4c.indd 92
C6161_EXC_02.4c.indd 92
3/6/07 3:28:13 PM
3/6/07 3:28:13 PM
Search JabSto ::




Custom Search