7.Click cell F12, then click the Paste button in the Home tab of theRibbon. Since we now have totals in row 12, we can paste thepercent change formula into this row.Figure 2.12shows the output of the Sum function that was added to cellsC12, D12, and E12. In addition, the percent change formula was copied andpasted 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 WorksheetIntegrity CheckCell Ranges in Statistical FunctionsWhen you intend to use a statistical function on a range of cells in aworksheet, make sure there are two cell locations separated by a colon andnot a comma. If you enter two cell locations separated by a comma, thefunction will produce an output but it will be applied to only two cell locationsinstead of a range of cells. For example, the SUM function shown inFigure2.13will add only the values in cells C3 and C11, not the range C3:C11.

Figure 2.13 Sum Function Adding Two Cell LocationsAbsolute References (Calculating Percentof Totals)Data file: Continue with CH2 Personal Budget.Since totals were added to row 12 of theBudget Detailworksheet, apercent 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 theAnnual Spend column with respect to the total in cell D12. However, after theformula is created, it will be necessary to turn off Excel’s relative referencingfeature before copying and pasting the formula to the rest of the celllocations in the column. Turning off Excel’s relative referencing feature isaccomplished through an absolute reference. The following steps explainhow this is done:1.Click cell B3 in theBudget Detailworksheet.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 represents16.7% of the Annual Spend budget (seeFigure 2.14).

Figure 2.14 Adding a Formula to Calculate the Percent of TotalFigure 2.14shows the completed formula that is calculating the percentagethat Household Utilities Annual Spend represents to the total Annual Spendfor the budget (see cell B3). Normally, we would copy this formula and pasteit into the range B4:B11. However, because of relative referencing, both cellreferences will increase by one row as the formula is pasted into the cellsbelow B3. This is fine for the first cell reference in the formula (D3) but notfor the second cell reference (D12).Figure 2.15illustrates what happens ifwe paste the formula into the range B4:B12 in its current state. Notice thatExcel produces the #DIV/0 error code. This means that Excel is trying todivide a number by zero, which is impossible. Looking at the formula in cellB4, you see that the first cell reference was changed from D3 to D4. This isfine because we now want to divide the Annual Spend for Insurance by thetotal Annual Spend in cell D12. However, Excel has also changed the D12 cellreference to D13. Because cell location D13 is blank, the formula producesthe #DIV/0 error code.

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 53 pages?

Upload your study docs or become a

Course Hero member to access this document

Term

Fall

Professor

NoProfessor

Tags

Control key