Detecting Problematic Lookup Functions in Spreadsheets

Report (2015)
Department
Software Technology (EEMCS) (TU Delft)
Copyright: © 2015 The Author(s) . Software Engineering Research Group, Department of Software Technology, Faculty of Electrical Engineering, Mathematics and Computer Science, Delft University of Technology
More Info
expand_more

Published Date

31-12-2015

Reuse Rights

Other than for strictly personal use, it is not permitted to download, forward or distribute the text or part of it, without the consent of the author(s) and/or copyright holder(s), unless the work is under an open content license such as Creative Commons.

Source:

Report TUD-SERG-2015-011 Preprint accepted for publication in Accepted for publication in the Proceedings of the IEEE Symposium in Visual Languages and Human Centric Computing (VL/HCC 2014)

ISSN:

1872-5392

Source:

http://swerl.tudelft.nl/bin/view/Main/TechnicalReports

Faculty

Electrical Engineering, Mathematics and Computer Science

Department

Software Technology

Abstract

Spreadsheets are used heavily in many business domains around the world. They are easy to use and as such enable end-user programmers to and build and maintain all sorts of reports and analyses. In addition to using spreadsheets for modeling and calculation, spreadsheets are often also used for creating reports and dashboards: combining data from different sources and creating overviews. For this, lookup functions can be used: they search for a value in a range and return a corresponding row or column. Lookup functions are common: according to recent research the VLOOKUP is the fifth most common Excel function. In this paper we investigate the use of lookup functions in more detail. We analyze lookup functions within the newly released Enron spreadsheet corpus. The results show that 1) a minority of 43% of lookup formulas use the default setting where an approximate match may be returned, 2) 77% of approximate matches are used unnecessary and 3) 23% of approximate lookups is problematic: they search over unsorted ranges, while this is specifically advised against in the specification, and might lead to wrong results.