A complete guide to Excel 2016 (Part 14): Create complex formulas

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

To write formulas that give you the correct answer, you need to understand the order in which the calculation is performed. Join TipsMake.com to learn how to create more complex formulas in Excel 2016 . Invite people to consult!

Watch the video below to learn more about complex formulas in Excel 2016:

I. Order of operators (The order of operations)

Excel calculates the formula based on the order of operators as follows:

1. Operators are enclosed in parentheses

2. Exponential calculation (Example: 3 ^ 2)

3. Multiply and divide, depending on the position to perform the calculation

4. Add and subtract, depending on the position to perform the calculation

Memorization (mnemonic) can help you remember the order is PEMDAS or P lease E xcuse.

Here's how the order in which calculations are performed is used to calculate formulas in Excel.

1. Using PEMDAS in calculation 10+ (6-3) / 2 ^ 2 * 4-1 . Although this formula looks complicated, we can use step-by-step order to find the right answer.

1. First, we will start calculating in brackets before the following parenthesis. In this case, there is only one thing we need to calculate: 6-3 = 3 .

1. As you can see, the formula has become simpler. Next, we will see if there are any exponents. There is an exponent: 2 ^ 2 = 4 .

1. Next, we will solve multiplication and division, proceed from left to right. Because division is preceded by multiplication, it is calculated first: 3/4 = 0.75 .

1. Now, we will continue to solve the remaining multiplication: 0.75 * 4 = 3 .

1. Next, take into account addition or subtraction, again from left to right. Perform the previous addition: 10 + 3 = 13 .

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

1. Now, our answer is: 12 .
   And this is exactly the result you will get if you enter the formula into Excel.

 

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.

1. 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 .

1. 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.

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 .

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 .

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 .

1. 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.

Note: Excel doesn't always notify you if your formula has an error, so check all your formulas.

Having fun!