Most attempts to test spreadsheets use the spreadsheetinternal model to automatically detect input, intermediate and output cells. In this paper, we discuss an example which shows why this approach is problematic even for simple spreadsheets. Following this, we derive a number of requirements for more feasible spreadsheet cell-meaning models and describe a rst prototype we have designed.
Input cells: Non-formula cells referenced by formula cells Intermediate cells: Formula cells referenced by other cells and referencing other cells themselves Result cells: Formula cells not referenced by other cells but referencing other cells themselves We will refer to this model as the `naive model` throughout this paper. While this model is certainly correct from a technical point of view, looking at it from the perspective of a spreadsheet user's domain can lead to imminent con icts in a number of cases. In this paper we discuss these cases and propose an explicit model which is more di cult to extract but which we believe is better suited for testing spreadsheets. Since the model is concerned with the type of cells from the perspective of what the cells mean to users, we could refer to it as \cell-type model". Unfortunately, the term \celltype" is usually already used for describing the data type of a spreadsheet cell's contents. To avoid confusion, we use the notion of \cell-meaning model" instead.
Figure 1 shows a spreadsheet for managing grades of a study course. The spreadsheet is lled with data by a course instructor and later passed to a secretary for transferring the grades to a di erent system. Furthermore, the spreadsheet is used for statistical purposes by the manager of the study course this exam belongs to. We want to use this spreadsheet as a showcase with counter examples, arguing why the naive model can lead to a biased perception of the actual input cells and output cells of a spreadsheet:
The total points (D7 to D12) could be output values for a secretary who has to process these grades further (e.g. write letters to students), but these cells are referenced by the grade cells (E7 to E12). Thus, the cells would be regarded as intermediate cells by naive models and not as output cells.
The study program manager might not be interested at all in the total points of the particular students but only in the failure rate (B15). Thus, he would not see the grades as output cells.
Spreadsheet authors sometimes use defensive programming techniques and introduce checks themselves. The plausibility column (G) is such an example: It checks whether any of the grades for Task 1 or Task 2 are outside acceptable limits (zero to maximum points for the task). The naive model would treat the cells in the plausibility column (G7 to G12) as output as well. The second worksheet named `grading key` is referenced by VLOOKUP functions in the grade column's cells (E7 to E12). The naive model would therefore interpret these referenced cells as input cells. However, none of the users of this spreadsheet is supposed to change the contents of these cells as they contain merely static data.
We are convinced that biased perceptions can be reduced if a spreadsheet is tested using a model explicitly designed for this purpose. From the discussion in the previous section, we derive the following requirements for such a model: User-speci able: It must be possible for users to specify the cells themselves. If automatic extraction is used, users must be able to change it.
Support for views: Since users have di erent perceptions and needs of the same spreadsheet, we either need one testing model with di erent views or it must be allowed that more than one testing model instance per spreadsheet exists.
Input cell types: The model must separate input cells at least into two types: data cells (which contain data that rarely changes or is fed from another system) and actual decision variables which are supposed to be manipulated by the user (of this model).
Output cell types: Apart from intermediate cells, the model must support cells which provide the data with nal results (which the user is looking for) as well as support for plausibility and other additional cells. Apart from these rather theoretical requirements, we identied two major practical requirements for the success of the implementation of such a model:
Understandability: It must be easy for spreadsheet users to understand the model with no or little training, so that users can identify cells correctly in the sense of the model.
Acceptance: Even if the model would be easily
understandable for spreadsheet users, its bene ts must be
striking so that spreadsheet users will be motivated to
take the e ort connected with using the model.
(Basically this requires a proper attention investment model
as described by Blackwell and Burnett [
It seems infeasible to expect spreadsheet users to identify all cell-meanings manually, especially for huge spreadsheets. As already discussed, fully automated cell-meaning detection seems impossible | but it might be bene cial to consider assisting users by proposing cell-meanings based on autodetection techniques.
A promising starting point could be the work of Hermans
[
We prototyped a cell-meaning model which takes into account the requirements stated in the previous section. The model is illustrated in Figure 2 as a standard UML class diagram.
We provide a partial implementation of our prototype in our tool `Spreadsheet Inspection Framework` which is available as open source software from GitHub1. The implementation allows users to manually mark cells and use them later for specifying test cases, but does not support all proposed cellmeaning types yet and lacks auto-detection capabilities.
To assess the feasibility of the model, further research is required. A crucial aspect for evaluation will be the question whether the cell types can be communicated clearly to users, so users will tag existing spreadsheet cells according to our proposed model.
Another important aspect will be the acceptance of the model. Since we believe that acceptance might be very low without a reasonable level of automated assistance, it seems worthwhile to address this point rst.
Although we explained the theoretical limitations of the naive model in this work, it must be explored whether the additional manual e ort connected with applying our model yields enough bene ts in terms of its ability to detect faults in spreadsheets more accurately. 1https://github.com/kuleszdl/Spreadsheet-InspectionFramework
We would like to thank Zahra Karimi, Kornelia Kuhle, Mandy Northover, Jochen Ludewig and Stefan Wagner for their constructive feedback on earlier versions of this position paper. Furthermore, we received many good hints and comments from the reviewers for which we are very thankful.