Who is the odd man out? (Part 3)
Part 3 on identifying the odd man out is about precedents.
To recap, the aim in finding an inconsistent cell is that the inconsistency may indicate an error, or an inconsistent design.
If the cells in a block have "consistent" formulas, then each cell will have a set of "consistent" precedent arrows. This is illustrated in the following diagram.
If a cell has an "inconsistent" formula, its precedent arrows will be different. This is illustrated in the following diagram, using the same example as in part 2.
In Excel, getting the precedent arrows for a block of cells can be quite tedious. We need to click on a cell, click "trace precedents" (perhaps a few times), and repeat for each cell in the block.
However, the process can be done easily with some VBA processing. The Multi-Precedent macro allows precedent arrows to be drawn for a selected block of cells. This macro is part of the menu for VisualCheck add-in (Influence charts: How to easily gain a powerful overview of your Excel spreadsheet model) and also for Power Formula Auditing add-in (Power Formula Auditing: The Power Tool for every Excel User)
To recap, the aim in finding an inconsistent cell is that the inconsistency may indicate an error, or an inconsistent design.
If the cells in a block have "consistent" formulas, then each cell will have a set of "consistent" precedent arrows. This is illustrated in the following diagram.
Consistent Precedent Arrows among Cells |
If a cell has an "inconsistent" formula, its precedent arrows will be different. This is illustrated in the following diagram, using the same example as in part 2.
Inconsistent Precedent Arrows. |
In Excel, getting the precedent arrows for a block of cells can be quite tedious. We need to click on a cell, click "trace precedents" (perhaps a few times), and repeat for each cell in the block.
However, the process can be done easily with some VBA processing. The Multi-Precedent macro allows precedent arrows to be drawn for a selected block of cells. This macro is part of the menu for VisualCheck add-in (Influence charts: How to easily gain a powerful overview of your Excel spreadsheet model) and also for Power Formula Auditing add-in (Power Formula Auditing: The Power Tool for every Excel User)
Comments
Post a Comment