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
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.
Search JabSto ::

Custom Search