Order of Precedence

The Order of Precedence used by Excel to calculate formulas is extremely important to know when building complex formulas.

Of course, Excel will respect all mathematical rules you learned in school, there are only a few more Operators in Excel: the Range Operators, single Space Operator, Union Operator, the Concatenation Operator and the Exponentiation Operator.

The following table is a complete list of the Order of Precedence used by Excel:

 Operator Description Order of Precedence : (colon) (single space) , (comma) Range operator Intersection operator Union operator 1st – Negation operator (as in –1) 2nd % Percent operator 3rd ^ Exponentiation operator 4th * and / Multiplication and division operators 5th + and – Addition and subtraction operators 6th & Connects two strings of text (concatenation) 7th =, <, >, <=, >=, <> Comparison operators 8th

If your formula contains operators with the same Precedence Operator — for example, if a formula contains the GTE operator (Greater Than or Equal To: “>=” ) and the LTE operator (Lower Than or Equal To: “<=” ) , Excel will evaluate the operators from left to right.

Just to give you a small example, =10/2/5 will give you the same result as: =10/(2*5), because in the first example, we have 2 Division Operators, and in this case Excel will evaluate the formula from left to right: =10/2/5=5/5 = 1. In the second version, excel will calculate the parantheses first: =10/(2*5)=10/10=1. Notice that Excel will respect the Order of Precedence inside parantheses too, in this example: =(2*3+20), 2*3 will be calculated first, then 20 will be added to the result.

You must always keep Excel Formulas under your control!

To control the order of calculation, include that term between parantheses, this way you will gain control over excel formulas. When you are using multiple nested sets of parantheses, Excel will calculate the parantheses from inside out.

For example: =10*(20-(3*3) *2) will calculate first the innermost set of parantheses: (3*3). Here are the steps followed by Excel to perform this calculation:

Step 1 :

=10*(20-9 *2) – Excel will calculate first the (3*3) parantheses

Step 2:

=10*(20-18)- the multiplication 9*2 will be processed first;

Step 3:

=10*2 – the term in parantheses is calculated first: 20-18

Result:

20

Be carefull with parantheses though… One of the most common mistakes made when using parantheses in formulas is to open multiple paranthesis but not closing them all.

When you do this, Excel will display an error message, and offers to solve the problem for you. The problem is that usually Excel will place the closing paranthesis at the end of the formula, and this may lead to wrong results. It is you that should decide where the closing paranthesis should be, not Excel.

If you haven’t subscribed yet, please do so, you will be notified each time a new post is published, there are many interesting things to discover.

Have fun

Cheers, Catalin

This site uses Akismet to reduce spam. Learn how your comment data is processed.

The Excel Learning Journey that I propose for you, is structured in such a way as to offer you the most practical way to learn Excel, free.