Validating the unit correctness of spreadsheet programs

Financial companies, engineering firms and even scientists create increasingly larger spreadsheets and spreadsheet programs. The creators of large spreadsheets make errors and must track them down. One common class of errors concerns unit errors, because spreadsheets often employ formulas with physical or monetary units. In this paper, we describe XeLda, our tool for unit checking Excel spreadsheets. The tool highlights cells if their formulas process values with incorrect units and if derived units clash with unit annotations. In addition, it draws arrows to the sources of the formulas for debugging. The tool is sensitive to many of the intricacies of Excel spreadsheets including tables, matrices, and even circular references. Using XeLda, we have detected errors in some published scientific spreadsheets.

[1]  Bay-Wei Chang,et al.  Fluid visualization of spreadsheet structures , 1998, Proceedings. 1998 IEEE Symposium on Visual Languages (Cat. No.98TB100254).

[2]  Mitchell Wand Finding the source of type errors , 1986, POPL '86.

[3]  Matthias Felleisen,et al.  DrScheme: a programming environment for Scheme , 2002, J. Funct. Program..

[4]  Narain H. Gehani,et al.  Units of Measure as a Data Attribute , 1977, Comput. Lang..

[5]  Mikael Rittri,et al.  Dimension inference under polymorphic recursion , 1995, FPCA '95.

[6]  Gregg Rothermel,et al.  Harnessing curiosity to increase correctness in end-user programming , 2003, CHI '03.

[7]  Mitchell Wand,et al.  Automatic Dimensional Inference , 1991, Computational Logic - Essays in Honor of Alan Robinson.

[8]  Andrew Kennedy,et al.  Relational parametricity and units of measure , 1997, POPL '97.

[9]  Matthias Felleisen,et al.  Catching bugs in the web of program invariants , 1996, PLDI '96.

[10]  Margaret M. Burnett,et al.  Forms/3: A first-order visual language to explore the boundaries of the spreadsheet paradigm , 2001, Journal of Functional Programming.

[11]  Paul Steckler Component support in PLT scheme , 2002, Softw. Pract. Exp..

[12]  Shriram Krishnamurthi,et al.  A type system for statically detecting spreadsheet errors , 2003, 18th IEEE International Conference on Automated Software Engineering, 2003. Proceedings..

[13]  Raymond R. Panko,et al.  What we know about spreadsheet errors , 1998 .

[14]  Margaret M. Burnett,et al.  Visually customizing inference rules about apples and oranges , 2002, Proceedings IEEE 2002 Symposia on Human Centric Computing Languages and Environments.

[15]  Gregg Rothermel,et al.  A methodology for testing spreadsheets , 2001, TSEM.

[16]  Gregg Rothermel,et al.  End-user software engineering with assertions in the spreadsheet paradigm , 2003, 25th International Conference on Software Engineering, 2003. Proceedings..

[17]  Andrew John Kennedy,et al.  Programming languages and dimensions , 1995 .

[18]  Michiel Korthals,et al.  Inf'erence d'unit'es physiques en ML , 1994 .

[19]  Margaret M. Burnett,et al.  Adding Apples and Oranges , 2002, PADL.

[20]  Gordon Filby Spreadsheets in science and engineering , 1998 .

[21]  Ronald T. House A Proposal for an Extended Form of Type Checking of Expressions , 1983, Comput. J..

[22]  Gordon S. Novak Conversion of Units of Measurement , 1995, IEEE Trans. Software Eng..