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: ﬁ rst negation (–), then all percentages (%),

then all exponentiations (^), then all multiplications (*) and divisions (/), and ﬁ 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 ﬁ 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