One of the biggest mistakes people make when constructing large Excel workbooks is they build formulae upon formulae, until they have lost sight of what they were originally trying to produce. Moreover, the size and inherent complexity of the workbooks constructed result in easy simplification of formulae being missed. Formulae that are dependent on multiple cells can often be reduced to a few characters with some simple modification.
The trick is to write your formulae down before you start, as you are proceeding, and certainly when you encounter problems. Pen and paper can save a lot of hard work later. Often how you would solve a calculation problem at school is how you should solve it on a computer.
The speed and ease of computers can lead us to be lazy and can cause multiple problems down the line.
We at JumpData have encountered this situation on many occasions. For example, one workbook we were recently asked to help a client with, had multiple formulae with so many dependencies that it would calculate so slowly as to be unusable.
So, we reverse-engineered the problem. For each formula (those that were relevant), we traced all their first dependencies, then wrote the actual equations down; then the second set, using the notation from before, as we might if we were writing a paper or the like. By proceeding in this manner, we arrived at formulae written in terms of the base inputs.
Then we cancelled out, where necessary, simplified, and turned multiple worksheets of complicated formulae, with multiple dependencies into a small number of formulae that pulled directly from the base inputs. The result was a workbook that was functional and able to easily produce all the analysis required. More importantly, it was easy to trace formulae dependencies to allow simple validation of the results.
