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

9.3 Calculations and Basic Formulas in Microsoft Excel

Workplace Software and Skills9.3 Calculations and Basic Formulas in Microsoft Excel

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:

  • Create basic formulas
  • Add numbers in Excel using a formula or function
  • Subtract numbers in Excel using a formula or function
  • Multiply numbers in Excel using a formula or function
  • Divide numbers in Excel using a formula or function

At WorldCorp, you manage several account teams, each of which has a sales team. Part of your job is to compare the sales agents’ revenues using some basic data analysis, which you can extend to other applications in your job. To assess the performance of the agents and the sales team, you will need to create some basic formulas. These formulas could include the total sales during a specific time period by agent or the average sales per time period for the whole team. These values can then be used to put together a report of the performance of your entire team. Upper management compiles the reports from all the teams to summarize the company’s performance. Using basic formulas in Microsoft Excel expedites the process and helps management to uniformly analyze the information.

Setting Up Basic Formulas

Now that you have learned how to set up a table or spreadsheet, you can perform calculations on the data. First, select the cell where you want the calculation to appear. Use the Formula Bar located below the ribbon to type in the formula or function, starting with the “=” sign (Figure 9.17).

Excel ribbon with the Home tab selected and a spreadsheet. Cell H2 is selected and shows “$26,016.00” and “=E2*G2” typed in the formula bar. Currency is selected in the Number category.
Figure 9.17 Formulas must begin with the “=” sign and contain mathematical operators (i.e., +, -, *, /). (Used with permission from Microsoft)

Adding Numbers

You have learned the two ways to add quantities in Excel: formulas and functions. We can use WorldCorp’s sales data to illustrate how to add numbers. Figure 9.18a contains a set of sales data, with a row at the bottom for the total of all the sales but with the total amount missing. To fill in this empty cell, first, type “=” to open the calculation procedure. Then, click on the first cell you want to add, type a “+” sign, then click on the next cell in the column. Continue this process until all the cells you want to add are included. Figure 9.18b shows the finished formula that includes all the cells in the columns. Notice that Excel adds colors to the cells to help you follow the formulas.

(a) Cell E9 displays Total; blank cell F9 selected. (b) Cell F9 selected; formula bar displays =F3+F4+F5+F6+F7+F8; cells in F column are highlighted in different colors that correspond to cells in formula.
Figure 9.18 You can use a formula to find the sum of a set of cells. (a) The table has a cell for the total sales. First, type an “=” sign, then use the cell references or click on the cells and a plus sign to construct the formula. (b) The finished formula shows all of the cells referenced. (Used with permission from Microsoft)

When you have a small set of data, or when the numbers you are adding using the “+” operator are not all in one row or column, using an addition formula may be the best way to find your sum. However, Excel has built-in shortcuts, such as the SUM function. Functions can often save you time over performing the same calculations using formulas. Like formulas, functions must begin with an equals sign, but rather than using cell references to build an equation, you type the function name, in this case, SUM (Figure 9.19). Then, type an open parenthesis, insert the cell references, separated by commas, and then close the parentheses. You can click on each cell individually or type its cell reference, as in Figure 9.20, but you’ll notice that this method does not save time because you are still entering or clicking each cell reference. Functions allow users to reference individual cells, but they also allow us to choose multiple cells at the same time, which does save time. To select a range of cells, which is a group of cells that are contiguous, you can type in the function and opening parenthesis, and then highlight the cells you want to add. You can also type in the range, separating the first cell and the last cell with a colon. Figure 9.21 shows the range as F3:F11. Functions are often faster to type than formulas, but sometimes you will need to use both formulas and functions to perform complex calculations.

Cell F12 selected; displays =sum. A pane displays options for: SUM (selected), SUMIF, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2. A text box reads: Adds all the numbers in a range of cells.
Figure 9.19 Functions are time-saving methods for performing calculations on data. To find the sum of a range of cells, type the equals sign and “SUM.” (Used with permission from Microsoft)
Cell F12 selected; Formula bar reads: =sum(F3,F4,F5,F6,F7,F8,F9,F10,F11. Cells F3-F11 are highlighted in different colors corresponding to their selection. A box reads: SUM[number1, [number2], [number3], [number4], [number5], [number6], [number7], [number8], [number9], [number10], …).
Figure 9.20 Then, choose the cells you want to include. (Used with permission from Microsoft)
Cell F12 and the formula bar show “=sum(f3:f11.” Cells F3 through F11 are selected. A small text box shows: SUM(number1, [number2], …).
Figure 9.21 Using a range, rather than listing individual cells, can save you time. (Used with permission from Microsoft)

Subtracting Numbers

Understanding how to add numbers in Excel makes subtracting them rather intuitive. If you wanted to subtract many cells in a column, you could type out a long subtraction equation, starting with the equals sign and using cell references and the hyphen key, for example, “=F3-F4-F5-F6-F7-F8-F9-F10-F11.” Using a new set of data, you can see in Figure 9.22 that the margin is the price minus the cost. You can carry this formula down the rows in the same column.

Cell D4 is selected and shows 85.00. The formula bar says “=B4-C4.” B4 says $170.00 and C4 says 85.00.
Figure 9.22 Subtraction in Excel uses formulas that are similar to addition formulas, but there is no subtraction function. (Used with permission from Microsoft)

Multiplying Numbers

With multiplication, formulas and functions start getting more complicated. Sometimes, you may need to multiply the values of two or more cells, and other times, you may need to multiply a cell value by a constant value such as a percentage. One may be more efficient than the other, depending on the data. In Figure 9.23, the price per unit is the same for every row, so you can use cell references to multiply the quantity cell by the price-per-unit cell. Because this table contains only one product, the unit price does not change, and it can be tedious to enter the same price for each sale. You can enter the price per unit in the first row and then click and drag the value to the bottom of your table. Another method would be to simplify your table by eliminating the price-per-unit column and including the value in the formula Figure 9.24. Condensing a table in this manner is helpful, especially if you have a large table of data. To construct a multiplication formula, start with an equals sign and use an asterisk (*) as the mathematical operator.

Cell G4 is selected and says “$2,128.00.” The formula bar shows “=D4*F4.” Cell D4 says 76 and cell F4 says 28.
Figure 9.23 For multiplication, use cell references to construct formulas. (Used with permission from Microsoft)
Cell F3 says $280.00. The formula bar says “=D3*28.” Cell D3 says 10.
Figure 9.24 You can also use constants to construct formulas. (Used with permission from Microsoft)

For multiplication, you can also use the PRODUCT function. Figure 9.25 shows an example of the function syntax. When you have entered all of the cell references, close the parentheses, and the product will be the same as it would if you had used a formula.

Cell G3 is selected and says “$280.00.” The formula bar shows “=PRODUCT(D3, F3)”. Cell D3 says 10, and cell F3 says 28.
Figure 9.25 You can use the PRODUCT function for multiplication. (Used with permission from Microsoft)

Dividing Numbers

Division calculations use the operator “/” and follow a similar syntax as other basic mathematical formulas. Figure 9.26a shows an example of division for determining increases or decreases in prices of aluminum. As you can see, the gain on 1/10/2021 was 2.36 percent. Excel does have a QUOTIENT function, but it returns only whole numbers, leaving off the decimal portion of the quotient (Figure 9.26b). It may be useful for some situations, but formulas provide more exact answers.

(a) Cell E4 is selected (0.0236) and formula bar reads: =D4/C4. (b) Column E displays information with a % sign (i.e., 2.36%).
Figure 9.26 The division formula uses a slash and returns (a) a quotient with decimal places, but you can format it to be (b) a percentage. (Used with permission from Microsoft)
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.