Put an end to incomplete SUM() ranges

Put an end to incomplete SUM() ranges

It’s probably the classic spreadsheet error; the developer writes a simple SUM() formula which adds up sales of all products, and this works fine until a new product is added but is left out of the SUM() formula, which now gives an incorrect result. Back in the eighties a Florida company sued Lotus over an error of this kind, and now Microsoft Excel attempts to warn where this may be an issue, but it’s still very hard to be sure you haven’t overlooked an incomplete range.

That’s why we’ve included this “Precedents only part of block” report (shown above) in the new Map Risks function. It gives you detailed information about blocks (ranges) of precedents which are part of a larger block of cells, showing you in which direction(s) they could be extended, and crucially whether the surrounding cells are a direct copy of the cells in the current precedent block (which often indicates a range error).

The report highlights in yellow where the surrounding cells are a direct copy, and you can see an example of this in the screenshot above in relation to Control sheet cell B23. The formula in this cell takes in two ranges up to column AJ in the Financial statements sheet, and the report indicates that the formulas in column AK are a direct copy of those in column AJ, suggesting that the ranges should be extended to column AK or beyond.

If you now look at the Financial statements sheet itself (on the right in the screenshot above) you will see that this is indeed the case; the calculations continue well beyond column AJ (actually up to column AS). The model is currently set up so that the project concludes in column AI, but if the assumptions were flexed so that it continued for a few more years, the model results (at least in Control sheet B23) would be incorrect.

So the “Precedent only part of block” report has rightly identified a potential error, and it can similarly help you find and correct errors of this kind before they become a problem.

Sorry, comments are closed for this post.

Back to topAdministration