Complex Formulas & Order of Operations
Use PEMDAS and parentheses to control calculation order when building multi-step formulas such as subtotals and tax.
Video
Watch the lesson video, then complete the reading and challenge.
Lesson Notes
Read through the key concepts before you try the challenge.
Order of Operations (PEMDAS)
When a formula includes multiple operators, Excel follows an order of operations. PEMDAS stands for Parentheses, Exponents, Multiplication, Division, Addition, and Subtraction.
If a result looks incorrect, it is often because Excel evaluated a part of the formula in a different order than you expected.
Excel evaluates formulas in this order unless parentheses change it.
Excel evaluates expressions inside parentheses first before moving on to other operations.
After parentheses, Excel evaluates exponents if present.
Multiplication and division are evaluated next, working from left to right.
Finally, Excel performs addition and subtraction from left to right.
Building a Subtotal Formula
A subtotal often involves adding multiple calculations together. In this example, we multiply Unit Price by Quantity for each item, then add the results.
Click the subtotal cell first, then type an equals sign (=) to begin entering your formula.
By adding parentheses, Excel performs the multiplication steps first before summing the results. If your subtotal seems too large or too small, check whether parentheses are controlling the order properly.
Calculating Tax Correctly
To calculate tax, you typically add totals first, then multiply by the tax rate. Parentheses ensure Excel performs the addition before multiplying.
Without parentheses, Excel multiplies before adding, which changes the result.
If your tax calculation is incorrect, check whether Excel multiplied something before completing the addition.
Key Takeaways
- Excel follows PEMDAS automatically.
- Use parentheses to control the order of calculations.
- Multiplication and division happen before addition and subtraction.
- If totals look wrong, check parentheses first.
Understanding Excel's Order of Operations (PEMDAS)
Excel calculates complex formulas using a specific hierarchy known as the Order of Operations. If you do not understand this hierarchy, your formulas may return unexpected results.
Excel follows PEMDAS: Parentheses, Exponents, Multiplication, Division, Addition, and Subtraction.
Excel calculates formulas using PEMDAS. Multiplication and division are solved left-to-right, as are addition and subtraction.
When operators share the same priority level—such as multiplication and division—Excel evaluates them from left to right.
Knowledge Check
In the formula =2+3*4, what is the result?
Practice File
Download this file and follow along with the lesson.
Challenge
Apply what you've learned in this lesson.
Complete the following tasks using the practice workbook:
- Download the practice file above.
- Click the Challenge worksheet tab in the bottom-left of the workbook.
Click cell D7 first. Type an equals sign (=) to begin your formula.
In cell D7, create a formula that calculates the tax for the invoice. Use a sales tax rate of 7.5%. (You can enter 7.5% directly, or use 0.075.)
In cell D8, calculate the total for the order. Use SUM to add cells D3:D7.
In cell D9, calculate the total after a 10% discount. A 10% discount means you pay 90% of the total.
If your totals look too high or too low, double-check your parentheses and percent formatting.
When you are finished, your spreadsheet should look like this:
