Economists really believe they control business performance (just as lawyers really believe they control the moral behaviour of citizens).
Spreadsheets are a great programming tool for all such beancounters. It is much more difficult to overlook an error in spreadsheet calculations than when, for example, coding in C.
It’s certainly not impossible, however, as recent events have shown.
Today’s invention is a simple macro, like a spellchecker, which would examine all functions which use data in a bank of cells as input.
If the bank has non-zero values in any cells on its immediate periphery, it would issue an alert and automatically outline the cells in question.
difficult to implement as I have seen spreadsheets with 2D data in which subtotals etc reside.
However knowing the range of the function, in this case a vertical range, you could check if the cells in the same direction as the range, vertical, to have a similar formula inside. Note that a formula can be a constant number.
Example:
you have a column of 5 numbers, directly under it =sum(A1:A5) and directly under that =average(A1:A5)
The range of sum is limited by the sum cell itself, no circular references allowed so that seems OK
The range of the average seems to have “one missing value” (sum number) but as it has a different signature “sum()” != “constant number” it does probably not belong to the range.
Another spell check function could test if ranges used in different cells are identical, as that is often the case. Also ranges of identical length but a different column/row seem often appropriate.
Finally I believe spreadsheets also have something I call “locality principle”, most functions are used near the data, often in line with their data (sum is under a column or at end of row). Exceptions are single cell data e.g. VAT% and lookup tables.
my 2 cents,
Rob
Thanks for this Rob…certainly true that a basic spatial check would never catch every possible error.
Maybe the best thing would be to have an enhanced ‘trace dependents’ function, which could be switched on more easily and with much greater display clarity.
This would make it clear, perhaps by coloured background shading, every time a new formula was entered, exactly which cells it referred to.