And this is exactly the result you will get if you enter the formula into Excel.
A complete guide to Excel 2016 (Part 14): Create complex formulas Picture 8
II. Create complex formulas
In the following example, we will introduce how Excel uses the operator's order to solve a more complex formula. Here, we want to calculate sales tax costs for an invoice.
To do this, we will write the formula like = (D3 + D4 + D5) * 0.075 in cell D6 . This formula will add the price of the item, then multiply that value by the 7.5% tax rate (written as 0.075) to calculate the result.
A complete guide to Excel 2016 (Part 14): Create complex formulas Picture 9
Excel follows the order of operators and first adds the values in parentheses: (45.80 + 68.70 + 159.60) = 274.10 . After that value multiplier with tax rate: 274.10 * 0.075 . The result will show that sales tax is $ 20.56 .
A complete guide to Excel 2016 (Part 14): Create complex formulas Picture 10
It is especially important to follow the order of calculations when creating formulas. Otherwise, Excel will give incorrect calculation results. In the example, without parentheses, multiplication will be calculated first and the result given will be incorrect. Parentheses are often the best way to determine which calculations will be performed first in Excel.
A complete guide to Excel 2016 (Part 14): Create complex formulas Picture 11
III. Create a complex formula using the order of performing calculations
In the example below, we will use cell references along with numeric values to create a complex formula that calculates a subtotal function. The formula will calculate the cost of each menu item, then add these values together.
1. Select the cell containing the formula. In the example, we will select cell C5 .
A complete guide to Excel 2016 (Part 14): Create complex formulas Picture 12
2. Enter your formula. In the example, we will type = B3 * C3 + B4 * C4 . This formula will follow the order of the calculation, first performing multiplication: 2.79 * 35 = 97.65 and 2.29 * 20 = 45.80 . Then these values will be added to calculate the sum: 97.65 + 45.80 .
A complete guide to Excel 2016 (Part 14): Create complex formulas Picture 13
3. Check the accuracy of the formula, then press Enter on the keyboard. The formula will calculate and display the results. In the example, the results show that the total subtotal function is $ 143.45 .
A complete guide to Excel 2016 (Part 14): Create complex formulas Picture 14
You can add parentheses to any equation to make it easier to read. Although it will not alter the results of the formula in this example, we can include multiplication in parentheses to clarify that they will be calculated before adding.
A complete guide to Excel 2016 (Part 14): Create complex formulas Picture 15
Note: Excel doesn't always notify you if your formula has an error, so check all your formulas.