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:
=B3*(B4+B6) * B8 * (1 + B9)
=B3*B4+(B6 * B8) * (1 + B9)
=(B3*B4+B6 * B8) * (1 + B9)
=(B3*B4+B6) * (B8 * 1 + B9)
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:
Figure 4-2 shows how Excel catches the error and offers a solution.