Workplace Software and Skills

# 9.7Calculations and Basic Formulas in Google Sheets

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

## Learning Objectives

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

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

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.

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.

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

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.)
Figure 9.61 Then, copy the formula to all cells in the same row. (Google Sheets is a trademark of Google LLC.)
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. 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.

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.

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.

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

Figure 9.70 You may use DIVIDE as a function in Sheets. (Google Sheets is a trademark of Google LLC.)