Will Dynamic Arrays finally change the way Models are built?

TL;DR

This paper explores how the adoption of dynamic arrays in spreadsheets could transform model building by reducing manual updates and errors, potentially replacing traditional techniques in professional environments.

Contribution

It analyzes the potential of dynamic arrays to improve spreadsheet modeling accuracy and efficiency, proposing their use as a replacement for traditional methods.

Findings

Dynamic arrays reduce manual updates in spreadsheets.

They potentially lower error rates in models.

They enable more professional and reliable spreadsheet development.

Abstract

Spreadsheets offer a supremely successful and intuitive means of processing and exchanging numerical content. Its intuitive ad-hoc nature makes it hugely popular for use in diverse areas including business and engineering, yet these very same characteristics make it extraordinarily error-prone; many would question whether it is suitable for serious analysis or modelling tasks. A previous EuSpRIG paper examined the role of Names in increasing solution transparency and providing a readable notation to forge links with the problem domain. Extensive use was made of CSE array formulas, but it is acknowledged that their use makes spreadsheet development a distinctly cumbersome task. Since that time, the new dynamic arrays have been introduced and array calculation is now the default mode of operation for Excel. This paper examines the thesis that their adoption within a more professional development environment could replace traditional techniques where solution integrity is important. A major advantage of fully dynamic models is that they require less manual intervention to keep them updated and so have the potential to reduce the attendant errors and risk.