Testing Tips
There are some techniques that might help you as you define and run your tests:
Omitting tests
Sometimes you don't want to run all your tests. Maybe there is one that is consistently failing, and you want to find out why, without having to run all the other tests as well.
You can stop a test from being executed simply by editing the cell containing the entry Test: so that it contains something else, such as NoTest:. XLSior will simply ignore all the rows of test definitions until it encounters the next cell in column A containing Test:.
Although any text other than Test: will have the effect of commenting out the test set, the use of NoTest: is recommended. XLSior recognises NoTest: as indicating that a test set is present but that it shouldn't be run, and annotates it accordingly.
If you omit tests by changing Test: to NoTest: (or some other text) you have to remember to change the entries back when you want to run the tests again.
You can omit all the tests on a sheet by renaming the sheet so that its name no longer starts with X~Test. Again, remember to change the name back when you want to run the tests again.
Testing for equality
You may get misleading results if you have test conditions that check that two numbers are equal; it often happens that they look equal but the test fails. This is because they are in fact slightly different, because of the inbuilt inaccuracies in Excel's arithmetic. Although the differences are small, they are enough to make the two numbers unequal.
The easiest way to get round this is to set up two test conditions using greater than and less than ( > and <). If you have two cells whose values should be equal you define one condition as Cell1 > Cell2 - .000001 and the second condition as Cell1 < Cell2 + .000001. Obviously you can choose the accuracy of testing (in this case .000001).
Testing for true or false
Sometimes you don't want a test condition to compare two values, you just want to know if something is TRUE or FALSE. You can do this by putting the relevant formula as Value1, using = as the operation to perform, and putting the value TRUE or FALSE in Value1.
Checking invariants
You can use XLSior test sets to check invariants, ie things that should be true regardless of the data, such as checking that row totals sum to the same as column totals, or that balance sheets actually balance. There are two ways of checking for invariants:
- Have a separate test set with no test data, with the test conditions checking the invariants.
- Add test conditions for the invariants to every test set.
Substitution ranges
It is often convenient to store the test data on the same sheets as the test definitions. If you do this, make sure that the ranges are not going to be altered if you insert new rows into your test definitions. I often put the test data below the test definitions, and then insert rows above the test data when I want to add a new test set.