Validating the unit correctness of spreadsheet programs

Abstract

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 toolis 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.

AutoTest: A Tool for Automatic Test Case Generation in Spreadsheets

“…On the other hand, satisfying γ F 3 leads to 0 in B8, which results in γ F 4 being satisfied. 6 Actually, the first constraint can also be satisfied by setting values in B5 or B6 to −1. Since the three test cases exercise the same DU pair, the system only generates the first one.…”

“…Since the sets {γ T 4 : − 1, γ F 3 :0} and {γ F 4 :D4+D5+D6, γ T 3 :1} cannot be satisfied, 5 we are left with {γ T 4 : − 1, γ T 3 :1} and {γ F 4 :D4+D5+D6, γ F 3 :0}. The first set of constraint can be satisfied by setting value in B4 to −1, 6 and the second set of constraints is already satisfied by the values in the spreadsheet.…”

“…The systems described in [5,6] allow the user to carry out consistency checking of spreadsheet formulas on the basis of user annotations, or on the basis of automatically inferred headers [1], and flag the inconsistent formulas as potential faults. Consistency checking can also be carried out using assertions on the range of values allowed in spreadsheet cells [8].…”

Combining spatial and semantic label analysis

“…Units of measurements can be employed as a concrete notion of types that is well known among end users [4], and are used to characterize different kinds of values, much like traditional, more abstract, type systems used in general-purpose programming languages. For example, a floating point number, which has just one type, can nevertheless represent different kinds of quantities, such as length or time values.…”

“…Several systems [5,6,4] have been developed in order to deal with unit of measurement errors. Among these dimension inference [5] is a method that can be used to automatically find dimension errors in spreadsheets.…”