What does operator precedence in Excel mean? Knowing this is important because when you write formulas in Excel you need to remember the rule of Operator Precedence. What is Operator Precedence also referred to as Operator Preference? Operator Precedence is when you write a formula with two or more different operators. The result being is that one of the operators takes precedence, or is calculated before, another.
An Operator Precedence in Excel Example
Take a look at the following calculation.
(See Calculated VAT formula for a real life example).
=5+2*10
You might think that the answer to this calculation is 70. Five plus two multiplied by 10. However, because of Operator Preference, the calculation is calculated by two multiplied by ten plus five. That is why the result is 25 when you enter the above formula in Excel.
To calculate the formula correctly you would need to put brackets around the part of the formula that you want to calculate first. In this way the formula should look like:
=(5+2)*10
Excel will follow the Operator Preference order of calculating the section of the formula with the brackets before the multiplication. So, in essence, you five plus two multiplied by ten. As a result you get seventy, the answer that you were expecting.
Using Operator Preference in Excel in a Practical way
So how can you use Operator Preference in a practical way? Take a look at the following formula:
=A2/(1+B2)
So using the above formula A2 would represent the Gross amount. Cell B2 would represent the percentage amount. The purpose of the formula is to calculate how much the net amount would be if we already had the gross number. So the calculation would proceed in the following order: 1 plus B2 then divided by A2. Let’s place some figures in the cells as an example.
=9600/(1+.20)
The result of the above formula would be 8000. How do we arrive at that figure? First one (representing one hundred percent) add twenty percent equals 120%. Then 9600 divided by 120% results in 8000.
Say, for instance, we didn’t use brackets in the above formula. Imagine for a moment the formula looked like the following:
=9600/1+.02
What would the result be? The answer is 9600.2. This is because first 9600 divided by one is 9600. Then adding .20, makes 9600.2. (It should be noted that as we are writing the formulas in Excel .2 represents 20%).
That is why we need to put brackets around the part of the calculation we want to calculated first. That way we can control how the formula is worked out.
To find out more about the order of preference check out this Microsoft Operator Precedence page.
See how to use Operator precedence in our “What’s the Formula for VAT Calculation in Excel?” Lesson.
How to use the Evaluate Formula to help with Operator Precedence?
In Microsoft Excel the “Evaluate Formula” feature is one that can be used to see exactly how a formula is calculated. You can find the Evaluate Formula button on the Formulas tab.
When you click on the Evaluate Formula you are presented with a box with a series of buttons on the bottom. Clicking repeatedly on the Evaluate button with take you step-by-step through the formula.
The Order of Precedence
Although it’s way beyond this operator precedence tutorial, here is a list of all operators with their order of precedence.
| Operator | Operation | Order of Precedence |
: | Range | 1st |
| <space> | Intersection | 2nd |
, | Union | 3rd |
| – | Negation | 4th |
% | Percentage | 5th |
| ^ | Exponentiation | 6th |
* and / | Multiplication and division | 7th |
| + and − | Addition and subtraction | 8th |
& | Concatenation | 9th |
| = < > <= >= <> | Comparison | 10th |
So, in conclusion, you really need to understand Operator Preference if you’re going to make any headway in Excel. Often, I train this with people who want to use less helper cells in Excel. A helper cell is a cell that serves no other purpose other than to make your formulas work. You may even end up hiding the helper cell columns.
Embark on a transformative journey with our 3-hour half-day Excel 365 beginners’ course, where you’ll not only learn the basics but delve into the subtleties of Excel, like mastering operator precedence for accurate formula creation. Our course is designed for those short on time but eager to gain deep insights, featuring practical examples such as calculating VAT and understanding the crucial role of parentheses in controlling operations. With a focus on real-world applications, such as efficiently converting gross figures to net amounts, this course will elevate your Excel skills beyond mere basics. Plus, with a month of post-training support, we ensure you confidently apply your new skills in any professional scenario, making this course an invaluable investment in your Excel proficiency.