Complex Excel formulas include more than one operator. Understand how to use complex formulas in Excel and other programs.
A simple formula is a mathematical expression with one operator, such as 7+9. A complex formula has more than one mathematical operator, such as 5+2*8. When there is more than one operation in a formula, the order of operations tells your spreadsheet which operation to calculate first. In order to use complex formulas, you will need to understand the order of operations.
Optional: Download our example file for this lesson.
All spreadsheet programs calculate formulas based on the following order of operations:
A mnemonic that can help you remember the order is PEMDAS, or Please Excuse My Dear Aunt Sally.
Click the arrows in the slideshow below to learn more about how the order of operations is used to calculate complex formulas.
While this formula may look really complicated, we can use the order of operations step by step to find the right answer.
First, we'll start by calculating anything inside the parentheses. In this case, there's only one thing we need to calculate: 6-3=3.
As you can see, the formula already looks a bit simpler. Next, we'll look to see if there are any exponents. There's one: 2^2=4.
Next, we'll solve any multiplication and division, working from left to right. Because the division operation comes before the multiplication, it is calculated first: 3/4=0.75.
Now, we'll calculate our remaining multiplication operation: 0.75*4=3.
Next, we'll calculate any addition or subtraction, again working from left to right. Addition comes first: 10+3=13.
Finally, we have one remaining subtraction operation: 13-1=12.
And now we have our answer: 12. This is the exact same result you would get if you entered the formula into a spreadsheet.
Using parentheses within a formula can be very important. Because of the order of operations, it can completely change an answer. Let's try the same problem from above, but this time we'll add parentheses to the last part.
In the example below, we'll demonstrate 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.
The spreadsheet then follows the order of operations and first adds the values inside the parentheses: (44.85+39.90) = $84.75. Then it 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, the spreadsheet 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 a formula.
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 add those values together.
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.
Your spreadsheet will not always tell you if your formula contains an error, so it's up to you to check all of your formulas. To learn how to do this, check out the Double-Check Your Formulas lesson.