Authors
Yirsaw Ayalew
Abstract
Spreadsheet programs, artifacts developed by non-programmers, are used for a variety of tasks and decisions. Yet a significant proportion of them have severe quality problems. This thesis presents a new approach for checking spreadsheets on the premises that their developers are not software professionals. The approach takes inherent characteristics of spreadsheets as well as the conceptual models of spreadsheets programmers into account and incorporates ideas from symbolic testing and interval analysis.
Unlike symbolic testing, which requires expressing formulas in terms of input variables, interval-based testing uses intermediate variables for the purpose of narrowing down computed intervals. In addition, while symbolic testing is used to validate a formula for any possible values of the input variables, interval-based testing requires the values of the variables to be expressed as intervals and validity is determined based on the intervals provides.
The observation that spreadsheets are mainly used for numerical computations enables us to introduce the idea of interval analysis to spreadsheet testing. Interval-based testing focuses on the functionality of the spreadsheet formulas instead of the internal structure of a spreadsheet program (i.e., it is not based on code coverage criterion). It requires the user to specify input and expected intervals for desired input and formula cells respectively.
This will be documented in a behind-the-scene spreadsheet and used to perform interval computations during the verification of a given spreadsheet. In addition, the expected intervals provided by the user are verified for reasonableness using interval analysis. The approach provided is thus essentially a kind of stratified plausibility check based on the consistency of legitimate boundaries users might specify for computations.
Sample
A bounding interval is a computed interval used to check the reasonableness of the expected interval specified by the user.
For example:
- (a) shows the formula in A3 to compute the sum of two numbers.
- (b) shows the result of the calculation in A3.
- (c) shows the corresponding input and expected intervals.
- (d) shows the bounding interval for the formula in cell A3.
Publication
2001, Ph.D thesis, Klagenfurt University, November
