Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo
Workplace Software and Skills

9.7 Calculations and Basic Formulas in Google Sheets

Workplace Software and Skills9.7 Calculations and Basic Formulas in Google Sheets

Table of contents
  1. Preface
  2. 1 Technology in Everyday Life and Business
    1. Chapter Scenario
    2. 1.1 Computing from Inception to Today
    3. 1.2 Computer Hardware and Networks
    4. 1.3 The Internet, Cloud Computing, and the Internet of Things
    5. 1.4 Safety, Security, Privacy, and the Ethical Use of Technology
    6. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  3. 2 Essentials of Software Applications for Business
    1. Chapter Scenario
    2. 2.1 Software Basics
    3. 2.2 Files and Folders
    4. 2.3 Communication and Calendar Applications
    5. 2.4 Essentials of Microsoft 365
    6. 2.5 Essentials of Google Workspace
    7. 2.6 Collaboration
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  4. 3 Creating and Working in Documents
    1. Chapter Scenario
    2. 3.1 Navigating Microsoft Word
    3. 3.2 Formatting Document Layout in Microsoft Word
    4. 3.3 Formatting Document Content in Microsoft Word
    5. 3.4 Collaborative Editing and Reviewing in Microsoft Word
    6. 3.5 Document Design
    7. 3.6 Navigating Google Docs
    8. 3.7 Formatting Layout and Content in Google Docs
    9. 3.8 Collaborative Editing and Reviewing in Google Docs
    10. 3.9 Versions and Version History
    11. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  5. 4 Document Preparation
    1. Chapter Scenario
    2. 4.1 Microsoft Word: Advanced Formatting Features
    3. 4.2 Working with Graphics and Text Tools in Microsoft Word
    4. 4.3 Managing Long Documents in Microsoft Word
    5. 4.4 Google Docs: Enhanced Formatting Features
    6. 4.5 Working with Graphics and Text Tools in Google Docs
    7. 4.6 Managing Long Documents in Google Docs
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  6. 5 Advanced Document Preparation
    1. Chapter Scenario
    2. 5.1 Creating Different Document Types in Microsoft Word
    3. 5.2 Mail Merge in Microsoft Word
    4. 5.3 Creating Forms in Microsoft Word
    5. 5.4 Creating Different Document Types in Google Docs
    6. 5.5 Creating Forms in Google Docs
    7. 5.6 Advanced Collaboration in Google Docs
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  7. 6 Preparing Presentations
    1. Chapter Scenario
    2. 6.1 Presentation and Design Essentials
    3. 6.2 Designing a Presentation in Microsoft PowerPoint
    4. 6.3 Formatting Microsoft PowerPoint Slides: Layout and Design Principles
    5. 6.4 Adding Visuals and Features to Microsoft PowerPoint Slides
    6. 6.5 Designing a Presentation in Google Slides
    7. 6.6 Creating Google Slides: Layout and Text
    8. 6.7 Adding Visuals and Features to Google Slides
    9. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  8. 7 Advanced Presentation Skills
    1. Chapter Scenario
    2. 7.1 Effective Presentation Skills
    3. 7.2 Finalizing a Slide Collection
    4. 7.3 Preparing a Microsoft PowerPoint Collection for Presentation
    5. 7.4 Preparing a Google Slides Collection for Presentation
    6. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  9. 8 Content Management Systems and Social Media in Business
    1. Chapter Scenario
    2. 8.1 What Are Content Management Systems?
    3. 8.2 Common Content Management Systems
    4. 8.3 Creating Content with a Content Management System
    5. 8.4 Search Engine Optimization
    6. 8.5 Social Media in Business
    7. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  10. 9 Working with Spreadsheets
    1. Chapter Scenario
    2. 9.1 Microsoft Excel Basics
    3. 9.2 Text and Numbers in Microsoft Excel
    4. 9.3 Calculations and Basic Formulas in Microsoft Excel
    5. 9.4 Formatting and Templates in Microsoft Excel
    6. 9.5 Google Sheets Basics
    7. 9.6 Text and Numbers in Google Sheets
    8. 9.7 Calculations and Basic Formulas in Google Sheets
    9. 9.8 Formatting and Templates in Google Sheets
    10. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  11. 10 Advanced Excel Formulas, Functions, and Techniques
    1. Chapter Scenario
    2. 10.1 Data Tables and Ranges
    3. 10.2 More About Formulas
    4. 10.3 Using Arithmetic, Statistical, and Logical Functions
    5. 10.4 PivotTables
    6. 10.5 Auditing Formulas and Fixing Errors
    7. 10.6 Advanced Formatting Techniques
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  12. 11 Advanced Excel Spreadsheets: Statistical and Data Analysis
    1. Chapter Scenario
    2. 11.1 Understanding Data, Data Validation, and Data Tables
    3. 11.2 Statistical Functions
    4. 11.3 What-If Analysis
    5. 11.4 PivotTables/Charts
    6. 11.5 Data Analysis Charts
    7. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  13. 12 Using Excel in Accounting and Financial Reporting
    1. Chapter Scenario
    2. 12.1 Basic Accounting
    3. 12.2 Financial Functions in Microsoft Excel
    4. 12.3 Integrating Microsoft Excel and Accounting Programs
    5. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  14. 13 Understanding and Using Databases
    1. Chapter Scenario
    2. 13.1 What Is a Database?
    3. 13.2 Microsoft Access: Main Features and Navigation
    4. 13.3 Querying a Database
    5. 13.4 Maintaining Records in a Database
    6. 13.5 Creating Reports in Microsoft Access
    7. 13.6 Creating Forms in Microsoft Access
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  15. 14 Advanced Database Use
    1. Chapter Scenario
    2. 14.1 Advanced Queries in Microsoft Access
    3. 14.2 Multiple Table Forms
    4. 14.3 Customizing Forms
    5. 14.4 Customizing Reports
    6. 14.5 Using Macros
    7. 14.6 Data Analysis and Integration
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  16. 15 Integrating Applications
    1. Chapter Scenario
    2. 15.1 Microsoft 365: Collaboration and Integration
    3. 15.2 Microsoft Word: Integration with Microsoft Excel and Microsoft Access
    4. 15.3 Microsoft Word and Microsoft PowerPoint Integration
    5. 15.4 Microsoft Excel and Microsoft PowerPoint Integration
    6. 15.5 Microsoft Excel and Microsoft Access Integration
    7. 15.6 Integrating Data from Other Programs into Google Workspace
    8. 15.7 New Developments: The Role of Artificial Intelligence
    9. 15.8 Mastering Workplace Software Skills: A Project
    10. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
  17. Index

