Authors
Larry J. LeBlanc, Michael R. Bartolacci, & Thomas A. Grossman
Abstract
Operations research practitioners often write spreadsheet software that is used, modified, and transferred to other people over time. They need techniques that enable them to quickly write error-free code whose accuracy can be easily verified and tested.
Practitioner spreadsheet models often must be suitable for transfer to others and be robust in the sense that inadvertently introducing errors during reuse and updating is difficult.
We examine some problem areas for spreadsheet design and programming and suggest 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.
Sample
This beastly formula contains 12 IF functions, 12 logical test formulas, and 12 result formulas.
It is difficult to verify because one must inspect 12 logical tests, 12 results, and the nesting of the IF functions, all embedded in a single long formula.
It is hard to test because the result value computed and displayed is state dependent.
Transfer is risky because any new user would find it challenging to comprehend, much less maintain, such a complex piece of code.
We recommend using a lookup function instead of complex nested-IF logic.
Publication
2017, Interfaces, Volume 47, Issue 3, May-June, pages 260-269
Full article
Increasing productivity and minimizing errors in operations research spreadsheet models
