It’s important to understand that when you create a formula with several operators, Excel evaluates and performs the calculation in a specific order. For instance, Excel always performs multiplication before addition. This order is called the order of operator precedence. You can force Excel to override the built-in operator precedence by using parentheses to specify which operation to evaluate first.
Consider this basic example. The correct answer to (2+3)*4 is 20. However, if you leave off the parentheses, as in 2+3*4, Excel performs the calculation like this: 3*4 = 12 + 2 = 14. Excel’s default order of operator precedence mandates that Excel perform multiplication before addition. Entering 2+3*4 gives you the wrong answer.
Because Excel evaluates and performs all calculations in parentheses first, placing 2+3 inside parentheses ensures the correct answer.
The order of operations for Excel is as follows:
Evaluate items in parentheses.
Evaluate ranges (:).
Evaluate intersections (spaces).
Evaluate unions (,).
Perform negation (-).
Convert percentages (%).
Perform exponentiation (^).
Perform multiplication (*) and division (/), which are of equal precedence.
Perform addition (+) and subtraction (-), which are of equal precedence.
Evaluate text operators (&).
Perform comparisons (=, <>, <=, >=).
Operations that are equal in precedence are performed left to right.
Here is another widely demonstrated example. If you enter 10^2, which represents the exponent 10 to the 2nd power as a formula, Excel returns 100 as the answer. If you enter -10^2, you would expect –100 to be the result. Instead, Excel returns 100 yet again.
The reason is that Excel performs negation before exponentiation, meaning that Excel is converting 10 to –10 before the exponentiation, effectively calculating –10*–10, which indeed equals 100. Using parentheses in the formula -(10^2) ensures that Excel calculates the exponent before negating the answer, giving you –100.
Remembering the order of operations and using parentheses where appropriate will ensure that you avoid miscalculating your data.