Skip to main content

2.2 Introductory Statistical Functions

Learning Objectives

  1. Use the SUM function to calculate totals.
  2. Use the COUNT function to count cell locations with numerical values.
  3. Use the AVERAGE function to calculate the arithmetic mean.
  4. Use the MAX and MIN functions to find the highest and lowest values in a range of cells.
  5. Learn how to copy and paste formulas without formats applied to a cell location.
  6. Use absolute references to calculate percent of totals.
  7. Learn how to set a multiple level sort sequence for data sets that have duplicate values or outputs.

In addition to formulas, another way to conduct mathematical computations in Excel is through functions. Excel functions apply a mathematical process to a group of cells in a worksheet. For example, the SUM function is used to add the values contained in a range of cells. Functions are more efficient than formulas when you are applying a mathematical process to a group of cells. If you use a formula to add the values in a range of cells, you would have to add each cell location to the formula one at a time. This can be very time-consuming if you have to add the values in a few hundred cell locations. However, when you use a function, you can highlight all the cells that contain values you wish to sum in just one step.

The components of a function are as follows:

=FunctionName(Arguments)

Functions are a type of formula, therefore they start with an equal sign.  The next component is the name of the function. A list of commonly used functions is shown in Table 2.4.  After the function name comes the arguments for the function, which are always enclosed in parentheses.  The arguments are the cell locations and/or values that will be used in the function.  The number and type of arguments varies based on the the function being used, although in this section we will only work with a range of cells for the function arguments.   Some examples of different functions with their arguments are:

=SUM(B2:B15) – adds the values in B2 through B15
=SQRT(A5) – finds the square root of the value in A5
=COUNTA(A1:A20) – finds the number of cells from A1 through A20 that contain text or a number

Throughout Section 2.2 we will add a variety of mathematical functions to the Personal Budget workbook. In addition to creating functions, this section also reviews percent of total calculations and the use of absolute references.

Table 2.4 Commonly Used Functions

Function Output
ABS The absolute value of a number
AVERAGE The average or arithmetic mean for a group of numbers
COUNT The number of cell locations in a range that contain a numeric value
COUNTA The number of cell locations in a range that contain text or a numeric value
MAX The highest numeric value in a group of numbers
MEDIAN The middle number in a group of numbers (half the numbers in the group are higher than the median and half the numbers in the group are lower than the median)
MIN The lowest numeric value in a group of numbers
MODE The number that appears most frequently in a group of numbers
PRODUCT The result of multiplying all the values in a range of cell locations
SQRT The positive square root of a number
SUM The total of all numeric values in a group

