Implementing Nested FOR Loops as Spreadsheet Formulas
A FOR loop is a computing structure that allows a set of calculations to be made repeatedly for each iteration of the loop where the number of iterations is known in advance. A nested loop happens when a loop is inside another loop. In a spreadsheet program like Microsoft Excel, one can program loops in VBA, its programming language. Spreadsheet developers who do not know how to program in VBA usually implement the equivalent of loops with static values (e.g. region codes and product types are typed as constants) or with formulas (e.g. the region code is the previous region code + 1). In this paper, we present similarities and differences between programming loops and spreadsheet formulas loops. We also present a set of formulas that implement nested loops for 1, 2 or 3 nested levels, along with a generalization for deeper nesting levels. We also provide model management formulas to help the spreadsheet developer ensure that his spreadsheet model covers all the iterations.
- 2.Burden, T.: How a Rookie excel error led JPMorgan to misreport its VaR for years (2013). http://www.zerohedge.com/news/2013-02-12/how-rookie-excel-error-led-jpmorgan-misreport-its-var-years
- 3.Mouchel profits blow (2011). http://www.express.co.uk/finance/city/276053/Mouchel-profits-blow
- 4.Conczal, M.: Researchers finally replicated Reinhart-Rogoff, and there are serious problems. Roosevelt Institute (2013). http://rooseveltinstitute.org/researchers-finally-replicated-reinhart-rogoff-and-there-are-serious-problems/
- 7.Cunha, J., Fernandes, J.P., Mendes, J., Saraiva, J.: MDSheet: a framework for model-driven spreadsheet engineering. In: Presented at the Proceedings of the 34th International Conference on Software Engineering, Zurich, Switzerland (2012)Google Scholar
- 8.Mireault, P.: Structured spreadsheet modeling and implementation. In: 2nd Workshop on Software Engineering Methods in Spreadsheets, Firenze, IT (2015)Google Scholar
- 9.Mireault, P.: Structured Spreadsheet Modelling and Implementation: A Methodology for Creating Effective Spreadsheets. SSMI International, Montréal (2016)Google Scholar