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.

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 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)

Household Utilities represents 16.7% of total Annual Spend from cell D12 when formula "=D3/D12" is entered in cell B3 and "enter" is pressed in D12.

Figure 4 Adding a Formula to Calculate the Percent of Total


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

Relative Referencing changed cell reference to D13 which is blank, causing divide by zero code "#DIV/0!" to appear repeatedly in Column B.

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 the row number 12. You can also type the dollar signs in front of 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 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 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 this cell changing D13 or D12, allowing calculations in remaining cells of column, eliminating the divide-by-zero error indicator.

Figure 6 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