Learning Objectives

By the end of this section, you will be able to:

  • Add numbers in Sheets
  • Subtract numbers in Sheets
  • Multiply numbers in Sheets
  • Divide numbers in Sheets

Microsoft Excel has been around for decades, yet there are no functions for subtracting or dividing numbers. Users must still build formulas with the appropriate mathematical operator to divide or subtract numbers. Google Sheets has added a MINUS and a DIVIDE function. WorldCorp likes being able to use these functions in their number crunching.

Adding Numbers

Adding numbers in Sheets uses the same commands and formulas as Excel does. Here, you will compare orders from two clients, Client A and Client B, who ordered the same product—headphones—but at different times and in different quantities. Figure 9.57 is a snapshot of these orders, showing the two clients’ order history in two different tables.

Two spreadsheets are visible. The top spreadsheet is labeled Client A. The lower spreadsheet is labeled Client B.
Figure 9.57 The addition formula in Sheets works the same way as in Excel. We can use the addition formula to summarize this information into one shorter table. (Google Sheets is a trademark of Google LLC.)

You will design a new table that summarizes these orders by client and by month using the data found in the two client tables. Figure 9.58 shows a snapshot of the summarized orders of each client, separated by month. In this table, we used the + sign to add the cells containing numbers (for example: =G3+G4+G5+G6+G7+G8+G9) in the formula bar to add together total sales for each month by client. Note this syntax is the same as Excel.

