Microsoft Office Tutorials and References

In Depth Information

**Entering Formulas**

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

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

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

in the cells reliant on the

formula or determined the

error, reinsert the equal

sign to change the text

back to a formula and edit

the formula as needed.

Table 2 – 3 Examples of Excel Formulas

Formula

Meaning

=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 product of 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 i 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.

To Enter Formulas Using Point Mode

The sketch of the worksheet in Figure 2 – 3 on page EX 70 calls for the federal tax, state tax, net pay, and tax

% for each employee to appear in columns G, H, I, and J, respectively. All four of these values are calculated using

formulas in row 4:

Federal Tax (cell G4) = 0.22 × (Gross Pay – Dependents × 24.32) or =0.22*(F4– C4*24.32)

State Tax (cell H4) = 0.04 × Gross Pay or = 0.04* F4

Net Pay (cell I4) = Gross Pay – (Federal Tax + State Tax) or =F4-(G4+H4)

Tax % (cell J4) = (Federal Tax + State Tax) / Gross Pay or =(G4+H4)/F4

An alternative to entering the formulas in cells G4, H4, I4, and J4 using the keyboard is to enter the formulas

using the mouse and Point mode.
Point mode
allows you to select cells for use in a formula by using the mouse.

The steps on the following pages enter formulas using Point mode.