In this lesson, we will analyze how to create a complex formula in Excel, and also consider typical mistakes that novice users make due to inattention. If you are new to Excel, we recommend that you first turn to the lesson where we discussed creating simple formulas.
How to create a complex formula in Excel
In the example below, we will demonstrate how Excel calculates complex formulas based on the order in which operations are performed. In this example, we want to calculate the amount of sales tax for food services. To do this, write the following expression in cell D4: =(D2+D3)*0,075. This formula will add the value of all invoice items and then multiply by the sales tax amount 7,5% (written as 0,075).
Excel follows the order of operations and adds the values in parentheses first: (44.85+39.90)=$84.75. Then he multiplies this number by the tax rate: * $ 84.75 0.075. The result of the calculation shows that the sales tax will be $ 6.36.
It is extremely important to enter complex formulas with the correct order of operations. Otherwise, Excel calculations may be inaccurate. In our case, in the absence of parentheses, the multiplication is performed first, and the result will be incorrect. Parentheses are the best way to define the order of calculations in Excel.
Create complex formulas using workflow
In the example below, we’ll use links along with quantities to create a complex formula that calculates the total cost of a food bill. The formula will calculate the cost of each menu item and then add all the values together.
- Select the cell that will contain the formula. In our example, we have selected cell C4.
- Enter the following expression into it: =B2*C2+B3*C3. The actions in the formula will be performed in accordance with the rules of order, therefore, the first is the multiplication: 2.29 * 20 = 45.80 и 3.49 * 35 = 122.15. These values will then be summed to calculate the total cost: 45.80 + 122.15.
- Check, then click Enter on keyboard. The formula will calculate and display the result. In our case, the calculation result shows that the total cost of the order is $ 167.95.
You can add parentheses to any formula to make it easier to read. Even though it won’t change the result of the calculation in this example, we can still parenthesize the multiplication. By this we specify that it is performed before addition.
Excel does not always warn you about errors in a formula, so you need to check all your formulas yourself. To find out how you can do this, check out the Formula Validation lesson.