Excel 2019 (Part 13): Creating Complex Formulas

You may have experience working with formulas that contain only one operator, such as 7+9. More complex formulas may contain several mathematical operators, such as 5+2*8. When there is more than one operation in a formula, the order of operations tells Excel which calculation to perform first. To write formulas that give you the correct answer, you need to understand the order of operations.

 

Order of operations

Excel calculates formulas based on the following order:

1. The operations are placed in parentheses.
2. Exponential calculations (e.g., 3^2)
3. Multiplication and division, whichever comes first.
4. Addition and subtraction, whichever comes first.

Although this formula may seem complicated, follow the steps to find the correct answer.

 

First, we'll start by performing the calculation inside the parentheses. In this case, it's 6 - 3 = 3 .

As you can see, the formula has become simpler. Next, let's see if there are any exponents. There are 2^2 = 4 .

Next, we will solve any multiplication and division problems, in order from left to right. Since division comes before multiplication, it is calculated first: 3/4 = 0.75 .

 

Now, we'll calculate the remaining multiplication: 0.75 * 4 = 3 .

Next, perform any addition or subtraction calculations, again in order from left to right. Addition comes first: 10 + 3 = 13 .

Finally, perform the remaining subtraction: 13 - 1 = 12 .

 

Now, we have the answer: 12. And this is the exact result you will get if you enter the formula into Excel.

Create complex formulas

In the example below, this article will demonstrate how Excel uses the order of operations to solve a more complex formula. Here, the example is to calculate the sales tax cost for a food and beverage invoice. To do this, the formula to be written would be =(D3+D4+D5)*0.075 in cell D6. This formula will add the prices of the items, then multiply that value by the tax rate of 7.5% (written as 0.075) to calculate the answer.

Excel follows the order of operations. First, add the values ​​inside the parentheses: (45.80 + 68.70 + 159.60) = 274.10. Then, multiply that value by the tax rate: 274.10 * 0.075. The result will show that the sales tax is $20.56.

 

It's especially important to follow the order of operations when creating formulas. Otherwise, Excel won't calculate the results correctly. In the example, if the parentheses weren't included, the multiplication would be calculated first, leading to an incorrect result. Parentheses are generally the best way to specify which calculation should be performed first in Excel.

How to create a complex formula using the order of operations

The example below uses cell references along with numerical values ​​to create a complex formula that calculates the subtotal for a food and beverage bill. The formula calculates the cost of each item on the menu first, then adds those values ​​together.

1. Select the cell that will contain the formula. For example, we would select cell C5.

2. Enter the formula. For example, you would enter =B3*C3+B4*C4. This formula will follow the order of operations, first performing the multiplication: 2.79*35 = 97.65 and 2.29*20 = 45.80 . Then, it will add these values ​​to calculate the sum: 97.65+45.80.

3. Double-check the accuracy of the formula, then press Enter on the keyboard. The formula will be calculated and the result displayed. In the example, the result shows the subtotal of the order is $143.45.

You can add parentheses to any equation to make it easier to read. Multiplication calculations can be placed in parentheses to clarify that they will be calculated before addition and that the result of the formula in this example will not change.

Excel won't always tell you if your formula is wrong, so checking all your formulas will be up to you.