{"id":100,"date":"2016-10-21T00:00:22","date_gmt":"2016-10-21T00:00:22","guid":{"rendered":"https:\/\/pressbooks.usnh.edu\/math502\/chapter\/statistical-functions\/"},"modified":"2023-03-24T14:25:55","modified_gmt":"2023-03-24T14:25:55","slug":"statistical-functions","status":"publish","type":"chapter","link":"https:\/\/pressbooks.usnh.edu\/mth402\/chapter\/statistical-functions\/","title":{"raw":"Statistical Functions","rendered":"Statistical Functions"},"content":{"raw":"
\n
\n

Learning Objectives<\/h3>\n
    \n \t
  1. Use the\u00a0SUM function\u00a0to calculate totals.<\/li>\n \t
  2. Use\u00a0absolute references\u00a0to calculate percent of totals.<\/li>\n \t
  3. Use the\u00a0COUNT function\u00a0to count cell locations with numerical values.<\/li>\n \t
  4. Use the\u00a0AVERAGE function\u00a0to calculate the arithmetic mean.<\/li>\n \t
  5. Use the\u00a0MAX and MIN functions\u00a0to find the highest and lowest values in a range of cells.<\/li>\n<\/ol>\n<\/div>\nIn addition to formulas, another way to conduct mathematical computations in Excel is through functions. Statistical 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. A list of commonly used statistical functions is shown in Table 2.4<\/strong>. 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. 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 percent of total calculations and the use of absolute references.\n\nTable 2.4\u00a0Commonly Used Statistical Functions\n<\/colgroup>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    Function<\/td>\nOutput<\/td>\n<\/tr>\n<\/thead>\n
    ABS<\/strong><\/td>\nThe absolute value of a number<\/td>\n<\/tr>\n
    AVERAGE<\/strong><\/td>\nThe average or arithmetic mean for a group of numbers<\/td>\n<\/tr>\n
    COUNT<\/strong><\/td>\nThe number of cell locations in a range that contain a numeric character<\/td>\n<\/tr>\n
    COUNTA<\/strong><\/td>\nThe number of cell locations in a range that contain a text or numeric character<\/td>\n<\/tr>\n
    MAX<\/strong><\/td>\nThe highest numeric value in a group of numbers<\/td>\n<\/tr>\n
    MEDIAN<\/strong><\/td>\nThe 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)<\/td>\n<\/tr>\n
    MIN<\/strong><\/td>\nThe lowest numeric value in a group of numbers<\/td>\n<\/tr>\n
    MODE<\/strong><\/td>\nThe number that appears most frequently in a group of numbers<\/td>\n<\/tr>\n
    PRODUCT<\/strong><\/td>\nThe result of multiplying all the values in a range of cell locations<\/td>\n<\/tr>\n
    SQRT<\/strong><\/td>\nThe positive square root of a number<\/td>\n<\/tr>\n
    STDEV.S<\/strong><\/td>\nThe standard deviation for a group of numbers based on a sample<\/td>\n<\/tr>\n
    SUM<\/strong><\/td>\nThe total of all numeric values in a group<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    The SUM Function<\/h2>\nThe 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\u00a0Budget Detail<\/strong> 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:\n
      \n \t
    1. Click the\u00a0Budget Detail\u00a0<\/strong>worksheet tab to open the worksheet.<\/li>\n \t
    2. Click cell C12.<\/li>\n \t
    3. Type an equal sign =<\/strong>.<\/li>\n \t
    4. Type the function name\u00a0SUM<\/strong>.<\/li>\n \t
    5. Type an open parenthesis (<\/strong>.<\/li>\n \t
    6. Click cell C3 and drag down to cell C11. This places the range C3:C11 into the function.<\/li>\n \t
    7. Type a closing parenthesis )<\/strong>.<\/li>\n \t
    8. Press the ENTER key. The function calculates the total for the Monthly Spend column, which is $1,496.<\/li>\n<\/ol>\nFigure 2.41<\/strong> shows the appearance of the SUM function added to the\u00a0Budget Detail<\/strong>\u00a0worksheet before pressing the ENTER key.\n\n[caption id=\"\" align=\"aligncenter\" width=\"1320\"]\"Budget<\/a> Figure 2.41 Adding the SUM Function to the Budget Detail Worksheet[\/caption]\n\nAs shown in Figure 2.11<\/strong>, 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:\n
        \n \t
      1. Click cell C12 in the\u00a0Budget Detail<\/strong>\u00a0worksheet.<\/li>\n \t
      2. Click the Copy button in the Home tab of the Ribbon.<\/li>\n \t
      3. Highlight cells D12 and E12.<\/li>\n \t
      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.<\/li>\n \t
      5. Click cell F11.<\/li>\n \t
      6. Click the Copy button in the Home tab of the Ribbon.<\/li>\n \t
      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.<\/li>\n<\/ol>\nFigure 2.42<\/strong> 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.\n\n[caption id=\"\" align=\"aligncenter\" width=\"1319\"]\"Totals<\/a> Figure 2.42 Results of the SUM Function in the Budget Detail Worksheet[\/caption]\n\n
        \n

        Integrity Check<\/h3>\nCell Ranges in Statistical Functions<\/strong>\n\nWhen 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 2.13<\/strong> will add only the values in cells C3 and C11, not the range C3:C11.\n\n<\/div>\n\n[caption id=\"\" align=\"aligncenter\" width=\"715\"]\"A<\/a> Figure 2.42 SUM Function Adding Two Cell Locations[\/caption]\n

        Absolute References (Calculating Percent of Totals)<\/h2>\nData file: Continue with CH2 Personal Budget.<\/em>\n\nSince totals were added to row 12 of the\u00a0Budget Detail<\/strong>\u00a0worksheet, a percent of 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\u2019s relative referencing feature before copying and pasting the formula to the rest of the cell locations in the column. Turning off Excel\u2019s relative referencing feature is accomplished through an absolute reference. The following steps explain how this is done:\n
          \n \t
        1. Click cell B3 in the\u00a0Budget Detail<\/strong>\u00a0worksheet.<\/li>\n \t
        2. Type an equal sign =<\/strong>.<\/li>\n \t
        3. Click cell D3.<\/li>\n \t
        4. Type a forward slash \/<\/strong>.<\/li>\n \t
        5. Click cell D12.<\/li>\n \t
        6. Press the ENTER key. You will see that Household Utilities represents 16.7% of the Annual Spend budget (see Figure 2.44<\/strong>).<\/li>\n<\/ol>\n[caption id=\"\" align=\"aligncenter\" width=\"1210\"]\"Household<\/a> Figure 2.44 Adding a Formula to Calculate the Percent of Total[\/caption]\n\nFigure 2.44<\/strong> shows the completed formula that is calculating the percentage that Household Utilities Annual Spend represents to the total Annual Spend 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 2.45<\/strong> illustrates what happens if we paste the formula into the range B4:B12 in its current state. Notice that Excel produces the\u00a0#DIV\/0\u00a0error 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.\n\n[caption id=\"\" align=\"aligncenter\" width=\"1210\"]\"Relative<\/a> Figure 2.45 #DIV\/0 Error from Relative Referencing[\/caption]\n\nTo eliminate the divide-by-zero error shown in Figure 2.15<\/strong> we must add an absolute reference to cell D12 in the formula. An\u00a0absolute\u00a0reference\u00a0prevents relative referencing from changing a cell reference in a formula. This is also referred to as\u00a0locking a cell. The following explains how this is accomplished:\n
            \n \t
          1. Double click cell B3.<\/li>\n \t
          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.<\/li>\n \t
          3. Press the F4 key. You will see a dollar sign ($) added in front of the column letter D and the row number 12. You can also type the dollar signs in front of the column letter and row number.<\/li>\n \t
          4. Press the ENTER key.<\/li>\n \t
          5. Click cell B3.<\/li>\n \t
          6. Click the Copy button in the Home tab of the Ribbon.<\/li>\n \t
          7. Highlight the range B4:B11.<\/li>\n \t
          8. Click the Paste button in the Home tab of the Ribbon.<\/li>\n<\/ol>\nFigure 2.46<\/strong> shows the percent of 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 2.15<\/strong>. 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.\n\n[caption id=\"\" align=\"aligncenter\" width=\"1210\"]\"Dollar<\/a> Figure 2.46 Adding an Absolute Reference to a Cell Reference in a Formula[\/caption]\n\n
            \n

            Skill Refresher<\/h3>\nAbsolute References<\/strong>\n
              \n \t
            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.<\/li>\n \t
            2. Press the F4 key or type a dollar sign $ in front of the column letter and row number of the cell reference.<\/li>\n<\/ol>\n<\/div>\n

              \u00a0The COUNT Function<\/h2>\nData file: Continue with CH2 Personal Budget.<\/em>\n\nThe next function that we will add to the\u00a0Budget Detail<\/strong>\u00a0worksheet 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. For the\u00a0Budget Detail<\/strong>\u00a0worksheet, 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 using the function list:\n
                \n \t
              1. Click cell D13 in the\u00a0Budget Detail\u00a0worksheet.<\/li>\n \t
              2. Type an equal sign =<\/strong>.<\/li>\n \t
              3. Type the letter\u00a0C<\/strong>.<\/li>\n \t
              4. Click the down arrow on the scroll bar of the function list (see Figure 2.47<\/strong>) and find the word COUNT.<\/li>\n \t
              5. Double click the word\u00a0COUNT\u00a0from the function list.<\/li>\n \t
              6. Highlight the range D3:D11.<\/li>\n \t
              7. You can type a closing parenthesis )<\/strong> and then press the ENTER key, or simply press the ENTER key and Excel will close the function for you.\u00a0The function produces an output of 9 since there are 9 items planned on the worksheet.<\/li>\n<\/ol>\nFigure 2.47<\/strong> 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.\n\n[caption id=\"\" align=\"aligncenter\" width=\"1320\"]\"Press<\/a> Figure 2.47 Using the Function List to Add the COUNT Function[\/caption]\n\nFigure 2.48<\/strong> 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.\n\n[caption id=\"\" align=\"aligncenter\" width=\"1215\"]\""=COUNT(D3:D11)"<\/a> Figure 2.48 Completed COUNT Function in the Budget Detail Worksheet[\/caption]\n

                The AVERAGE Function<\/h2>\nThe next function we will add to the\u00a0Budget Detail<\/strong>\u00a0worksheet is the AVERAGE function. This function is used to calculate the arithmetic mean<\/strong><\/em> for a group of numbers. For the\u00a0Budget Detail\u00a0<\/strong>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. The following steps explain how this is accomplished:\n
                  \n \t
                1. Click cell D14 in the\u00a0Budget Detail<\/strong>\u00a0worksheet.<\/li>\n \t
                2. Click the Formulas tab on the Ribbon.<\/li>\n \t
                3. Click the More Functions button in the Function Library group of commands.<\/li>\n \t
                4. Place the mouse pointer over the Statistical option from the drop-down list of options.<\/li>\n \t
                5. Click the AVERAGE function name from the list of functions that appear in the menu (see Figure 2.49<\/strong>). This opens the Function Arguments dialog box.<\/li>\n \t
                6. Click the Collapse Dialog button in the Function Arguments dialog box (see Figure 2.410<\/strong>).<\/li>\n \t
                7. Highlight the range D3:D11.<\/li>\n \t
                8. Click the Expand Dialog button in the Function Arguments dialog box (see Figure 2.411<\/strong>). You can also press the ENTER key to get the same result.<\/li>\n \t
                9. Click the OK button on the Function Arguments dialog box. This adds the AVERAGE function to the worksheet.<\/li>\n<\/ol>\nFigure 2.49<\/strong> illustrates how a function is selected from the Function Library in the Formulas tab of the Ribbon.\n\n[caption id=\"\" align=\"aligncenter\" width=\"1320\"]\"Press<\/a> Figure 2.19\u00a0Selecting the AVERAGE Function from the Function Library[\/caption]\n\nFigure 2.410<\/strong>\u00a0shows 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.\n\n[caption id=\"\" align=\"aligncenter\" width=\"1320\"]\"Function<\/a> Figure 2.410 Function Arguments Dialog Box[\/caption]\n\nFigure 2.411<\/strong> shows how a range of cells can be selected from the Function Arguments dialog box once it has been collapsed.\n\n[caption id=\"\" align=\"aligncenter\" width=\"1319\"]\"When<\/a> Figure 2.411 Selecting a Range from the Function Arguments Dialog Box[\/caption]\n\nFigure 2.412<\/strong> 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.\n\n[caption id=\"\" align=\"aligncenter\" width=\"1319\"]\"Function<\/a> Figure 2.412 Function Arguments Dialog Box after a Cell Range Is Defined for a Function[\/caption]\n\nFigure 2.413<\/strong> shows the completed AVERAGE function in the\u00a0Budget Detail<\/strong>\u00a0worksheet. The output of the function shows that on average we expect to spend $1,994 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.\n\n[caption id=\"\" align=\"aligncenter\" width=\"1319\"]\"The<\/a> Figure 2.413 Completed AVERAGE Function[\/caption]\n

                  The MAX and MIN Functions<\/h2>\nData file: Continue with CH2 Personal Budget.<\/em>\n\nThe final two statistical functions that we will add to the\u00a0Budget Detail<\/strong> worksheet are the MAX<\/strong> and MIN<\/strong> 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\u00a0Budget Detail<\/strong>\u00a0worksheet:\n
                    \n \t
                  1. Click cell D15 in the\u00a0Budget Det<\/strong>ail\u00a0worksheet.<\/li>\n \t
                  2. Type an equal sign =<\/strong>.<\/li>\n \t
                  3. Type the word\u00a0MIN<\/strong>.<\/li>\n \t
                  4. Type an open parenthesis (<\/strong>.<\/li>\n \t
                  5. Highlight the range D3:D11.<\/li>\n \t
                  6. Type a closing parenthesis ) <\/strong>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 2.414<\/strong>).<\/li>\n \t
                  7. Click cell D16.<\/li>\n \t
                  8. Type an equal sign =<\/strong>.<\/li>\n \t
                  9. Type the word\u00a0MAX<\/strong>.<\/li>\n \t
                  10. Type an open parenthesis (<\/strong>.<\/li>\n \t
                  11. Highlight the range D3:D11.<\/li>\n \t
                  12. Type a closing parenthesis ) <\/strong>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 2.415<\/strong>).<\/li>\n<\/ol>\n[caption id=\"\" align=\"aligncenter\" width=\"1320\"]\"The<\/a> Figure 2.414 MIN Function Added to the Budget Detail Worksheet[\/caption]\n\n[caption id=\"\" align=\"aligncenter\" width=\"1320\"]\"The<\/a> Figure 2.415 MAX Function Added to the Budget Detail Worksheet[\/caption]\n\n
                    \n

                    Skill Refresher<\/h3>\nStatistical Functions<\/strong>\n
                      \n \t
                    1. Type an equal sign =<\/strong>.<\/li>\n \t
                    2. Type the function name followed by an open parenthesis (<\/strong>\u00a0or double click the function name from the function list.<\/li>\n \t
                    3. Highlight a range on a worksheet or click individual cell locations followed by commas.<\/li>\n \t
                    4. Type a closing parenthesis )<\/strong> and press the ENTER key or press the ENTER key to close the function.<\/li>\n<\/ol>\n<\/div>\n
                      \n

                      Key Takeaways<\/h3>\n