When you create a formula that contains several operators, Excel performs the calculation in a specific order. For example, Excel always performs multiplication before addition. This order is called Operator Precedence.
You can use parenthesis () to override the Operator Precedence and specify with operation to perform first.
Microsoft Excel always calculates the portion of the formula in parenthesis () before using the precedence rules below.
Note: The Order column identifies the Operator Precedence. #1 is calculated first, followed by #2. #8, the comparison operators are always calculated last.
E.g.
#5 multiplication and division are always calculated before #6 addition and subtraction.
The Order of Operator Precedence
Order
Operator
Meaning
( )
Overrides the order of mathematical operators
1
: (colon)
(single space)
, (comma)
Reference Operators
2
−
Minus (for negative numbers)
3
%
Percent
4
∧
Exponents
5
*
⁄
Multiplication
Division
6
+
−
Addition
Subtraction
7
&
Joining text (concatenation)
8
=
>
<
>=
<=
<>
Equal to
Greater than
Less than
Greater than or equal to
Less than or equal to
Not equal to
Let’s Try a Few Examples…
As you will notice, depending upon how your formulas are constructed, Excel may provide a different answer than you were expecting.
2 + 3 * 4
In this formula, there are no parenthesis, so Excel calculates the formula based on the order of Operator Precedence:
3 * 4 which equals 12
then 2 + 12
Answer: 14
(2 + 3) * 4
Excel calculates the parenthesis () first:
2 + 3 which equals 5
then 5 * 4
Answer: 20
More complex formulas may also be constructed by using nested parethesis. Excel always calculates the values in the innermost parenthesis () first.
Note: If your formula does not have a matching right parenthesis, Excel will add one for you.
=((2*2)+(4*4)+(5*5))*2
=((4)+(16)+(25))*2
Here Excel calculates the innermost parenthesis () first, from left to right.
(2*2)=4
(4*4)=16
(5*5)=25
=(4+16+25)*2
=45*2
=90
After Excel calculates the innermost parenthesis, your formula looks like this.
Then Excel calculates the next set of parenthesis ().
Then Excel evaluates the rest of the formula based on the order of precedence because there are no more parenthesis.
=(2+3)*5-(4+6)*2
=(5)*5-(10)*2
=25-20
=5
Here Excel calculates the parenthesis () first, from left to right.
Then Excel evaluates the rest of the formula based on the order of precedence, from left to right.
=(2+3)*1.05
Here Excel calculates the parenthesis () first.
Then Excel evaluates the rest of the formula.
=(3-(6*8+2-(7-5)+3)/2)*4
=(3-(6*8+2-(2)+3)/2)*4
=(3-(48+2-(2)+3)/2)*4
=(3-(51)/2)*4
=(3-25.5)*4
=(–22.5)*4
=-90
Here Excel calculates the innermost nested parenthesis () first and subtracts 5 from 7.
Then Excel looks at the next nested outer parenthesis (). Within that set of parenthesis, it uses the order of precedence and multliples 6 by 8 first. Then it adds or substracts the remaining numbers with the parenthesis ().
Then Excel looks at the next outer parenthesis (). Within that set of parenthesis it uses the order of precedence and divides 51 by 2 first. Then it subracts 25.5 from 3.
Then Excel evaluates the rest of the formula.
Please share this tip with other Excel users to ensure that Excel performs the calculations correctly.
~ Let Microsoft Excel do the work for you. ~
0 Comments