can be found on the European Spreadsheet Risks
Interest Group (EuSpRIG) website.
Spreadsheet engineering is frequently described
as end-user programming. A 2005 study (Scaffidi et
al., 2005) estimates that by 2012 there will be over
13 million end-user developers in the US, compared
with 3 million professional programmers. It is
acknowledged that end-user developed systems
introduce risk into an organization, and these risks
can have many influencing factors, for instance,
spreadsheet developers do not follow a structured
process, many are untrained in the use of processes
as in software development, and are rarely aware of
the unreliability of spreadsheets. One study found
that only 6% of development time is spent testing
spreadsheets (Baker et al., 2006).
Spreadsheet researchers all converge on the same
findings – spreadsheet use is ubiquitous, and
spreadsheet quality is not considered paramount
within organisations. Testing is not considered
crucial, and critical decisions are made based on
unregulated spreadsheets.
1.2 Naming
Many researchers have examined the importance of
naming in programming. Keller (1990) found that
people who read programs that followed a defined
naming scheme found them easier to read, but could
not pinpoint why. Jones (2008) raises the issue of
incorrect spelling: “if people make spelling mistakes
for words whose correct spelling they have seen
countless times, it is certain that developers will
make mistakes, based on the same reasons, when
typing a character sequence they believe to be the
spelling of an identifier”.
Range names are a feature in Excel that allows a
developer to assign a meaningful name to a cell or
group of cells. This name can then be used
throughout the spreadsheet instead of the cell
reference. Names can be easily created, modified,
and deleted through the Name Manager, located in
the formulas tab of the Excel ribbon.
Practitioners often suggest that range names can
make spreadsheets easier to understand and to
develop, in books, academic papers, journals, and on
websites, illustrated by the following examples:
• “Range names improve reliability. If you need
to change references to the range, you only
have to change the definition of the range
name. Then every formula that uses it will refer
to the new address.” (O'Beirne, 2005)
• “Clearly, using the Defined Names makes the
formula much easier to understand and
maintain.” (Pearson, 2009)
In contrast, some experts caution against using
range names. Panko and Ordway (2005) warn that
range names “should be considered potentially
dangerous until research on using range names is
done.” Blood (2006) states that names are
unnecessary if the model is well designed, and that
range names make it more difficult to audit
formulas, as important information becomes hidden.
He also criticizes range names for making formulas
unnecessarily long.
1.3 Summary
Many researchers have explored spreadsheet errors,
in terms of their frequency and causes. It is widely
recognised that spreadsheets are unreliable, and
range name use is often mentioned as a practice that
improves spreadsheet quality. The majority of
practitioners are in favour or range names, yet a few
vocal opponents remind us that there is no scientific
evidence to support these recommendations.
This paper outlines earlier aspects of our
research programme, and then describes in detail an
experiment to establish the effect of range names on
the reliability of basic spreadsheet formulas.
2 BACKGROUND
This study is part of a wider research project that
investigates the impact of range names on
spreadsheet reliability, in order to assess the
feasibility of recommending range names for use in
spreadsheets, and is guided by the following
objectives:
Objective 1: Investigate the impact of range
names on the ability of novice users to successfully
identify and correct errors in a spreadsheet.
Objective 2: Investigate the reliability of
spreadsheets developed using range names.
Objective 1 was addressed by experiments
detailed in Section 2.1. The study presented in this
paper begins to address Objective 2 by examining
the reliability of formulas developed using range
names, as compared to formulas developed using
cell references.
2.1 Debugging Experiments
The experiments that addressed Objective 1 were
adapted from a design first used in a study by Howe
and Simkin (2006), and later used by Bishop and
ICSOFT 2011 - 6th International Conference on Software and Data Technologies
270