Microsoft Excel: Use Parentheses To Control Order Of Calculations

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

Strategy: Understand the default Excel order of calculations. If you do not use parentheses, the order is as follows:

1. Unary minus operation

2. Exponents

3. Multiply and divide left to right

4. Add and subtract left to right

Thus, if you had the formula =5+4*–5T3/6, Excel would 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)*–(5T(1/2)) will give an answer of –20.1246, as shown in Fig. 471.

Additional Details: In math class, you may have been taught that if you need to nest parentheses, start from the inside out using normal parenthesis, then square brackets, and then curly braces. In math class, you might have written:

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

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

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

When I get the formula error message, as shown in Fig. 472, it is almost always because I’ve missed a closing parenthesis somewhere. In theory, as you enter or edit the 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. Fig. 473 was taken during the fleeting moment when the first and eighth parentheses were in bold.

Alternate Strategy: For a very confusing formula with many sets of parentheses, use this technique. Highlight the formula in the formula bar. Use Ctrl+C to copy the formula. Paste it to a blank Notepad or Wordpad window and print. Use different colored highlighters to match up the various sets of parentheses until you find the extra parentheses.

Summary: Based on the default calculations Excel uses, placing parenthesis in a formula will produce the correct results.

Microsoft Excel: Use Parentheses To Control Order Of Calculations

Share to Facebook