Google Sheets

Complex formulas

Watch the video (5:06).

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 us which operation to calculate first. In order to use Google Spreadsheets to calculate complex formulas, you will need to understand the order of operations.

Watch the video to learn how to create complex formulas.

Order of operations

Google Spreadsheets calculates formulas based on the following order of operations:

1. Operations enclosed in parentheses
2. Exponential calculations (3^2, for example)
3. Multiplication and division, whichever comes first
4. Addition and subtraction, whichever comes first

A mnemonic that can help you remember the order is Please Excuse My Dear Aunt Sally.

Example 1

The following example demonstrates how to use the order of operations to calculate a formula:

Following the order of operations

Example 2

This example demonstrates how Google Spreadsheets solves a complex formula using the order of operations. The complex formula in cell C8 calculates the sales tax by adding the prices together and multiplying by the 5.5% tax rate (which is written as 0.055).

Entering a complex formula

Google Spreadsheets follows the order of operations and first adds the values inside the parentheses: (C5+C6+C7) = $14.37. Then it multiplies by the tax rate: $14.37*0.055. The result will show that the tax is $0.79.

The result in C8

It's important to enter complex formulas with the correct order of operations. Otherwise, Google Spreadsheets will not calculate the results accurately. In our example, using the parentheses is important, otherwise multiplication is calculated first and the result is incorrect.

Result of an incorrect formula