Google Sheets

Page 1

Introduction

Google Spreadsheets can help you keep track of your household budget, checking account, business inventory, company finances, and more. To create these types of spreadsheets, you will need to understand complex formulas and the order of operations.

In this lesson, you will learn how Google Spreadsheets follows the order of operations to solve complex formulas. You will also learn how to create complex formulas.

Page 2

Complex formulas

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:

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

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.

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.

Page 3

To create a complex formula using the order of operations:

In our example invoice below, we will use the order of operations to create a formula that calculates a subtotal. Our formula will add the available items and the backordered items, then it will multiply by the price.

1. Select the cell that will display the calculated value and type the equals sign (=).
   
   Typing the equal sign
2. Enter your formula. In our example, we will type (C5+D5)*E5. This formula will add the available items and backordered items first because they are enclosed in parentheses, then it will multiply by the price.
   
   Entering a formula
3. Press the Enter key on your keyboard. The formula calculates, and Google Spreadsheets displays the result. Double-check your formula for accuracy.
   
   The result

Google Spreadsheets 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, read our article on why you should Double-Check Your Excel Formulas.

Page 4

Challenge!

To work through the challenge, open GCFLearnFree L13: IT Order Form and copy the file to your Google Drive. View the instructions below the challenge if you are not sure how to make a copy of the file.

1. In cell F6, write a complex formula that first adds the values in C6 and D6, then multiples by the value in E6.
2. The formula in cell F9 has been written incorrectly. It multiplies first and then adds. Correct the formula by adding parentheses so it adds first and then multiplies.

To copy the example file to your Google Drive:

In these tutorials, we will provide example files you can use to practice what you've learned in each lesson. Because these files are Google Docs we have chosen to share, you will need to copy the file to your Google Drive before you can edit the file.

1. Click the link at the top of this page to open the example file.
2. The example file will appear in a new browser tab or window. If you are not currently signed in to your Google account, locate and click Sign in on the top-right corner of the page.
   
   Signing in to your Google Account
3. After you have signed in to your Google account, locate and select File in the toolbar menu and select Make a copy... from the drop-down menu.
   
   Making a copy of the example file
4. The Copy Document dialog box will appear. Enter a new title for the file, then click OK.
   
   Naming the file and clicking OK
5. The copy of the file will appear in a new browser tab. Now you're ready to start using the example file.
   
   Viewing the copied example file in a new tab