Detecting problematic lookup functions in spreadsheets

Felienne Hermans, Efthimia Aivaloglou, Bas Jansen

Research output: Chapter in Book / Report / Conference proceedingConference contributionAcademicpeer-review

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.

Original languageEnglish
Title of host publicationProceedings - 2015 IEEE Symposium on Visual Languages and Human-Centric Computing, VL/HCC 2015
EditorsScott D. Fleming, Zhen Li, Claudia Ermel
PublisherIEEE Computer Society
Pages153-157
Number of pages5
ISBN (Electronic)9781467374576
DOIs
Publication statusPublished - 14 Dec 2015
Externally publishedYes
EventIEEE Symposium on Visual Languages and Human-Centric Computing, VL/HCC 2015 - Atlanta, United States
Duration: 18 Oct 201522 Oct 2015

Publication series

NameProceedings of IEEE Symposium on Visual Languages and Human-Centric Computing, VL/HCC
Volume2015-December
ISSN (Print)1943-6092
ISSN (Electronic)1943-6106

Conference

ConferenceIEEE Symposium on Visual Languages and Human-Centric Computing, VL/HCC 2015
CountryUnited States
CityAtlanta
Period18/10/1522/10/15

Fingerprint

Dive into the research topics of 'Detecting problematic lookup functions in spreadsheets'. Together they form a unique fingerprint.

Cite this