Spreadsheet quality assessment
See a video of how to use our spreadsheet quality checker
Home Register Contact About Us

  XL-Excellence® - Examples - Formula consistency


Formula inconsistency can be a major source of spreadsheet weakness. The following example shows how the XL-Excellence® Add-In can detect and report on spreadsheet inconsistencies.

Two ways are shown below of calculating a running total ‐ a “bad” way (rows 2 to 5) and a “good” way (rows 7 to 10).

A bad way of calculating a running total

Both methods give the same answer (rows 5 and 10) but only the “good” method uses consistent formulas ‐ as shown by the following report.

A good way of calculating a running total

The heatmap above has been generated to report on formula consistency. Cells with the same colours contain the same formulas.

The report leaves the original spreadsheet unchanged - it is composed on a new spreadsheet. To maximise speed and minimise memory required the report is a "lightweight" copy of the original: Formulae are not copied - only values and formatting (but not conditional formatting).

Note also that you can "hyperlink" between the original spreadsheet and the report in order to examine formulae in the original spreadsheet.

The colours change above on row 5 in the “bad” method ‐ but the colours are consistent on row 10 in the “good” method. Why is that?

We can answer that by putting the original spreadsheet into “Formula View” mode. Pressing the CTRL + ` keys does that. Now the spreadsheet shows the underlying formulas.

Excel formula view mode reveals the underlying formulae

We can see formulas are inconsistent in the “bad” method: The formula in cell C5 picks up the first revenue. Following formulas are different: They add the current period's revenue to the prior period's running total.

How does the “good” method work?

Formulae used in the good method of calculating a running total

The “good” method uses a consistent formula: The cumulative revenue is the sum of revenues from the “anchor” cell ($C$9) to the current period's cell (xx$9).

If you wish to experiment with the examples discussed above you can download the examples by choosing from the Links section.


Sitemap | Terms of use | Privacy | Contact us
- -