Cell P5 is selected ($19,584.00) and “=G3+G4+G5+G6+G7+G8+G9” shown in the formula bar.
Figure 9.58 You can use the addition formula to add up all of the sales that occur in a specific month. (Google Sheets is a trademark of Google LLC.)

The same calculations can occur using functions. You can use the table in Figure 9.58, but replace the plus sign with the SUM function, like Excel. Type “=SUM", an open parenthesis, then list the cells you wish to add, putting a comma after each cell reference. Then close the formula with a closing parenthesis (Figure 9.59). Repeat the process for all sales data by month and client.

Cells D21, D22, D23, and D24 are selected in a group. The formula bar and a cell in column L says '=SUM(D21:D24)'
Figure 9.59 Sheets offers a SUM function that can be used instead of formulas. (Google Sheets is a trademark of Google LLC.)

Subtracting Numbers

One way to use subtraction in data analysis is to determine how far an order is above or below the average order. This information can tell us which clients might need more attention to increase their orders or to analyze ordering patterns due to seasonality. To perform this calculation, we will measure the distance from the average. First, find the average, which is the total sales divided by the number of orders. Using the data in Figure 9.57, insert two columns after Quantity for the average quantity and distance from average and insert two more columns after FOB $ for the average FOB $ and distance from average. Then, find the average for each of these and repeat the value in all cells in that column. Then, to find the distance from average, subtract the average from the individual order, for both Quantity and FOB $. Figure 9.60 shows the average columns and the formula for finding the distance from average. Then, apply the formula to all rows (Figure 9.61). Repeat the process for the distance from average for FOB $ (Figure 9.62). In these statistical analyses, you can see that there are some large orders that make the average high, which in turn makes many smaller orders have a negative distance from average and makes them seem even smaller by comparison.

Cell G3 is selected and formula bar reads: =E3-F3, which also shows in cell G3. A pop up box shows above the “=” sign in cell G3 with $ (34.72) inside.
Figure 9.60 You can perform subtraction calculations in Sheets using formulas, just as in Excel. After finding the average, subtract the average from the quantity to get the distance from average. (Google Sheets is a trademark of Google LLC.)
Cell G24 is selected and the formula bar shows: =E24-F24. The information in the cell is: $ (14.72).
Figure 9.61 Then, copy the formula to all cells in the same row. (Google Sheets is a trademark of Google LLC.)
Cell J24 is selected. The formula in the formula bar is: =H24-I24 with cell showing: $ (706.67).
Figure 9.62 Do the same calculation to find the distance from average for the FOB $. (Google Sheets is a trademark of Google LLC.)

