Microsoft Office Tutorials and References

In Depth Information

**Entering Formulas**

Arithmetic Operations

Excel provides powerful functions and capabilities that allow you to perform

arithmetic operations easily and efciently. Table 2–2 describes multiplication and

other valid Excel arithmetic operators.

Table 2
–
2 Summary of Arithmetic Operators

Arithmetic

Operator

Example

of Usage

Meaning

Result

–

Negation

–78

Negative 78

%

Percentage

=23%

Multiplies 23 by 0.01

^

Exponentiation

=3 ^ 4

Raises 3 to the fourth power

*

Multiplication

=61.5 * C5

Multiplies the contents of cell C5 by 61.5

/

Division

=H3 / H11

Divides the contents of cell H3 by the contents of cell H11

+

Addition

=11 + 9

Adds 11 and 9

–

Subtraction

=22 – F15

Subtracts the contents of cell F15 from 22

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 (–).

As in algebra, you can use parentheses to override the order of operations.

For example, if Excel follows the order of operations, 8 * 3 + 2 equals 26. If you use

parentheses, however, to change the formula to 8 * (3 + 2), the result is 40, because the

parentheses instruct Excel to add 3 and 2 before multiplying by 8. Table 2–3 illustrates

several examples of valid Excel formulas and explains the order of operations.

Table 2
–
3 Examples of Excel Formulas

Formula

Result

=G15

Assigns the value in cell G15 to the active cell.

=2^4 + 7

Assigns the sum of 16 + 7 (or 23) to the active cell.

=100 + D2 or =D2 +100 or

=(100 + D2)

Assigns 100 plus the contents of cell D2 to the active cell.

=25% * 40

Assigns the product of 0.25 times 40 (or 10) to the active cell.

– (K15 * X45)

Assigns the negative value of the product of the values contained in cells K15

and X45 to the active cell. You do not need to type an equal sign before an

expression that begins with minus signs, which indicates a negation.

=(U8 – B8) * 6

Assigns the difference between the values contained in cells U8 and B8 times 6

to the active cell.

=J7 / A5 + G9 * M6 –

Z2 ^ L7

Completes the following operations, from left to right: exponentiation (Z2 ^ L7),

then division (J7 / A5), then multiplication (G9 * M6), then addition (J7 / A5) +

(G9 * M6), and nally subtraction (J7 / A5 + G9 * M6) – (Z2 ^ L7). If cells

A5 = 6, G9 = 2, J7 = 6, L7 = 4, M6 = 5, and Z2 = 2, then Excel assigns the

active cell the value – 5; that is, 6 / 6 + 2 * 5 – 2 ^ 4 = – 5.