Operators and the Excel 2016 Calculation Sequence

Operators

The formulas shown so far in the book (Excel 2016 in easy steps) have used several operators (+, *, %), but there are many other operators you might use, in a number of categories, including the following:

 

Operators can be applied to constants, cell references, or functions.

 

 

The Intersection of two ranges is a reference to all the cells that are common between the two ranges.

 

 

 

 

 

Calculation Sequence

The order in which a calculation is performed may affect the result. As an example, the calculation 6+4*2 could be interpreted in two different ways. If the addition is performed first, this would give 10*2, which equals 20. However, if the multiplication is performed first, the calculation becomes 6+8, which equals 14.

To avoid any ambiguity in calculations, Excel evaluates formulas by applying the operators in a specific order. This is known as “operator precedence”. The sequence is as follows:

 

Operator precedence is a mathematical concept used by all programming languages and applications, such as spreadsheet programs that include computation.

 

 

When the formula has several operators with the same precedence, multiplication and division for example, Excel evaluates the operators from left to right.

 

 

 

 

These are some example formulas that illustrate the effect of operator precedence on the calculation result:

 

 

 

 

 

 

 

 

You use parentheses to change the order of evaluation, since the expressions within parentheses are evaluated first. If there are parentheses within parentheses, Excel evaluates the expression in the innermost pair of parentheses first, then works outwards.

 

Operator precedence in Excel is not always the same as mathematical precedence. For example, take the expression -3². This is entered as -3^2 which Excel evaluates as (-3)^2 which is +9. However, mathematically you’d expect -(3^2) which is -9. 

 

 

… and it’s as easy as that!

 

Want to know more?

For the complete guide to Excel 2016, all in the trusted In Easy Steps style, click here. Whether you’re upgrading from a previous version or new to the spreadsheet concept, Excel 2016 in easy steps will help you learn the key features constructively so you’ll be a pro in no time!