Statistical Functions in Microsoft Excel

Introduction

In addition to formulas, functions are another way to conduct mathematical computations in Excel. Statistical functions apply a mathematical process to a group of cells in a worksheet. For example, the SUM function adds values in a range of cells. A list of commonly used statistical functions is shown in Table 1. Functions are more efficient than formulas when applying a mathematical process to a group of cells.

If you use a formula to add the values in a range of cells, you must add each cell location to the formula one at a time. Adding the values in a few hundred cell locations can be very time-consuming. However, when you use a function, you can highlight all the cells that contain values you wish to sum in just one step. This section demonstrates a variety of statistical functions that we will add to the Personal Budget workbook. In addition to demonstrating functions, this section also reviews the percent of total calculations and the use of absolute references.

Source: Noreen Brown, Barbara Lave, Julie Romey, Mary Schatz, and Diane Shingledecker, https://openoregon.pressbooks.pub/beginningexcel/chapter/2-2-statistical-functions/
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License.

The Sum Function

Use the SUM function to calculate totals for a range of cells or a group of selected cells on a worksheet. We will use the SUM function for the Budget Detail worksheet to calculate the totals in row 12. Note that several methods for adding a function to a worksheet will be demonstrated throughout this chapter. The following illustrates how to add a function 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.

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, you also need this function to calculate the totals in the Annual Spend and LY Spend columns. You can copy and paste the function 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 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.

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

Figure 3 SUM Function Adding Two Cell Locations

Absolute References (Calculating Percent of Totals)

Data file: Continue with CH2 Personal Budget.

Since totals were added to row 12 of the Budget Detail worksheet, a percent of the total calculation can be added to Column B beginning in cell B3. The percent of total calculation shows the percentage for each value in the Annual Spend column with respect to the total in cell D12. However, after the formula is created, it will be necessary to turn off Excel’s relative referencing feature before copying and pasting the formula to the rest of the cell locations in the column. Turning off Excel’s relative referencing feature is accomplished through an absolute reference. The following steps explain how this is done:

1. Click cell B3 in the Budget Detail worksheet
   
   
2. Type an equal sign: =
   
   
3. Click cell D3
   
   
4. Type a forward slash: /
   
   
5. Click cell D12
   
   
6. Press the ENTER key; you will see that Household Utilities represents 16.7% of the Annual Spend budget (see Figure 4)
   
   

Figure 4 Adding a Formula to Calculate the Percent of Total

Figure 4 shows the completed formula for calculating the percentage that Household Utilities Annual Spend represents to the total Annual Spending for the budget (see cell B3). Normally, we would copy this formula and paste it into the range B4:B11. However, because of relative referencing, both cell references will increase by one row as the formula is pasted into the cells below B3. This is fine for the first cell reference in the formula (D3) but not for the second cell reference (D12).

Figure 5 illustrates what happens if we paste the formula into the range B4:B12 in its current state. Notice that Excel produces the #DIV/0 error code. This means that Excel is trying to divide a number by zero, which is impossible. Looking at the formula in cell B4, you see that the first cell reference was changed from D3 to D4. This is fine because we now want to divide the Annual Spend for Insurance by the total Annual Spend in cell D12. However, Excel has also changed the D12 cell reference to D13. Because cell location D13 is blank, the formula produces the #DIV/0 error code.

Figure 5 #DIV/0 Error from Relative Referencing

To eliminate the divide-by-zero error shown in Figure 5 we must add an absolute reference to cell D12 in the formula. An absolute reference prevents relative referencing from changing a cell reference in a formula. This is also referred to as locking a cell. The following explains how this is accomplished:

1. Double-click cell B3
   
   
2. Place the mouse pointer in front of D12 and click. The blinking cursor should be in front of the D in the cell reference D12
   
   
3. Press the F4 key. You will see a dollar sign ($) added in front of the column letter D and row number 12. You can also type the dollar signs before the column letter and row number.
   
   
4. Press the ENTER key
   
   
5. Click cell B3
   
   
6. Click the Copy button in the Home tab of the Ribbon
   
   
7. Highlight the range B4:B11
   
   
8. Click the Paste button in the Home tab of the Ribbon

Figure 6 shows the percent of the total formula with an absolute reference added to D12. Notice that in cell B4, the cell reference remains D12 instead of changing to D13, as shown in Figure 5. Also, you will see that the percentages are calculated in the rest of the cells in the column, and the divide-by-zero error is now eliminated.

Figure 6 Adding an Absolute Reference to a Cell Reference in a Formula

The Count Function

Data file: Continue with CH2 Personal Budget.

The next function we will add to the Budget Detail worksheet is the COUNT function. The COUNT function determines how many cells in a range contain a numeric entry. The COUNT function will not work for counting text or other non-numeric entries. For the Budget Detail worksheet, we will use the COUNT function to count the number of items planned in the Annual Spend column (Column D). The following explains how the COUNT function is added to the worksheet by using the function list:

1. Click cell D13 in the Budget Detail worksheet
   
   
2. Type an equal sign: =
   
   
3. Type the letter C
   
   
4. Click the down arrow on the scroll bar of the function list (see Figure 2.17) and find the word COUNT
   
   
5. Double-click the word COUNT from the function list
   
   
6. Highlight the range D3:D11
   
   
7. You can type a closing parenthesis ) and press the ENTER key, or simply press the ENTER key, and Excel will close the function. The function produces an output of 9 since there are 9 items planned on the worksheet.
   
   

Figure 7 shows the function list box that appears after completing steps 2 and 3 for the COUNT function. The function list provides an alternative method for adding a function to a worksheet.

Figure 7 Using the Function List to Add the COUNT Function

Figure 8 shows the output of the COUNT function after pressing the ENTER key. The function counts the number of cells in the range D3:D11 that contain a numeric value. The result of 9 indicates that there are 9 categories planned for this budget.

Figure 8 Completed COUNT Function in the Budget Detail Worksheet

The Average Function

The next function we will add to the Budget Detail worksheet is the AVERAGE function. This function calculates the arithmetic mean for a group of numbers. For the Budget Detail worksheet, we will use the function to calculate the average values in the Annual Spend column. We will add this to the worksheet by using the Function Library. The following steps explain how this is accomplished:

1. Click cell D14 in the Budget Detail worksheet
   
   
2. Click the Formulas tab on the Ribbon
   
   
3. Click the More Functions button in the Function Library group of commands
   
   
4. Place the mouse pointer over the Statistical option from the drop-down list of options
   
   
5. Click the AVERAGE function name from the list of functions that appear in the menu (see Figure 2.19). This opens the Function Arguments dialog box.
   
   
6. Click the Collapse Dialog button in the Function Arguments dialog box (see Figure 2.20)
   
   
7. Highlight the range D3:D11
   
   
8. Click the Expand Dialog button in the Function Arguments dialog box (see Figure 2.21); you can also press the ENTER key to get the same result
   
   
9. Click the OK button on the Function Arguments dialog box; this adds the AVERAGE function to the worksheet
   
   

Figure 9 illustrates how a function is selected from the Function Library in the Formulas tab of the Ribbon.

Figure 9 Selecting the AVERAGE Function from the Function Library

Figure 10 shows the Function Arguments dialog box. This appears after a function is selected from the Function Library. The Collapse Dialog button is used to hide the dialog box so a range of cells can be highlighted on the worksheet and then added to the function.

Figure 10 Function Arguments Dialog Box

Figure 11 shows how a range of cells can be selected from the Function Arguments dialog box once it has been collapsed.

Figure 11 Selecting a Range from the Function Arguments Dialog Box

The MIN and MAX Functions

Data file: Continue with CH2 Personal Budget.

The final two statistical functions that we will add to the Budget Detail worksheet are the MAX and MIN functions. These functions identify the highest and lowest values in a range of cells. The following steps explain how to add these functions to the Budget Detail worksheet:

1. Click cell D15 in the Budget Detail worksheet
   
   
2. Type an equal sign: =
   
   
3. Type the word MIN
   
   
4. Type an open parenthesis: (
   
   
5. Highlight the range D3:D11
   
   
6. Type a closing parenthesis ) and press the ENTER key, or simply press the ENTER key and Excel will close the function for you; the MIN function produces an output of $1,200, which is the lowest value in the Annual Spend column (see Figure 14)
   
   
7. Click cell D16
   
   
8. Type an equal sign: =
   
   
9. Type the word MAX
   
   
10. Type an open parenthesis: (
    
    
11. Highlight the range D3:D11
    
    
12. Type a closing parenthesis ) and press the ENTER key, or simply press the ENTER key and Excel will close the function for you. The MAX function produces an output of $3,500. This is the highest value in the Annual Spend column (see Figure 15).

Figure 14 MIN Function Added to the Budget Detail Worksheet

Figure 15 MAX Function Added to the Budget Detail Worksheet

Copy and Paste Formulas (Pasting without Formats)

Data file: Continue with CH2 Personal Budget.

As shown in Figure 15, the COUNT, AVERAGE, MIN, and MAX functions summarize the data in the Annual Spend column. You will also notice that there is space to copy and paste these functions under the LY Spend column. This allows us to compare what we spent last year and what we plan to spend this year. Normally, we would simply copy and paste these functions into the range E13:E16.

However, you may have noticed the double-line style border used around the perimeter of the range B13:E16. If we used the regular Paste command, the double line on the right side of the range E13:E16 would be replaced with a single line. Therefore, we will use one of the Paste Special commands to paste only the functions without any formatting treatments. This is accomplished through the following steps:

1. Highlight the range D13:D16 in the Budget Detail worksheet
   
   
2. Click the Copy button in the Home tab of the Ribbon
   
   
3. Click cell E13
   
   
4. Click the down arrow below the Paste button in the Home tab of the Ribbon
   
   
5. Click the Formulas option from the drop-down list of buttons (see Figure 16)

