Statistical Functions in Microsoft Excel

As you read, review Table 1, which outlines the most common statistical functions in Microsoft Excel. You can apply these functions to a large set of data entered in a spreadsheet. The rest of this section details how to use each type of function in a budget spreadsheet.

The Sum Function

The SUM function is used when you need to calculate totals for a range of cells or a group of selected cells on a worksheet. With regard to the Budget Detail worksheet, we will use the SUM function to calculate the totals in row 12. It is important to note that there are several methods for adding a function to a worksheet, which will be demonstrated throughout the remainder of this chapter. The following illustrates how a function can be added to a worksheet by typing it into a cell location:

  1. Click the Budget Detail worksheet tab to open the worksheet
  2. Click cell C12
  3. Type an equal sign: =
  4. Type the function name SUM
  5. Type an open parenthesis: (
  6. Click cell C3 and drag down to cell C11. This places the range C3:C11 into the function
  7. Type a closing parenthesis: )
  8. Press the ENTER key; the function calculates the total for the Monthly Spend column, which is $1,496


Figure 1 shows the appearance of the SUM function added to the Budget Detail worksheet before pressing the ENTER key.

Budget Detail Worksheet showing range of cells included in function to calculate total of values when "Enter" is pressed.

Figure 1 Adding the SUM Function to the Budget Detail Worksheet


As shown in Figure 1, the SUM function was added to cell C12. However, this function is also needed to calculate the totals in the Annual Spend and LY Spend columns. The function can be copied and pasted into these cell locations because of relative referencing. Relative referencing serves the same purpose for functions as it does for formulas. The following demonstrates how the total row is completed:

  1. Click cell C12 in the Budget Detail worksheet
  2. Click the Copy button in the Home tab of the Ribbon
  3. Highlight cells D12 and E12
  4. Click the Paste button in the Home tab of the Ribbon. This pastes the SUM function into cells D12 and E12 and calculates the totals for these columns
  5. Click cell F11
  6. Click the Copy button in the Home tab of the Ribbon
  7. Click cell F12, then click the Paste button in the Home tab of the Ribbon; since we now have totals in row 12, we can paste the percent change formula into this row


Figure 2 shows the output of the SUM function that was added to cells C12, D12, and E12. In addition, the percent change formula was copied and pasted into cell F12. Notice that this version of the budget is planning a 1.7% decrease in spending compared to last year.

Totals were added to cell range C12:E12 and percent change formula was pasted in cell F12 indicating spending will decrease by 1.7% compared to last year.

Figure 2 Results of the SUM Function in the Budget Detail Worksheet


Integrity Check

Cell Ranges in Statistical Functions

When you intend to use a statistical function on a range of cells in a worksheet, make sure there are two cell locations separated by a colon and not a comma. If you enter two cell locations separated by a comma, the function will produce an output but it will be applied to only two cell locations instead of a range of cells. For example, the SUM function shown in Figure 3 will add only the values in cells C3 and C11, not the range C3:C11.

A comma indicates functions will only be applied to cells C3 and C11, and not the range.


Figure 3 SUM Function Adding Two Cell Locations