An Investigation of the Incidence and Effect of Spreadsheet Errors Caused by the Hard Coding of Input Data Values into Formulas

TL;DR

This paper investigates how hard coding input data into spreadsheet formulas causes errors, examines its recognition among academics and practitioners, and presents an automated detection method with analysis results.

Contribution

It introduces an automated tool for detecting hard coding in spreadsheets and analyzes its effectiveness on various models, highlighting the significance of this design flaw.

Findings

Hard coding is common and often overlooked in spreadsheets.

Automated detection can identify hard coding errors effectively.

Hard coding errors can lead to significant spreadsheet inaccuracies.

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.