msgbartop
Just another WordPress site
msgbarbottom

19 Jun 18 TDD in Excel




What is TDD?

Test-driven development (TDD) is a software development process that relies on the repetition of a very short development cycle: requirements are turned into very specific test cases, then the software is improved to pass the new tests, only” (Wikipedia)

We could think this refers only to programming/coding. I don’t think so.

How did I apply a version of TDD in Excel?

Theoretically, when we build an Exel model then it should be simple and easy to read.

However, life goes beyond the simplicity and readability rule because it is important to maintain the structure of the table and/or the importance of calculation speed.
If we use complicated formula, that should be protected or converted to values to ensure the formulas cannot be edited by those who are not familiar with it.

In any case, complicated formulas must be thoroughly tested.

These test cases were listed and the formula was copied to under one another to check different kinds of input values for the correct calculation.

For example, we would like to calculate the date of the first Monday of the first working week.
This is a very simple example, I use this method for much more complicated calculations.

The trick is the week that counts as the first workweek is the one that contains the first Thursday of the year.

I listed years where the first days of January are on different days so that all of the 7 cases are correct. (I added the correct result to compare the calculated results with)

And it seems that there was a need for it because a bad calculation can give a good result.

If I had tested it only till 2020, I could think that everything is good in the first run.
If I had tested it till 2025, I would think the second run is correct.
Thus, the third run gave a result that is good for each case.

For a perfect solution, it would also be worth dealing with invalid inputs, eg. someone makes a mistake and writes a date instead of a year, or writes a text, an error or a fraction.



 

Comments are closed.