• Corpus ID: 11581526

The Lookup Technique to Replace Nested-IF Formulas in Spreadsheet Programming

@article{Grossman2009TheLT,
  title={The Lookup Technique to Replace Nested-IF Formulas in Spreadsheet Programming},
  author={Thomas A. Grossman and {\"O}zg{\"u}r {\"O}zl{\"u}k and John Harold Gustavson},
  journal={ArXiv},
  year={2009},
  volume={abs/0908.1188}
}
Spreadsheet programmers often implement contingent logic using a nested-IF formula even though this technique is difficult to test and audit and is believed to be risky. We interpret the programming of contingent logic in spreadsheets in the context of traditional computer programming. We investigate the "lookup technique" as an alternative to nested-IF formulas, describe its benefits for testing and auditing, and define its limitations. The lookup technique employs four distinct principles: 1… 
View PDF on arXiv
5 Citations
Background Citations
1
Methods Citations
1

Figures from this paper

5 Citations

Effect of Range Naming Conventions on Reliability and Development Time for Simple Spreadsheet Formulas
TLDR
It is found that the choice of naming convention can have a significant impact on novice and intermediate users' performance in formula development, with less structured naming conventions resulting in poorer performance by users.
ASP, The Art and Science of Practice: Increasing Productivity and Minimizing Errors in Operations Research Spreadsheet Models
TLDR
This work examines some problem areas for spreadsheet design and programming and suggests techniques intended to increase productivity and reduce the risk of errors, especially in situations in which someone other than the original author is using or maintaining the spreadsheets.
How do Range Names Hinder Novice Spreadsheet Debugging Performance?
TLDR
Range names are investigated in a more structured and controlled manner the effect they have on the debugging performance of novice spreadsheet users.
User Defined Spreadsheet Functions in Excel
TLDR
This paper presents a method to create a limited form of UDSFs in Excel without any use of VBA, which utilizes what-if data tables to execute the same part of a worksheet several times, thus turning it into a reusable function definition.
Hands-Off Spreadsheets
TLDR
This paper applies to those spreadsheets which involve significant operator intervention, describes a method that has been used to improve reliability and efficiency, and reports on how it has worked in practice.

References

SHOWING 1-10 OF 23 REFERENCES
What we know about spreadsheet errors
TLDR
Although spreadsheet programs are used for small "scratchpad" applications, they are also used to develop many large applications, and error rates are in line with those in programming and other human cognitive domains.
An auditing protocol for spreadsheet models
Lessons from Mission-Critical Spreadsheets
TLDR
It is concluded that spreadsheets are vitally important to business, and merit sustained research to discover techniques to enhance quality, productivity, and maintainability.
Spreadsheet-Based Professional Modelling
This paper describes, with reference to several professional models developed by the author, how the use of Excel often allows for a much higher efficiency to cost ratio than more traditional…
Impact of errors in operational spreadsheets
Regulation and the Integrity of Spreadsheets in the Information Supply Chain
TLDR
ClusterSeven provides one of these monitoring solutions, highlighting areas of operational risk whilst also establishing a database of information to deliver new business intelligence.
A critical review of the literature on spreadsheet errors
Spreadsheet modelling for management decision making
TLDR
This paper considers the potential use of spreadsheets as decision‐support systems (DSS), and considers some of the models that can be easily built with modern spreadsheet packages, including models to support what‐if or scenario analysis, sensitivity analysis, goal seeking and optimization.
The Importance and Criticality of Spreadsheets in the City of London
TLDR
The author suggests that the City of London is presently exposed to significant reputational risk through the continued uncontrolled use of critical spreadsheets in the financial markets and elsewhere.
New Guidelines For Spreadsheets
TLDR
Rules of style for text, graphics, and mathematics are examined and a new style is applied to spreadsheets, which contrasts with the existing programming style.
...
...