If you plan on writing formulas in your Excel spreadsheet- I am assuming you do want to use Excel for calculations at this stage 🙂 then knowing the order of precedence or that Excel performs the calculations is really useful for understanding Formulas as it can influence the value returned by the formula.

Microsoft Excel includes four different types of calculation operators: arithmetic, comparison, text, and reference.

The operators that Excel will recognise are:

 

 

Arithmetic operator	Meaning	Example
+ (plus sign)	Addition	1+1
– (minus sign)	SubtractionNegation	3–1–1
* (asterisk)	Multiplication	3*4
/ (forward slash)	Division	3/3
% (percent sign)	Percent	10%
^ (caret)	Exponentiation	3^2 (the same as 3*3)

It’s important to understand that when you create a formula with several operators, Excel evaluates and performs the calculation in a specific order. For instance, Excel always performs multiplication before addition. This order is called the order of operator precedence. You can force Excel to override the built-in operator precedence by using parentheses to specify which operation to evaluate first.

Then  order of calculations can be easily remembered by the acromym BEDMAS

Brackets

Exponents

Division

Multiplication

Addition

Subtraction

If you are unsure if your formula is giving the correct answer, always test it using some simple numbers and by knowing the expected result, in this way you can easily verify your formula is working correctly. If it is not working out right there will be an error in your sequence of it or the formula has simply been typed incorrectly.

Here’s a simple example:-

The following formula produces 11 because Excel calculates multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.

=5+2*3

In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21.

=(5+2)*3

See how this changes things?

I use parentheses even if they are are not strictly necessary.  I find doing so helps to clarify what the formula is intended to do. For example, the following formula =B2-(B3*B4) makes it perfectly clear that B3 should be multiplied by B4, and the result subtracted from cell B2.

Without the parentheses, you would need to remember Excel’s order of precedence.

Another Example

So what about a more complex example?.  Let’s look at the example below and walk through it.

=20*(20-(3*3) *2)

Step 1 :

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

Step 2:

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

Step 3:

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

The result being 40.  Easy when you how huh?.

• View all of your Excel formulas at once
• Hide all of your Excel formulas from prying eyes!
• Undertand and debug Excel formula errors
• How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel

 

[amazonjs asin=”1936220776″ locale=”US” tmpl=”Small” title=”Microsoft Excel 2013 Functions & Formulas Quick Reference Card (4-page Cheat Sheet focusing on examples and context for intermediate-to-advanced functions and formulas- Laminated Guide)”]

[amazonjs asin=”1118490444″ locale=”US” tmpl=”Small” title=”Excel 2013 Formulas”]

[amazonjs asin=”1118902688″ locale=”US” tmpl=”Small” title=”101 Ready-to-Use Excel Formulas (Mr. Spreadsheet’s Bookshelf)”]

[amazonjs asin=”1936220776″ locale=”US” tmpl=”Small” title=”Microsoft Excel 2013 Functions & Formulas Quick Reference Card (4-page Cheat Sheet focusing on examples and context for intermediate-to-advanced functions and formulas- Laminated Guide)”]