Abstract
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.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
References
Panko, R.R.: What we know about spreadsheet errors. J. End User Comput. 10, 15–21 (2008)
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
Mouchel profits blow (2011). http://www.express.co.uk/finance/city/276053/Mouchel-profits-blow
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/
Jones, S.P., Blackwell, A., Burnett, M.: A user-centred approach to functions in excel. ACM Sigplan Not. 38, 165–176 (2003)
Erwig, M.: Software engineering for spreadsheets. IEEE Softw. 26, 25–30 (2009)
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)
Mireault, P.: Structured spreadsheet modeling and implementation. In: 2nd Workshop on Software Engineering Methods in Spreadsheets, Firenze, IT (2015)
Mireault, P.: Structured Spreadsheet Modelling and Implementation: A Methodology for Creating Effective Spreadsheets. SSMI International, Montréal (2016)
Author information
Authors and Affiliations
Corresponding author
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2016 Springer International Publishing AG
About this paper
Cite this paper
Mireault, P. (2016). Implementing Nested FOR Loops as Spreadsheet Formulas. In: Milazzo, P., Varró, D., Wimmer, M. (eds) Software Technologies: Applications and Foundations. STAF 2016. Lecture Notes in Computer Science(), vol 9946. Springer, Cham. https://doi.org/10.1007/978-3-319-50230-4_31
Download citation
DOI: https://doi.org/10.1007/978-3-319-50230-4_31
Published:
Publisher Name: Springer, Cham
Print ISBN: 978-3-319-50229-8
Online ISBN: 978-3-319-50230-4
eBook Packages: Computer ScienceComputer Science (R0)