Complex Formulae in Excel

Complex Formulae in Excel

A complex formula has more than one mathematical operator, such as 6+7*9. When there is more than one operator in a formula, the order of operations tells Excel which operation to calculate first.

The Precedence order of operators

Excel calculates formulas based on the following order of operations:

1.      Operations enclosed in parentheses

2.      Exponential calculations (3^2, for example)

3.      Multiplication and division, whichever comes first

4.      Addition and subtraction, whichever comes first

A mnemonic that can help you remember the order is PEMDAS, or Please Excuse My Dear Adorable  Student.

Solution to this expression is as follows:

How to Create a complex formula

Let us show 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.

It is especially important to enter complex formulas with the correct order of operations. Otherwise, Excel will not calculate the results accurately. In our example, if the parentheses are not included, the multiplication is calculated first and the result is incorrect. Parentheses are the best way to define which calculations will be performed first in Excel.

To create a complex formula using the order of operations:

In our example below, we will use cell references along with numerical values to create a complex formula that will calculate the total cost for a catering invoice. The formula will calculate the cost for each menu item and then add those values together.

1.      Select the cell that will contain the formula. In our example, we'll select cell C4.

2.      Enter your formula. In our example, we'll type =B2*C2+B3*C3. This formula will follow the order of operations, first performing the multiplication: 2.29*20 = 45.80 and 3.49*35 = 122.15. It then will add those values together to calculate the total: 45.80+122.15.

3.      Double-check your formula for accuracy, then press Enter on your keyboard. The formula will calculate and display the result. In our example, the result shows that the total cost for the order is $167.95.

You can add parentheses to any equation to make it easier to read. While it won't change the result of the formula in this example, we could enclose the multiplication operations within parentheses to clarify that they will be calculated before the addition.

Excel will not always tell you if your formula contains an error, so it's up to you to check all of your formulas.