Basic Statistics and Data Analysis

Lecture notes, MCQS of Statistics

Tag: Creating Excel Formula

Creating Formula in Excel: Operators order of precedence

Creating Formula in Excel: Operators Order of Precedence

Creating customized (user defined) formulas in Microsoft Excel is not too difficult. For creating formulas just combine the references of your data with the correct mathematical operator (such as -, +, /, * and ^).

Microsoft Order of Precedence

The order of mathematical operations determines in which order the mathematical operations are carried out. If more than mathematical operators are used in formula, there is a specific order (sequence) that Microsoft Excel will follow to perform (compute) these mathematical operations. However, to change the order of operations, brackets (parenthesis) are used in the Excel formula. The easy way to remember the order of operations (precedence) is to remember the acronym: BEDMAS (PEDMAS), that i.e.,

The order of operations (precedence) is:

Bracket or Parenthesis
Exponents (^)
Division (/)
Multiplication (*)
Addition (+)
Subtraction (-)

Suppose, following is the screenshot of an Excel sheet. The formula is also shown in formula bar. As an example, addition (+), division (/) and multiplication (*) operators are used.

order of precedenceThe formula in screenshot performs the computation in the following order,

  • E1/F1 will be computed (answer is 1.5),
  • the answer of E1/F1 will be multiplied by value of G1 (answer is 1.5*2 = 3)
  • the answer of E1/F1 * G1 will be added to D1 (answer is 7)

Any operation(s) enclosed in brackets (parenthesis) will be carried out first followed by any exponents. After that, Excel will consider division or multiplication operations to be of equal importance. The operations are performed in the order they occur left to right in the formula. Similar sequence is also performed for addition and subtraction. Both (addition and subtraction) are considered equal in the order of operations. The operator which appears first will be computed first.

For example, see the screenshot order of precedence bracketThe sequence of operation is

  • First bracket will be computed, that is, multiplication will be performed (2 *2 = 4)
  • E1 will be divided by the answer from multiplication of F1 and G1 (3/4 = 0.75)
  • Lastly D1 will be added to the answer 0.75 (4 + 0.75 = 4.75)

Now check the sequence in the following screenshot

order of precedence bracketFor Creating formula in Excel, see the link Creating Excel Formula


Copy Right © 2011-2017 ITFEATURE.COM