Midlands Microsoft Office Provider

Appendix A – Intro to Excel 2016 Data Analysis

Appendix A

Excel formulas support a variety of operators. Operators are symbols that indicate what mathematical operation you want the formula to perform. When multiple operators are used in a formula, Excel uses an operator “hierarchy” or precedence, to determine which operator Excel will evaluate first.

​Understanding Operator Precedence 

Basic rules:

  1. Parenthesis will override any other operator precedence and Excel will evaluate parenthesis first.
  2. If multiple pairs of parenthesis are in a formula, Excel evaluates the inner-most parenthesis first, then the outer ones.
  3. Precedence means the order in which Excel evaluates the operators
  4. In the table below, 1 is considered highest precedence; 5 is considered lowest precedence
  5. Two operators of the same precedence in a formula are evaluated as they appear (left-to-right)
​
Formula Result Can be Wrong Without Parenthesis (subtract B3 from B2 and multiply amount by B4).
If you enter the formula without the parentheses, Excel computes a different answer. Because multiplication has a higher precedence, cell B3 is multiplied by cell B4. Then this result is subtracted from cell B2, which isn’t what was intended. The formula without parentheses looks like this:
=B2 – B3 * B4
Override Precedence Priority with Parenthesis
​You can use parenthesis to override Excel’s built-in order of precedence. Expressions within parenthesis are always evaluated first. For example, the following formula uses parentheses to control the order in which the calculations occur. In this case, cell B3 is subtracted from cell B2, and the result is multiplied by cell B4.
= (B2 – B3) * B4
Equal Precedence Formulas – Evaluate equal precedence operators as you see them, left-to-right.
=B2 – B3 + B4
=B1 * B2 / B3
Evaluating Multiple Parenthesis Pairs     

  • Evaluate the inner parenthesis first. With multiple parenthesis, do inner parenthesis first, then the middle parenthesis, then the outer parenthesis.
  • Performing the operation (  /  *  +   –  )  inside the parenthesis removes the parenthesis.
Basic rules to remember while doing the practice exercise:

  1. Parenthesis always has highest precedence. With multiple parenthesis pairs, evaluate the inner-most parenthesis first and evaluate outer pairs secondly.
  2. * and / are the same precedence
  3. +  and – are the same precedence
  4. * and / are higher in precedence than + and –
  5. If operators of the same precedence are in a formula, do them in the order that they appear; left-to-right, I.E. = 3 x 2 / 5 would be evaluated as 3 times 2 divided by 5 since that is the left-to-right order.