Microsoft Office Tutorials and References

In Depth Information

**Chapter 4: Fixing Formula Boo-Boos**

The placement of the parentheses is critical to making the formula work.

Excel won’t sense a problem if any particular parenthesis is in the wrong

place as long as there is a matching number of open and closed parentheses.

For example, using the cells and values from Figure 4-1, here are some

possibilities of valid formulas that return incorrect answers:

Formula

Result

5626.84

=B3*(B4+B6) * B8 * (1 + B9)

=B3*B4+(B6 * B8) * (1 + B9)

549.13

=(B3*B4+B6 * B8) * (1 + B9)

589.96

=(B3*B4+B6) * (B8 * 1 + B9)

299.15

Correct parentheses placement and a firm understanding of

mathematicaloperator precedence are critical to calculating correct answers. I suggest a

brush up on these basic math concepts if you aren’t sure how to construct

your formulas. See Chapter 18 for more.

There is a great mnemonic for orders of operation:
Please
excuse
my
dear

Aunt
Sally. That is meant to help remember: parentheses, exponents,

multiplication, division, addition, subtraction. By the way, I had to excuse my dear

Aunt Honey for undercooking the stuffing one year at Thanksgiving. Great

meal, and then we all got sick!

What if, during entry, a parenthesis is left out? When you try to complete

the entry, Excel will pop up a warning and a suggestion. In this example, the

first closed parenthesis is purposely left out. Here is the
incorrect
formula:

=(B3*B4+B6*B8*(1+B9).

Figure 4-2 shows how Excel catches the error and offers a solution.

Figure 4-2:

Fixing

mismatched

parentheses.