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

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) make logical tests visible; 2) make outcomes visible; 3) make logical structure visible; and 4) replace a multi-function nested-IF formula with a single-function lookup formula. It can be used only for certain simple contingent logic. We describe how the principles can be applied in more complex situations, and suggest avenues for further research

Initial report on Object Spreadsheets

There is a growing demand for data-driven web applications that help automate organizational and business processes of low to medium complexity by letting users view and update structured data in controlled ways. We present Object Spreadsheets, an end-user development tool that combines a spreadsheet interface with a rich data model to help the process administrators build the logic for such applications themselves. Its all-in-one interface with immediate feedback has the potential to bring more complex tasks within reach of end-user developers, compared to existing approaches. Our data model is based on the structure of entity-relationship models and directly supports nested variable-size collections and object references, which are common in web applications but poorly accommodated by traditional spreadsheets. Object Spreadsheets has a formula language suited to the data model and supports stored procedures to specify the forms of updates that application users may make. Formulas can be used to assemble data in the exact structure in which it is to be shown in the application UI, simplifying the task of UI building; we intend for Object Spreadsheets to be integrated with a UI builder to provide a complete solution for application development. We describe our prototype implementation and several example applications we built to demonstrate the applicability of the tool

The A.D.E. taxonomy of spreadsheet application development

Spreadsheets are a major application in end-user computing, one of the fastest growing areas of computing. Studies have shown that 30% of spreadsheet applications contain errors. As major decisions are often made with the assistance of spreadsheets, the control of spreadsheet applications is a matter of concern to enduser developers, managers, EDP auditors and computer professionals. The application of appropriate controls to the spreadsheet development process requires prior categorisation of the spreadsheet application. The special-purpose A.D.E. (Application, Development, Environment) taxonomy of spreadsheet application development was evolved by mathematical taxonomic methods to categorise spreadsheet development projects to facilitate their management and control