It is important to note that there are several methods for adding a function to a worksheet, and we will explore each of them throughout this section.

  • Typing the function directly into a cell
  • Selecting from the function list
  • Using the Function Library on the ribbon
  • Using the Insert Function button

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, starting with the Monthly Spend total in B12. The following illustrates how a function can be added to a worksheet by typing it into a cell location:

  1. Switch to the Budget Detail worksheet if needed.
  2. Click cell B12.
  3. Type an equal sign =.
  4. Type the function name SUM.
  5. Type an open parenthesis (.
  6. Click cell B3 and drag down to cell B11. This places the range B3:B11 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,427.

Figure 2.11 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 before "Enter" is pressed.
Figure 2.11 Adding the SUM Function to the Budget Detail Worksheet

As shown in Figure 2.11, the SUM function was added to cell B12. However, this function is also needed to calculate the totals in the Annual Spend and Last Year 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.  To complete the Totals in row 12, we need to copy and paste the SUM function into D12 and E12.  Since we will then have totals in D12 and E12, we can paste the percent change formula into F12.

  1. Click cell B12 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.

Figure 2.12 shows the output of the SUM function that was added to cells B12, 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 an increase in spending compared to last year.

Totals were added to cells B12, D12, and E12 and percent change formula was pasted in cell F12 indicating spending will increase by 7.3% compared to last year.
Figure 2.12 Results of the SUM Function in the Budget Detail worksheet
Integrity Check

Cell Ranges in Functions

When you intend to use a 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 calculate only the two cell locations listed instead of an entire range of cells. For example, the SUM function shown in Figure 2.13 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 2.13 SUM Function Adding Two Cell Locations

The Count Function

Data file: Continue with CH2 Personal Budget.

The next function that we will add to the Budget Detail worksheet is the COUNT function. The COUNT function is used to determine how many cells in a range contain a numeric entry. The COUNT function will not work for counting text or other non-numeric entries. If you want to count text instead of, or in addition to, numeric entries you use the COUNTA function. For the Budget Detail worksheet, we will use the COUNT function to count the number of items that are planned in the Annual Spend column (Column D). The following explains how the COUNT function is added to the worksheet by selecting from the function list:

  1. Click cell D13.
  2. Type an equal sign =.
  3. Type the letter C (to start spelling the name of the function).
  4. Click the down arrow on the scroll bar of the function list (see Figure 2.14) and find the word COUNT.
    Excel for Mac icon Mac Users can scroll down with touchpad or mouse to find COUNT
  5. Double click the word COUNT from the function list.
    Excel for Mac icon Mac Users should single click the word “COUNT” do not double-click
  6. Highlight the range D3:D11.
  7. You can type a closing parenthesis ) and then press the ENTER key, or simply press the ENTER key and Excel will close the function for you. The function produces an output of 9 since there are 9 items planned on the worksheet.

Figure 2.14 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.

Press Shift + F3 to open the function search/picker. Search for a function, or use up/down arrows to scroll through function list.
Figure 2.14 Using the Function List to Add the COUNT Function

Figure 2.15 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.

"=COUNT(D3:D11)" appears in formula bar, and output "9" appears in cell D13.
Figure 2.15 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 is used to calculate the arithmetic mean for a group of numbers. For the Budget Detail worksheet, we will use the function to calculate the average of the values in the Annual Spend column. We will add this to the worksheet by using the Function Library on the  Formulas ribbon. 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.16). This opens the Function Arguments dialog box.
  6. Click the Collapse Dialog button in the Function Arguments dialog box (see Figure 2.17).
    Excel for Mac icon For Mac Users, the Collapse Dialog button may not collapse. Just continue with Step 7 and press Enter after selecting the range.
  7. Highlight the range D3:D11.
  8. Click the Expand Dialog button in the Function Arguments dialog box (see Figure 2.18). 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.
    Excel for Mac icon Mac Users should click the DONE button

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

Press F6 until ribbon pane is activated, then M to select Formulas, then Q to select More Functions. Press S to select Statistical menu item, then scroll down to select the Average function.
Figure 2.16 Selecting the AVERAGE function from the Function Library

Figure 2.17 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.

Function Arguments dialog box open to Average function, with function definition, and Collapse Dialog button.
Figure 2.17 Function Arguments Dialog Box

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

When Function Arguments dialog box is collapsed, cell range can be highlighted with function appearing in cell as it is being built.
Figure 2.18 Selecting a range from the Function Arguments Dialog Box

Figure 2.19 shows the Function Arguments dialog box after the cell range is defined for the AVERAGE function. The dialog box shows the result of the function before it is added to the cell location. This allows you to assess the function output to determine whether it makes sense before adding it to the worksheet.

Function Arguments dialog box shows first few values next to cell range, and output of function appears both in center of box and at bottom as "formula result".
Figure 2.19 Function Arguments Dialog Box after a Cell Range Is Defined for a Function

Figure 2.20 shows the completed AVERAGE function in the Budget Detail worksheet. The output of the function shows that on average we expect to spend $1,903 for each of the categories listed in Column A of the budget. This average spend calculation per category can be used as an indicator to determine which categories are costing more or less than the average budgeted spend dollars.

