• 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… 

Figures from this paper

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.

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.

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.

References

SHOWING 1-10 OF 22 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.

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

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.

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.

Teaching Management Science with Spreadsheets: From Decision Models to Decision Support

TLDR
An inventory of the current state of MS education is taken and some promising new directions in the area of decision support systems for MS educators to consider for the future are suggested.