Test Driven Development In Excel
I was working at a health care giant, when I was presented an interesting problem. One sheet would NOT add up. The guy that wrote the sheet, could not understand why. See, Excel SHOULD add up stuff, and get it right, that’s what we expect.
The problem was that his equations (over 5,000) built in small rounding errors and IF-THEN errors that compounded until the final total was off. You know what a rounding error is. An IF-THEN error could be caused if a cell is less than $1, so we just enter zero. That rounding-off can cause a huge error as the result (zero) is later multiplied to arrive at a subtotal.
So how do we combat the cascade of small errors. One way is with test-driven-development. We include a hidden sheet that has known inputs and an expected results. The data on this hidden sheet is run through the main worksheet every time the sheet is opened. This “tests” our equations to insure we don’t have issues.
In the example above, I used VBA code to simulate the same Excel equations and printed OK in RED to show that the test inout matched the expected results.