The AVERAGE function at top of worksheet as "=AVERAGE(D:3D11)" and output of "$1,903" in cell D14.
Figure 2.20 Completed AVERAGE function

The Max And Min 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 using the Insert Function button:

  1. Click cell D15 in the Budget Detail worksheet.
  2. Click the Insert Function button on the Formulas ribbon. (see Figure 2.21)
  3. This brings up the Insert Function dialog box.  Type the word MIN in the search box and then click the Go button. (see Figure 2.22)
  4. Double-click MIN in the list. This opens the Function Arguments dialog box.
  5. Click the Collapse Dialog button in the Function Arguments dialog box.
  6. Highlight the range D3:D11.
  7. Click the Expand Dialog button in the Function Arguments dialog box.
  8. Click the OK button on the Function Arguments dialog box. This adds the MIN function to the worksheet. (see Figure 2.23)
  9. Click cell D16.
  10. Repeat steps 2-8 (using MAX instead of MIN) to add the MAX function to the worksheet. (see Figure 2.24)

Formulas Ribbon with box around the Insert Function button

Figure 2.21 Insert Function button on the Formulas Ribbon
Insert Function dialog box with MIN typed in the Search box, Go button highlighted, and MIN selected in the function list
Figure 2.22 Insert Function Dialog Box
The MIN function in formula as "=MIN(D3:D11)" and output of "$1,200" in cell D15 for Minimum Spent
2.23 MIN function added to the Budget Detail worksheet
The MAX function in formula as "=MAX(D3:D11)" and output of "$3,600" in cell D16 for Maximum Spent
Figure 2.24 MAX function added to the Budget Detail worksheet

Skill Refresher

Typing a function or selecting from the function list

  1. Type an equal sign =.
  2. Type the function name followed by an open parenthesis ( or double click the function name from the function list.
  3. Highlight the range of cells to use or click individual cell locations followed by commas.
  4. Type a closing parenthesis ) and press the ENTER key or press the ENTER key to close the function.

Inserting a function using the ribbon

  1. On the Formulas ribbon, select the correct category in the Function Library.   Click the desired function in the list.
  2. In the Function Dialog box, click the Collapse Dialog button and highlight the range of cells to use.
  3. Click the Expand Dialog button and then click the OK button in the Function Arguments dialog box.

Inserting (and searching for) a function using the Insert Function button

  1. On the Formulas ribbon, click the Insert Function button and search for the function to use.  Double-click on the desired function in the list.
  2. In the Function Dialog box, click the Collapse Dialog button and highlight the range of cells to use.
  3. Click the Expand Dialog button and then click the OK button in the Function Arguments dialog box.

Copy And Paste Formulas (Pasting Without Formats)

Data file: Continue with CH2 Personal Budget.

As shown in Figure 2.24, the COUNT, AVERAGE, MIN, and MAX functions are summarizing the data in the Annual Spend column. You will also notice that there is space to copy and paste these functions under the Last Year Spend column. This allows us to compare what we spent last year and what we are planning to spend this year. Normally, we would simply copy and paste these functions into the range E14:E16. However, you may have noticed the thicker style border that was used around the perimeter of the range D13:E16. If we used the regular Paste command, the thick line on the right side of the range D13:E16 would be replaced with a single line. Therefore, we are going to use one of the Paste Special commands to paste only the functions without any of the formatting treatments. This is accomplished through the following steps:

  1. Highlight the range D14:D16 in the Budget Detail worksheet.
  2. Click the Copy button in the Home tab of the Ribbon.
  3. Click cell E14.
  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 2.25).

Figure 2.25 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 you make a selection by dragging the mouse pointer over the options. When the mouse pointer is placed over the Formulas button, you can see how the functions will appear before making a selection. Notice that the thick line border does not change when this option is previewed. That is why this selection is made instead of the regular Paste option.

Press Ctrl + Alt + V for Paste Special menu, then F to select Functions, or R to select formulas and number functions.
Figure 2.25 Paste Formulas Option
Skill Refresher

