i spreadsheet testing methodology — Immunity

No spreadsheet can be guaranteed to be 100% free of errors under all circumstances. But the way a spreadsheet handles errors has a major impact on its usability and the confidence that stakeholders have in its outputs.

Specific questions to consider when testing a spreadsheet's immunity:

    • To what extent is the spreadsheet immune to errors? This question recognises that it is unrealistic to expect a spreadsheet to be completely immune to all potential and actual sources of error. Nonetheless, it is important for a spreadsheet to deal with the most common types of user and data errors without failing. Instead of failing, the spreadsheet should recognise when errors have occurred and respond appropriately.
    • How well does the spreadsheet handle invalid data? The user needs to get helpful and constructive feedback when they enter invalid data. Having the spreadsheet filled with cell values such as #DIV/0!, #VALUE!, or #N/A is not an elegant or helpful way to handle invalid data.
    • Is the spreadsheet easy to modify without creating errors? The spreadsheet should be constructed in a way that is easy to modify while minimising the potential for creating errors. For example, using named ranges rather than cell address in VBA avoids the errors created when a row or column is inserted.
The way a spreadsheet handles errors has a major impact on its usability and the confidence that stakeholders have in its outputs.
  • Are the error messages adequate? When an error occurs, the spreadsheet should provide meaningful information that enables the user to recognise that an error has occurred and provides guidance about how to correct the error.
  • Does the spreadsheet fail gracefully when necessary? For situations not directly handled by the spreadsheet, it should still fail in a way that causes minimal problems. In particular, having a spreadsheet report an incorrect result, but giving no indication that something went wrong, can be disastrous.

Finally, we summarise the i spreadsheet testing methodology with a brief conclusion.

Previous: Implementation

Next: Conclusion

Return to: Overview