Sheets also offers a subtraction function, unlike Excel. You can replace the subtraction formula with the MINUS function. First, type “=minus(” and list the cell references separated by a comma (Figure 9.63). The MINUS function subtracts numbers in the order they appear in the formula just as if they were separated by “-” in a formula. You can repeat this for all the subtraction formulas in the table. You can only subtract two numbers using the MINUS function.

Cell G3 is selected and the formula in the formula bar reads: =minus(E3,f3)|. In cell G3 this shows: =minus(E3,f3).
Figure 9.63 Sheets has the advantage over Excel in that it has a MINUS function. (Google Sheets is a trademark of Google LLC.)

Multiplying Numbers

As in Excel, when using the multiplication formula in Sheets, you can multiply a cell reference by a constant or a cell reference by a cell reference. Using the same dataset, you can find the FOB $ by multiplying the quantity by the price per unit. Figure 9.64a shows multiplication of the cell reference from the Quantity column by the constant price per unit without adding a column for the value. A Price per Unit column is added and the constant in the formula is replaced with the cell reference (Figure 9.64b). You can repeat the calculation for all rows, as previously illustrated.

(a) Cell F3 displays $720.00 in cell and formula bar displays =D3*48. (b) An addition column (F) has been added (Price per Unit). Cell G3 (FOB$) displays =D3*F3.
Figure 9.64 You can use cell references and constants for multiplication using formulas in Sheets. (a) The price per unit is the same for all items in this table, so you can multiply the quantity by the price without adding a column. Alternatively, you can add a column for the price per unit and (b) use cell references to perform the calculation. (Google Sheets is a trademark of Google LLC.)

To use the function, replace the formula with “=multiply(” and add the constant and/or cell references in parentheses, separated by commas (Figure 9.65). You can only multiply two numbers using the MULTIPLY function. Sheets has another function, PRODUCT, that can multiply a series of numbers rather than just two.

Cell G3 is selected and the formula bar and cell say: =multiply(D3,F3)
Figure 9.65 The function for multiplication in Sheets is MULTIPLY. (Google Sheets is a trademark of Google LLC.)

Dividing Numbers

Dividing numbers using formulas in Sheets is also the same as in Excel. You can use the same sales data to find the proportion of sales of each client compared with the total. Start with the data from Figure 9.58, organized by month. Add a column for the total sales for each month and one for the proportion, and then add a formula to calculate the total sales for clients A and B for each month (Figure 9.66).

Now that you have the totals, find the proportion by dividing the FOB $ in O3 by the total in Q3 (Figure 9.67). Repeat the same operation for the other three rows (Figure 9.68). This analysis shows the sales behavior differences between clients A and B. This data analyses shows that in December, client B had 52.36 percent of the sales, and in January client A had 65.81 percent of the sales. The proportion can be changed into a percentage to increase readability, as shown in Figure 9.69.

Spreadsheet visible. Cell Q5 in Totals column contains formula =O5+O6 (Box above reads: $29,760.00). Cell O5 contains $10,176.00 and cell O6 contains $19,584.00.
Figure 9.66 You can use division to find the proportion of sales by client and by month. First, find the total sales for each month. (Google Sheets is a trademark of Google LLC.)
This spreadsheet shows previously empty cells Q5 and Q6 both filled in with “$29,760.00.” In cell R3, this is written: = O3/O3 with a text box above with $ 0.48 inside.
Figure 9.67 Then, use the division formula to divide the client’s total by the total monthly sales. (Google Sheets is a trademark of Google LLC.)
This spreadsheet shows column R filled in: R3: 0.4764; R4: 0.5236; R5: 0.3419; R6: 0.6581.
Figure 9.68 Carry these formulas down the column to get the proportion for each client in each month. (Google Sheets is a trademark of Google LLC.)
Spreadsheet shows Column R selected and data inside as a percentage with the % sign. The % icon from Action bar is selected – Format as percent. Formula bar shows: = O3/$Q$.
Figure 9.69 Reformatting the cells as percentages makes the data more useful. (Google Sheets is a trademark of Google LLC.)

The division function in Sheets works in the same way as other mathematical functions. You can type “=divide(” and then enter the cell references, separated by a comma (Figure 9.70). Notice that the result of the DIVIDE function is a decimal. The numbers are divided in the order in which they are placed in the function, and you can only divide two numbers using the DIVIDE function. The QUOTIENT function in Sheets also works in the same way as Excel. The result of the QUOTIENT function will be a whole number, not a decimal. For example, when you divide 5 by 4, the answer is 1.25, but using the QUOTIENT function, the answer will be displayed as 1.

Cell R3 is selected and =divide(O3,$Q$6)| shows in the formula bar and in Cell R3.
Figure 9.70 You may use DIVIDE as a function in Sheets. (Google Sheets is a trademark of Google LLC.)
Citation/Attribution

This book may not be used in the training of large language models or otherwise be ingested into large language models or generative AI offerings without OpenStax's permission.

Want to cite, share, or modify this book? This book uses the Creative Commons Attribution License and you must attribute OpenStax.

Attribution information
  • If you are redistributing all or part of this book in a print format, then you must include on every physical page the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
  • If you are redistributing all or part of this book in a digital format, then you must include on every digital page view the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
Citation information

© Jan 3, 2024 OpenStax. Textbook content produced by OpenStax is licensed under a Creative Commons Attribution License . The OpenStax name, OpenStax logo, OpenStax book covers, OpenStax CNX name, and OpenStax CNX logo are not subject to the Creative Commons license and may not be reproduced without the prior and express written consent of Rice University.