Performing Calculations in Excel - Order of Operations Explained

Author:

In this tutorial I am going to cover how to perform calculations in Excel. Calculations in Excel follow the same basic rules as simple math. The idea of Order of Operations in math applies to Excel in the same way.

The + sign is the addition operator. The addition operator will add the number on the left to the number on the right. So for example I want to add 1, 2 and 4 together: Excel performs calculations from left to right so 1 + 2 will be evaluated first. The 1 will be added to the 2 to make 3. Excel will then take this 3 and evaluate the next part of the equation (3 + 4). This gives you the total of 7 which you can see in cell A1 above. The subtraction operator (-) works in the same way for subtracting numbers from each other.

The next 2 operators are the multiplication (*) and division (/) operators. These work slightly differently to the addition/subtraction operators. Just like in math, multiplication/division is done first before addition/subtraction, regardless of what order the operators are. So if I enter the following equation: The 2 * 4 part of my equation will calculated first. The 2 will be multiplied by 4 to get 8. Then Excel will evaluate 1 + 8. This gives you the answer of 9 which you can see above.

Tip: You can see this happen if you select the cell and go to the Formulas tab and click the Evaluate Formula button and then watch the window as you click the Evaluate button.

Now, say I wanted Excel to do the 1 + 2 part of my equation first, I introduce parenthesis into my equation like so: If part of your equation is in parenthesis (()), everything inside them will be calculated before considering the rest of the equation. So for the example above, (1 + 2) will be evaluated to (3) first. Then Excel will evaluate (3) * 4. This gives you 12 as seen above.

Functions

The Order of Operations also applies to Excel Functions and all of the operators that can be used in formula can be used in a Function. If you are using a Function then each of the Functions arguments will be evaluated in turn, from left to right, before the overall Function is evaluated. Looking at the example below: I have combined the previous 2 examples into a Sum function. First, each of the arguments will be evaluated giving Sum (9, 12). Then the overall Function will be evaluated to give 21. Taking this one step further you can even nest Functions within Functions and they too follow an Order of Operations. Below I have a formula with several different Functions within the 1 Function: As each argument of a Function is evaluated from left to right, The Average(A1:A4)*2 will be evaluated first, then the Max(A1:A4) and then Min(A1:A4). Finally the overall Sum Function will be evaluated to give the final number of 52.5.

Evaluate Formula

If you are ever not sure about the Order of Operations for a particular Formula/Function remember the Evaluate Formula button in the Formula Auditing section of the Formula tab:  Note: Have a go with the Evaluate Formula feature to see whats going on in the accompanying Excel workbook for this tutorial. You will also find basic examples of each of the Arithmetic Operators including examples I didnt cover in the tutorial.

Additional Examples in the Attached Workbook: PEMDAS

Hint: remember PEMDAS

P         Parentheses first

E         Exponents (ie Powers and Square Roots, etc.)

MD     Multiplication and Division (left-to-right)