A simple formula is a mathematical expression with one operator, such as 7+9. A complex formula has more than one mathematical operator, such as 5+2*8. When there is more than one operation in a formula, the order of operations tells Excel which operation to calculate first. In order to use Excel to calculate complex formulas, you will need to understand the order of operations.
The order of operations
Excel calculates formulas based on the following order of operations:
Operations enclosed in parentheses
Exponential calculations (3^2, for example)
Multiplication and division, whichever comes first
Addition and subtraction, whichever comes first
A mnemonic that can help you remember the order is PEMDAS, or Please Excuse My Dear Aunt Sally.
*NOTE: with Multiplication and Division, whichever operation comes first must be executed, also with Addition and Subtraction.
Creating complex formulas
In the example below, we will demonstrate how Excel solves a complex formula using the order of operations. Here, we want to calculate the cost of sales tax for a catering invoice. To do this, we'll write our formula as =(D2+D3)*0.075 in cell D4. This formula will add the prices of our items together and then multiply that value by the 7.5% tax rate (which is written as 0.075) to calculate the cost of sales tax.
Excel follows the order of operations and first adds the values inside the parentheses: (44.85+39.90) = $84.75. It then multiplies that value by the tax rate: $84.75*0.075. The result will show that the sales tax is $6.36.
Credits: Excel 2013
