• Excel Book Excerpt

Excel Use Parentheses to Control the Order of Calculations

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Use Parentheses to Control the
Order of Calculations

Problem: In what order does Excel perform calculations? For example, is 2+3*4 equal to 20 or 14?

Strategy: In Excel, if you do not use parentheses, the default order of calculations is as follows:

1. Unary minus operation

2. Exponents

3. Multiply and divide, left to right

4. Add and subtract, left to right

Thus, with the formula =5+4*-5^3/6, Excel will do the following:

1. Figure unary minus on -5.

2. Raise -5 to the third power (-5*-5*-5 = -125).

3. Do division and multiplication from left to right (4*-125 is -500. Then -500/6 is -83.3).

4. Add 5 (-83.3 + 5 is -78.3).

The answer will be -78.3.

You can control the order of operations by using parentheses. For example, the formula =(5+4)*-(5^(1/2)) will yield the answer -20.1246.

LE10000146.jpg

Figure 177 Use parentheses to override the order of operations.

Additional Details: In math class, you may remember that nested parentheses use regular parentheses, then square brackets, and then curly braces. In math class, you might have written:

{(5+4)*[-5*(3/6)]}+3

Forget all that. In Excel, you use regular parentheses throughout.

((5+4)*(-5*(3/6)))+3

When you get the formula error message, it is often because you’ve missed a closing parenthesis somewhere.

The best trick is to watch the color of the last parenthesis. If it is black, then you have a balanced number of left and right parentheses. If it is any color, then you are missing a parenthesis.

Gotcha: As you enter or edit a formula, when you type a closing parenthesis, Excel bolds the corresponding opening parenthesis. However, this bolded condition lasts for only a moment and disappears before you can figure out what is going on.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:



Popular Pages

More Info