Microsoft Office Tutorials and References
In Depth Information
Creating Formulas
Let’s consider the following formula: =C2+B8*4–
D10, and the values C2=50, B8=3, and D10=14.
Tip
Excel first takes B8*4, which is 12.
You do not have to create long,
complicated formulas to solve common
problems, such as adding up a long column
of numbers. Excel provides a
Next, since addition and subtraction are
both calculated at roughly the same time,
Excel moves left to right to complete its
calculations.
function
(built-in formula) just for that, and it’s
called SUM. You learn about functions
in Chapter 3, “Using Excel Functions.”
Thus, Excel takes C2 and adds the result
of B8*4, which is 50+12, or 62.
Finally, Excel takes this result and subtracts
D10, or 14, resulting in a final total: 48.
Considering the Order
of Operations
When you create compound formulas (formulas
that utilize more than one mathematical
operator), you need to stop and consider the order in
which Excel solves that formula. You see, Excel
doesn’t solve a formula by moving from the
left to the right, calculating as it goes. Nope,
Excel performs the operations in a formula in
a particular order:
If you don’t consider Excel’s order of operations,
you can run into problems with your formulas.
For example, suppose you are trying to find the
average sales amount per salesperson, using this
week’s sales totals. If you use a formula such
as =A2+B2+C2/3, you’ll get the wrong answer
because Excel will start by taking C2/3, and then
add that result to A2 and B2. To tell Excel to add
the sales totals first, use parentheses like this:
=(A2+B2+C2)/3. Excel performs the calculations
in parentheses first, and then it moves on to
Exponential operations and operations
within parentheses
Multiplication and division