This is “Statistical Functions”, section 2.2 from the book Using Microsoft Excel (v. 1.1). For details on it (including licensing), click here.
For more information on the source of this book, or why it is available for free, please see the project's home page. You can browse or download additional books there. To download a .zip file containing this book to use offline, simply click here.
In 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 "Commonly Used Statistical Functions". 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.
Table 2.4 Commonly Used Statistical 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 character |
COUNTA | The number of cell locations in a range that contain a text or numeric character |
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 |
STDEV.S | The standard deviation for a group of numbers based on a sample |
SUM | The total of all numeric values in a group |
Follow-along file: Continue with Excel Objective 2.00. (Use file Excel Objective 2.03 if starting here.)
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:
Figure 2.11 "Adding the SUM Function to the Budget Detail Worksheet" shows the appearance of the SUM function added to the Budget Detail worksheet before pressing the ENTER key.
Figure 2.11 Adding the SUM Function to the Budget Detail Worksheet
As shown in Figure 2.11 "Adding the SUM Function to the Budget Detail Worksheet", 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:
Figure 2.12 "Results of the SUM Function in the Budget Detail Worksheet" 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.
Figure 2.12 Results of the SUM Function in the Budget Detail Worksheet
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 2.13 "SUM Function Adding Two Cell Locations" will add only the values in cells C3 and C11, not the range C3:C11.
Figure 2.13 SUM Function Adding Two Cell Locations
Follow-along file: Continue with Excel Objective 2.00. (Use file Excel Objective 2.04 if starting here.)
Since totals were added to row 12 of the Budget Detail worksheet, 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’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:
Figure 2.14 Adding a Formula to Calculate the Percent of Total
Figure 2.14 "Adding a Formula to Calculate the Percent of Total" 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.15 "#DIV/0 Error from Relative Referencing" illustrates what happens if we paste the formula into the range B4:B12 in its current state. Notice that Excel produces the #DIV/0Error code displayed in a cell containing a formula or function that is trying to divide by zero. 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 2.15 #DIV/0 Error from Relative Referencing
To eliminate the divide-by-zero error shown in Figure 2.15 "#DIV/0 Error from Relative Referencing", we must add an absolute reference to cell D12 in the formula. An absolute referencePrevents Excel from using relative referencing to change a cell reference used in a formula or function; an absolute reference is applied to a cell reference by typing a dollar sign ($) in front of the column letter and row number. 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:
Figure 2.16 "Adding an Absolute Reference to a Cell Reference in a Formula" 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 "#DIV/0 Error from Relative Referencing". 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.
Figure 2.16 Adding an Absolute Reference to a Cell Reference in a Formula
Follow-along file: Continue with Excel Objective 2.00. (Use file Excel Objective 2.05 if starting here.)
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. 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 using the function list:
Figure 2.17 "Using the Function List to Add the COUNT Function" 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 2.17 Using the Function List to Add the COUNT Function
Figure 2.18 "Completed COUNT Function in the Budget Detail Worksheet" 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 2.18 Completed COUNT Function in the Budget Detail Worksheet
Follow-along file: Continue with Excel Objective 2.00. (Use file Excel Objective 2.06 if starting here.)
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. The following steps explain how this is accomplished:
Figure 2.19 "Selecting the AVERAGE Function from the Function Library" illustrates how a function is selected from the Function Library in the Formulas tab of the Ribbon.
Figure 2.19 Selecting the AVERAGE Function from the Function Library
Figure 2.20 "Function Arguments Dialog Box" 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 2.20 Function Arguments Dialog Box
Figure 2.21 "Selecting a Range from the Function Arguments Dialog Box" shows how a range of cells can be selected from the Function Arguments dialog box once it has been collapsed.
Figure 2.21 Selecting a Range from the Function Arguments Dialog Box
Figure 2.22 "Function Arguments Dialog Box after a Cell Range Is Defined for a Function" 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.
Figure 2.22 Function Arguments Dialog Box after a Cell Range Is Defined for a Function
Figure 2.23 "Completed AVERAGE Function" shows the completed AVERAGE function in the Budget Detail worksheet. 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.
Figure 2.23 Completed AVERAGE Function
Follow-along file: Continue with Excel Objective 2.00. (Use file Excel Objective 2.07 if starting here.)
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:
Figure 2.24 MIN Function Added to the Budget Detail Worksheet
Figure 2.25 MAX Function Added to the Budget Detail Worksheet
Follow-along file: Continue with Excel Objective 2.00. (Use file Excel Objective 2.08 if starting here.)
As shown in Figure 2.25 "MAX Function Added to the Budget Detail Worksheet", 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 LY 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 E13:E16. However, you may have noticed the double-line style border that was 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 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:
Figure 2.26 "Paste Formulas Option" 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. As shown in the figure, 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 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 2.26 Paste Formulas Option
Follow-along file: Continue with Excel Objective 2.00. (Use file Excel Objective 2.09 if starting here.)
The Budget Detail worksheet shown in Figure 2.26 "Paste Formulas Option" 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. As mentioned in Chapter 1 "Fundamental Skills", sorting is a powerful tool that enables you to analyze key trends in any data set. We demonstrated the process of executing a single-level sort in Chapter 1 "Fundamental Skills". For the purposes of the Budget Detail worksheet, we need to set multiple levels for the sort order. This is accomplished through the following steps:
Click the Sort button in the Sort & Filter group of commands. This opens the Sort dialog box, as shown in Figure 2.27 "Sort Dialog Box".
Figure 2.27 Sort Dialog Box
Figure 2.28 "Budget Detail Worksheet after Sorting" 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 gives you the option to set as many sort levels as necessary for the data contained in a worksheet.
Figure 2.28 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 import 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 are assuming 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 take measures to 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 go over 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 with a decrease in usage. The third item that is planned not 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 that is noticeable in the worksheet is the Food and Entertainment categories in rows 5 and 6 (see definitions in Table 2.1 "Spend Category Definitions"). The Percent Change column indicates that there is 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 price of gas can have a significant impact on 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 for 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 decrease in Miscellaneous spending of 19.8%. This was defined as a group containing several expenses, such as textbooks, school supplies, software updates, and so on (see Table 2.1 "Spend Category Definitions"). 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 spend category that has increased by 20%. We will continue to develop the Personal Budget workbook further in Section 2.3 "Functions for Personal Finance".
In the formula =C2/$C$24, the dollar signs used in the C24 cell reference indicate:
Which statement best explains how the following function will produce a result =AVERAGE(B1,B10)?
Which of the following best explains the purpose of the Paste Formulas command?
Which of the following explains how data in a worksheet can be sorted if the primary column of data contains several duplicate values?