Paste Formulas without formatting

  1. Click a cell location containing a formula or function.
  2. Click the Copy button in the Home tab of the Ribbon.
  3. Click the cell location or cell range where the formula or function will be pasted.
  4. Click the down arrow below the Paste button in the Home tab of the Ribbon.
  5. Click the Formulas button under the Paste group of buttons.

Absolute References (Calculating Percent Of Totals)

Data file: Continue with CH2 Personal Budget.

To further analyze your budget, you want to see what percentage of your total monthly spending is spent in each category. Since totals were added to row 12 of the Budget Detail worksheet, a percent of total calculation can be added to Column C beginning in cell C3. The percent of total calculation shows the percentage for each value in the Monthly Spend column with respect to the total in cell B12. 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.

First we will create the formula, which needs needs to divide the amount in B3 by the total monthly spend in B12.

  1. Click cell C3 in the Budget Detail worksheet.
  2. Type an equal sign =.
  3. Click cell B3.
  4. Type a forward slash /.
  5. Click cell B12.
  6. Press the ENTER key. You will see that Utilities represent about 17.5% of the Monthly Spend budget (see Figure 2.26).
Utilities represents about 17.5% of total Monthly Spend from cell B12 when formula "=B3/B12" is entered in cell C3
Figure 2.26 Adding a Formula to Calculate the Percent of Total

Figure 2.26 shows the completed formula that is calculating the percentage that Utilities represents to the total Monthly Spend for the budget (see cell C3). Normally, we would copy this formula and paste it into the range C4:C11. However, because of relative referencing, both cell references will increase by one row as the formula is pasted into the cells below C3. This is fine for the first cell reference in the formula (C3) but not for the second cell reference (C12).

Figure 2.27 illustrates what happens if we paste the formula into the range C4:C12 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 C4, you see that the first cell reference was changed from B3 to B4. This is fine because we now want to divide the Monthly Spend for Cell Phone (cell B4) by the total Monthly Spend in cell B12. However, Excel has also changed the B12 cell reference to B13. Because cell location B13 does not contain a number, the formula produces the #DIV/0 error code.

Relative Referencing changed cell reference to B13 which contains text, causing divide by zero code "#DIV/0!" to appear repeatedly in Column C.
Figure 2.27 #DIV/0 Error from Relative Referencing

To eliminate the divide-by-zero error shown in Figure 2.27 we must add an absolute reference to cell B12 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. No matter where you copy a formula with an absolute reference, it will always refer back to the locked cell.  An absolute reference is indicated by a $ sign in front of both the column letter and the row number.  For example, $A$15 is an absolute reference to cell A15.

$A$15 is an example of
an absolute reference

We are going to modify the existing formula in C3 to make the reference to cell B12 an absolute reference. The following explains how this is accomplished:

  1. Double click cell C3.
  2. Place the mouse pointer in front of B12 and click. The blinking cursor should be in front of the B in the cell reference B12.
  3. Press the F4 key. You will see a dollar sign ($) added in front of the column letter B and the row number 12. You can also type the dollar signs in front of the column letter and row number if you prefer. The formula should appear as =B3/$B$12. The F4 key is a cool shortcut for adding the dollar signs.
    Excel for Mac icon     Mac Users: If the F4 key does not insert the $ symbols, check the keyboard settings: click black Apple icon black apple iconat top left of the screen, choose “System Preferences”, click the Keyboard icon, make sure the checkbox is checked for the item that says: “Use F1, F2, etc. as standard function keys”.
  4. Press the ENTER key.
  5. Click cell C3.
  6. Use the AutoFill Handle or Copy and Paste to copy the formula from C3 to the range C4:C11.

Figure 2.28 shows the percent of total formula with an absolute reference added to B12. Notice that in cell C4, the cell reference remains B12 instead of changing to B13. Also, you will see that the percentages are being calculated in the rest of the cells in the column, and the divide-by-zero error is now eliminated.

