Authors
Thomas A. Grossman, Ozgur Ozluk, & Jan Gustavson
Abstract
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:
- Make logical tests visible.
- Make outcomes visible.
- Make logical structure visible.
- Replace a multi-function nested-
IFformula 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.
Sample
We replace the complex, multi-function nested-IF cell formula with a simple, single-function VLOOKUP cell formula.
The single nested-IF cell has been replaced by 12 cells. Five cells report the value of the five logical tests. Six cells report the value of the six outcomes. One cell contains the structure (and only the structure) of the contingent logic.
Publication
2009, EuSpRIG
Full article
The lookup technique to replace nested-IF formulas in spreadsheet programming
