Whenever you are constructing large formulas with several operators it’s important to understand the order in which Excel will perform the calculation. This order is called the operator precedence. You can force Excel to perform the calculation in the order you want by using parentheses to specify what operation to calculate first.
The Order of Operations
Below is a table which shows the order or operations for Excel.
Multiplication and division have the same order precedence. The comparison operators also have the same order precedence. If the formula contains operators with the same order precedence then Excel will evaluate the operators from left to right.
Let’s look at an example of how Excel calculates formulas with and without parenthesis.
The formula 5+5*2 will return 15 because it will calculate 5*2 first as multiplication has a higher order precedence than addition. It will then add 5 to give an answer of 15. However, I want to perform the 5+5 calculation first so I therefore need to put parenthesis around this. The formula will now be (5+5)*2 so the answer will be 20.
Let’s have a look at a more advanced example.
I have a list of numbers in the cell range A1:A5. I want to add these figures and then multiply it by 6-3. Without the parenthesis the formula will be =6-3*SUM(A1:A5). The answer will return -213. I have broken this down to show you how Excel will calculate this formula.
In this formula the range operator (:) has the highest precedence so it calculates the SUM(A1:A5) first which is 73.
The multiplication has the second highest precedence so it calculates 3*73 which is 219.
The minus (-) has the least precedence so therefore does the minus calculation last. The calculation now is =6-219 which results in -213.
This is not the answer I want. I want to calculate the 6-3 first and then multiply by SUM(A1:A5). In order to do that I put parenthesis around 6-3. The correct formula is =(6-3)*SUM(A1:A5). This is how Excel now calculates the formula.
Because there is parentheses around 6-3 Excel will calculate this first. The answer is of course 3.
Excel now calculates SUM(A1:A5) next as the range operator has the next highest precedence. The answer is 73.
Excel now multiplies 3 by 73 and returns 219.
When a formula has multiple parenthesis then Excel will calculate from the inside out. The below steps is the process of how the formula =5*(100-(4*4)/2) is worked out by Excel.
First Excel works out (4*4) which is 16. The formula will be =5*(100-16/2)
Now Excel will calculate 16/2 next as division has a higher precedence than subtraction. The formula will be =5*(100-8)
Now Excel will calculate (100-8) which is 92. It will then multiply this by 5. The formula is =5*92 and the result is 460.
Knowing the order of operator precedence is extremely important if you want to create complex formulas. If they are not written correctly then they will display incorrect results. Using parenthesis in your formulas are therefore critically important.
A common problem with using parenthesis however is when you start to open them you forget to close them all. Excel will display an error message saying it found a mistake and will offer to close the formula with a parenthesis. The problem with this however is that if you click the Yes button it will put the closing parenthesis at the end of the formula which sometimes is not correct and will therefore display an incorrect result. It is important to know where the closing parenthesis should be.
I hope you enjoyed this blog. I would love to hear from you. Please leave your comments below.