Figure 16 shows the list of buttons that appear when you click the down arrow below the Paste button in the Home tab of the Ribbon. One thing to note about these options is that you can preview them before selecting them by dragging the mouse pointer over the options. As shown in the figure, when the mouse pointer is placed over the Formulas button, you can see how the functions appear before selecting. Notice that the double-line border does not change when this option is previewed. That is why this selection is made instead of the regular Paste option.

Figure 16 Paste Formulas Option

Sorting Data (Multiple Levels)

Data file: Continue with CH2 Personal Budget.

The Budget Detail worksheet shown in Figure 16 now produces several mathematical outputs through formulas and functions. The outputs allow you to analyze the details and identify trends regarding budgeting and spending. Before drawing conclusions from this worksheet, we will sort the data based on the Percent of Total column. Sorting is a powerful tool that enables you to analyze key trends in any data set. Sorting will be covered thoroughly in a later chapter but is briefly introduced here. For the Budget Detail worksheet, we want to set multiple levels for the sort order. This is accomplished through the following steps:

1. Highlight the range A2:F11 in the Budget Detail worksheet
   
   
2. Click the Data tab in the Ribbon
   
   
3. Click the Sort button in the Sort & Filter group of commands; this opens the Sort dialog box, as shown in Figure 17
   
   
4. Click the down arrow next to the "Sort by" box
   
   
5. Click the Percent of Total option from the drop-down list
   
   
6. Click the down arrow next to the sort Order box
   
   
7. Click the Largest to Smallest option
   
   
8. Click the Add Level button; this allows you to set a second level for any duplicate values in the Percent of Total column
   
   
9. Click the down arrow next to the "Then by" box
   
   
10. Select the LY Spend option. Leave the Sort Order as Smallest to Largest
    
    
11. Click the OK button at the bottom of the Sort dialog box
    
    
12. Save the Ch2 Personal Budget file
    
    

Figure 17 Sort Dialog Box

Figure 18 shows the Budget Detail worksheet after it has been sorted. Notice that there are three identical values in the Percent of Total column. This is why a second sort level had to be created for this worksheet. The second sort level arranges the values of 8.4% based on the values in the LY Spend column in ascending order. Excel allows you to set as many sort levels as necessary for the data in a worksheet.

Figure 18 Budget Detail Worksheet after Sorting

Now that the Budget Detail worksheet is sorted, a few key trends can be easily identified. The worksheet clearly shows that the top three categories as a percentage of total budgeted spending for the year are Taxes, Household Utilities, and Food. All three categories are necessities (or realities) of life and typically require a significant amount of income for most households.

Looking at the Percent Change column, we can see how our planned spending is expected to change from last year. This is perhaps the most important column on the worksheet because it allows you to assess whether your plan is realistic. You will see that there are no changes planned for Taxes and Household Utilities.

While Taxes can change from year to year, it is not too difficult to predict what they will be. In this case, we assume that there are no changes to the tax costs for our budget. We are also planning no change in Household Utilities. These costs can fluctuate from year to year as well. However, you can reduce costs, such as using less electricity, turning off heat when no one is in the house, keeping track of your wireless minutes so you do not exceed the maximum allowed in your plan, and so on. As a result, there is no change in planned spending for Household Utilities because we will assume that any rate increases will be offset by a decrease in usage.

The third item that is not planned to change is insurance. Insurance policies for cars and homes can change, but as is true for taxes, the changes are predictable. Therefore, we are assuming no changes in our insurance policy.

The first big change noticeable in the worksheet is the Food and Entertainment categories in rows 5 and 6 (see definitions in Table 1). The Percent Change column indicates an 11.1% decrease in Entertainment spending and an 11.1% increase in Food spending. This is logical because if you plan to eat in restaurants less frequently, you will be eating at home more frequently. Although this makes sense in theory, it may be hard to do in practice. Dinners and parties with friends may be tough to turn down. However, the entire process of maintaining a budget is based on discipline, and it certainly takes a significant amount of discipline to plan targets for yourself and stick to them.

A few other points to note are the changes in the Gasoline and Vacation categories. If you commute to school or work, the gas price can significantly impact your budget. It is important to be realistic if gas prices are increasing, and you should reflect these increases in your budget. To compensate for the increased spending on gas, the spending plan for vacations has been reduced by 25%. Budgeting often requires a certain degree of creativity. Although the Vacation budget has been reduced, there is still money you can set aside to make plans for spring break or winter break.

Finally, the budget shows a 19.8% decrease in Miscellaneous spending. This was defined as a group containing several expenses, such as textbooks, school supplies, software updates, and so on (see Table 1). You may be able to reduce your spending in this category if you can use items such as online textbooks. This reduction in spending can free up funds for Clothes, a spending category that has increased by 20%.