|
Figure 17. Precedent Arrow for a Range of Cells
In the above figure, the formula indicates a range of cells. Trace precedent
shows the range of cells in a rectangle, and shows only one arrow from the
range of cells.
This box arrow will appear, e.g., in these formulas: =sum(A1:A10),
=average(A1:10), =count(A1:B10).
The box arrow can cover many rows and columns, as shown in the following
figure.
Figure 18. Precedent Arrow for a Multi-column Range of Cells
More examples of “Trace Precedents” pattern of arrows will be given later.
For example, what happens when a formula refers to a cell in another Excel
worksheet? What happens in complex formulas that look up a table of data,
such as vlookup?
If we trace the dependents of Ben’s English mark, the following figure will
appear. First select Ben’s English mark (click on it), then click on “Trace
Dependents”.
Figure 19. Trace Dependents
Since Ben’s English mark is used by only one cell, there is only one arrow
from there.
The “box arrow” drawn by “Trace Precedents” will not appear when we trace
dependents. It is a good point to bear in mind. Tracing precedents over an
entire worksheet may not give an identical set of arrows, compared to tracing
dependents.
The next figure shows the result when we trace the dependents for every cell
(click on “Trace Dependents” after selecting each cell). Notice that the pattern
of arrows is quite different.
Figure 20. Dependent Arrows do not have the box outline
5. Wrong or Missing Connections: A Common Error
A wrong connection happens when a formula in a cell refers to a wrong cell.
This is a very common error. But it is notoriously difficult to find just by
looking at formulas one by one. With the precedent/dependent arrows, the error
may become visually obvious.
One wrong connection can have big consequences. Here are a few cases.
$15M mistake in county books being corrected: Treasurer says spreadsheet
error had no financial impact [Herald-Times, Bloomington, Ind.]
(URL: http://www.tmcnet.com/usubmit/2009/11/21/4494996.htm)
"The financial discrepancy came to light earlier this month during a county
council work session. At that time, Smith described it as a reporting error that
happened when two employees switched jobs, after which the new employee
in her office began using a different spreadsheet that contained a broken data
link. A single entry error compounded over the past few months to make the
apparent discrepancy larger, she said at the time, eventually leaving the
county's books showing $15 million more than the bank said it had."
Trustee's Office mistake to cost taxpayers $12,500
(URL:
http://archive.knoxnews.com/news/local/trustees-office-mistake-to-
cost-taxpayers-12500-ep-402350878-357355791.html)
"He said the reports his office sent to KPMG didn't include one account, which
threw off his department's financial statements. The total cash on hand for June
30 that the office should have reported was $128.9 million, he said, and not
$122.7 million, which was what was sent to the auditor. It occurred when one
account wasn't correctly linked into an Excel spreadsheet."
Example 1: A payroll model
For an example of how precedent arrows can give clues to errors, consider
this spreadsheet (given in this article (
Ferret Out Spreadsheet Errors
, Use
Excel’s tools to uncover and correct formula problems.by Mark G. Simkin,
January 31, 2004, JournalofAccountary.com)
The precedent picture generated from
the spreadsheet
is given below. This is
generated by selecting the “total” cell (G14) and clicking “Trace Precedents” a
few times.
Irregularities in the blue dots indicate potential errors. A blue dot on a cell
means that the cell is the beginning of one or more precedent arrows.
Lack of a blue dot means that the cell is not used (for the set of precedent
arrows shown). A missing blue dot may indicate a missing connection.
Can you spot all the irregularities?
Figure 21. A Model with Missing Connections
Notice that in column C (regular hours), only one cell has a blue dot, indicating
that this cell is used in some formula in other cell or cells. More importantly,
the other cells in column C are not used, as they do not contain any blue dots.
This pattern strongly suggests the presence of errors, and these cells should be
checked thoroughly.
The relevant portion is shown below.
Figure 22. Missing Dots, Missing Connections
In column D (overtime hours), there is also a cell without a blue dot, different
from its neighbors. This also indicates a potential error. If you look harder, you
can find that cell F7 also has a different arrow pattern compared to its
neighbors.
This example illustrates a very important use of the automatically generated
influence chart. While the items are there, the connections are missing.
The influence chart makes the missing connections visually obvious.
Example 2: An economics model
This economics model, shown earlier, is repeated below, to illustrate another
arrow pattern that indicates potential errors. This is about the position of box
arrows. The boxes miss out the intended rows (rows from 25 to 29).
Figure 23. Excluded Rows in Faulty Range Cell Formulas
6. An Enhanced Influence Chart: Power Formula Auditing
Excel’s tools for tracing precedents and dependents are very useful. They can
be make even more useful with some additional programming. An enhanced
tool for generating better influence charts can be downloaded here:
Power
Formula Auditing
(Excel 2013).
Power Formula Auditing has very good improvements over Excel’s tracing of
precedents and dependents. The main improved features include:
-
The ability to select many cells for tracing precedents and
dependents. Excel’s trace precedents/dependents buttons are limited to
only one selected cell.
-
The ability to get an entire overview with just 2 clicks. Simply select
the entire spreadsheet, and click one button on the new toolbar.
Currently, to get an overview may require a few hundred clicks, and
most people will just not do it, and they will miss out on powerful
influence charts.
Power Formula Auditing is developed based on the original formula auditing
functions. It is unlikely to change the appearance of spreadsheets. However, it
is advisable to work on duplicate copies.
Power Formula Auditing may be slow for very big models, e.g., a thousand
cells. The functions may take some time to complete their jobs. Excel’s “Trace
Precedents” avoids the time delay by limiting to only one selected cell.
This software works for Windows Excel 2013 and 2010. It may not work for
older versions of Excel. It is the product of spreadsheet research by the author
and others at the National University of Singapore. (The URL to download is:
https://drive.google.com/open?id=0B926LJ4H-JLIdHItcWE3WEJnNms)
The software can be easily added to Excel 2013, as an add-in software. This is
explained in Appendix A. Appendix B explains how it can be removed.
7. Tracing Precedents / Dependents for Multiple Cells
Do'stlaringiz bilan baham: |