Dollar signs in formula indicate that Absolute Reference was added to the cell reference for B12, allowing calculations in remaining cells of column, eliminating the divide-by-zero error indicator.
Figure 2.28 Adding an Absolute Reference to a Cell Reference in a Formula

Skill Refresher

Absolute References

  1. Click in front of the column letter of a cell reference in a formula or function that you do not want altered when the formula or function is pasted into a new cell location.
  2. Press the F4 key or type a dollar sign $ in front of the column letter and row number of the cell reference.

Sorting Data (Multiple Levels)

Data file: Continue with CH2 Personal Budget.

The Budget Detail worksheet shown in Figure 2.28 is now producing several mathematical outputs through formulas and functions. The outputs allow you to analyze the details and identify trends as to how money is being budgeted and spent. Before we draw some 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 will be briefly introduced here.

For the purposes of the Budget Detail worksheet, we want to set multiple levels for the sort order.  We are going to sort first by the Percent of Total, and then by the Last Year Spend amount.  Excel will first sort the items by the Percent of Total, and any items with the same Percent of Total will then be sorted by Last Year Spend. This is accomplished through the following steps:

  1. Highlight the range A2:F11.
  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 2.29.
  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.
    Excel for Mac icon the + symbol at bottom left corner is the “Add Level” button for Excel for Mac
  9. Click the down arrow next to the “Then by” box.
  10. Select the Last Year 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.
Sort Dialog Box with Add Level Button, down arrow for Sort by, Sort On, and Sort Order Box. Percent of Total selected in Sort by box. Range A2:F11 is highlighted.
2.29 Sort Dialog Box

Figure 2.30 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 7.01% based on the values in the Last Year Spend column in ascending order. Excel gives you the option to set as many sort levels as necessary for the data contained in a worksheet.

Budget Detail worksheet after Sorting showing duplicate values in Column C of "7.01%". Primary sort level based on values in Column C "Percent of Total."
Figure 2.30 Budget Detail Worksheet after Sorting

Skill Refresher

Sorting Data (Multiple Levels)

  1. Highlight a range of cells to be sorted.
  2. Click the Data tab of the Ribbon.
  3. Click the Sort button in the Sort & Filter group.
  4. Select a column from the “Sort by” drop-down list in the Sort dialog box.
  5. Select a sort order from the Order drop-down list in the Sort dialog box.
  6. Click the Add Level button in the Sort dialog box.
  7. Repeat Steps 4 and 5.
  8. Click the OK button on the Sort dialog box.

Key Takeaways

  • Statistical functions are used when a mathematical process is required for a range of cells, such as summing the values in several cell locations. For these computations, functions are preferable to formulas because adding many cell locations one at a time to a formula can be very time-consuming.
  • Statistical functions can be created using cell ranges or selected cell locations separated by commas. Make sure you use a cell range (two cell locations separated by a colon) when applying a statistical function to a contiguous range of cells.
  • To prevent Excel from changing the cell references in a formula or function when they are pasted to a new cell location, you must use an absolute reference. You can do this by placing a dollar sign ($) in front of the column letter and row number of a cell reference.
  • The #DIV/0 error appears if you create a formula that attempts to divide a constant or the value in a cell reference by zero.
  • The Paste Formulas option is used when you need to paste formulas without any formatting treatments into cell locations that have already been formatted.
  • You need to set multiple levels, or columns, in the Sort dialog box when sorting data that contains several duplicate values.
Attribution

Adapted by Barbara Lave from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.

LICENSE

Icon for the Creative Commons Attribution 4.0 International License

Beginning Excel 2019 by Noreen Brown; Barbara Lave; Hallie Puncochar; Julie Romey; Mary Schatz; Art Schneider; and Diane Shingledecker is licensed under a Creative Commons Attribution 4.0 International License, except where otherwise noted.


No Comments
Back to top