Cohesion 3 (Bad examples)
The classic example of poor cohesion in a spreadsheet is a block of cells that might reasonably be expected to contain the same formula, but in fact don't. A very common example which regularly catches maintainers out are tables of VLOOKUPS where the table column is hard coded, and changes in each column.
In the above formula the 2 becomes a 3 in column F, a 4 in column G etc. This means changes that get copied across will break the model. This particular case can be fixed by using external column indexes so all formulas are identical:
In this table the VLOOKUP is replaced by a MATCH to find the Total Expenses Row, and then a series of INDEXes that use the grey row above to find their column index.
This is a simple example, but common. Another all too common example is where the first formula in a running total is different from all those below.
Audit tools have long picked up on this, and in Excel from 2002 onwards one of the possible error checks is inconsistent formula in the region. This can be used to spot poor block cohesion, well it could if it worked, which it doesn't sadly. Most decent third part auditing tools will spot this though.
Poor cohesion can also occur at the sheet level, where for example a project settings sheet is used as a dumping ground for lots of fairly unconnected pieces of data. Indeed poor cohesion can occur at any level within the spreadsheet hierarchy whenever elements that are not directly or closely related are bundled together for developer convenience rather than as a clear design decision.
|
