Authors
Paul J. Blayney
Abstract
The hard coding of input data or constants into spreadsheet formulas is widely recognised as poor spreadsheet model design.
However, the importance of avoiding such practice appears to be underestimated perhaps in light of the lack of quantitative error at the time of occurrence and the recognition that this design defect may never result in a bottom-line error.
The paper examines both the academic and practitioner view of such hard coding design flaws. The practitioner or industry viewpoint is gained indirectly through a review of commercial spreadsheet auditing software.
The development of an automated (electronic) means for detecting such hard coding is described together with a discussion of some results obtained through analysis of a number of student and practitioner spreadsheet models.
Sample
The Automated Hard Coding Identification Method (AHCIM) identifies formulas that contain any constants.
This table provides a summary view of the AHCIM analysis of a practitioner spreadsheet model. This highly complex model consists of 18 separate worksheets and over 50,000 formulas containing more than 14,000 hard coded constants.
Investigation of the meaning of the 'magic numbers' (e.g. 236, 259, 279) is certainly warranted and could potentially reveal a serious problem.
Publication
2006, EuSpRIG
