Chapter 4: SUM and AVERAGE Functions

Apart from using mathematical operators in your formulas, you can also use functions that can help to quickly create formulas. For example, the function that adds the values in cells or a range of cells is called the SUM function.

To illustrate, the following formula calculates the values in ten cells without using a function:

=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10

This is not pretty, isn’t it? This can be prone to mistakes such as missing a cell reference or typing the wrong cell reference. With the SUM function, your formula would be shortened to:

=SUM (A1:A10)

As usual, start with the equal sign (=) to create a formula and then followed by the SUM function. In brackets, type the first cell reference, A1, and the last cell reference, A10 with the colon symbol (:) in between the cell references. It tells Excel to include all cells between the two endpoint cell references, all cells from cell A1 until cell A10.

Much simpler, right? The SUM function adds the values from a range of cells, from cell A1 to cell A10.

In this chapter, you will learn to use two popular functions in Excel, the SUM, and AVERAGE functions.

The SUM Function

Type the contents in their respective cells as shown in the Figure below. The contents are five car manufacturers with their revenues. The total and average revenues will be calculated using the SUM and AVERAGE functions.

Use the default font and font size. Adjust any column width if necessary. It is advisable to save the workbook before you proceed much further to avoid losing your work.

Figure 4.1 Type the contents in their respective cells as shown.

After typing all the contents above, create a formula to calculate the total revenue in cell B7 using the SUM function. The total revenue will include all the car manufacturers’ revenues from cell B2 until cell B6. Click cell B7 to make it an active cell and type =SUM (B2:B6) as the formula using the SUM function.

See the Figure below.

Figure 4.2 Type the formula using the SUM function.

After typing the formula, you can press Enter as usual to let Excel execute the formula and the active cell moves to the next cell below, cell B8 or you can press Ctrl+Enter to also let Excel execute the formula but the active cell stays in the cell B7. Let’s press Ctrl+Enter. You will see the SUM function in the Formula Bar and the formula result in cell B7.

See the Figure below.

Figure 4.3 Observe the SUM function as shown in the Formula Bar

Without the SUM function, you must write a longer formula using all the included cell references and a plus symbol (+), such as =B2+B3+B4+B5+B6. With the SUM function, the formula is shortened to =SUM (B2:B6).

Now, let’s calculate the average revenue of the five car manufacturers’ revenues using the AVERAGE function.

The AVERAGE Function

Click cell B8 to make it an active cell and type =AVERAGE (B2:B6) as the formula using the AVERAGE function.

See the Figure below.

Figure 4.4 Type the formula using the AVERAGE function.

After typing the formula, you can press Enter as usual to let Excel execute the formula and the active cell moves to the next cell below, cell B9 or you can press Ctrl+Enter to also let Excel execute the formula but the active cell stays in the cell B8. Let’s press Ctrl+Enter. You will see the AVERAGE function in the Formula Bar and the formula result in cell B8.

See the Figure below.

Figure 4.5 Observe the SUM function as shown in the Formula Bar

Without the AVERAGE function, you must write a longer formula using all the included cell references and a plus symbol (+), such as =(B2+B3+B4+B5+B6)/5. With the AVERAGE function, the formula is shortened to =SUM (B2:B6).

The above SUM and AVERAGE functions are among the most popular functions among Excel users. You can learn more about other functions that suit your purpose on your own by exploring the functions from within Excel itself or from other sources such as the Internet.

That’s all for this Chapter.

You’re awesome!