9.3 Calculations and Basic Formulas in Microsoft Excel

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

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.

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.

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)

Figure 9.20 Then, choose the cells you want to include. (Used with permission from Microsoft)

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.

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.

Figure 9.23 For multiplication, use cell references to construct formulas. (Used with permission from Microsoft)

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.

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.

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)