Advertisement

Implementing Nested FOR Loops as Spreadsheet Formulas

  • Paul Mireault
Conference paper
Part of the Lecture Notes in Computer Science book series (LNCS, volume 9946)

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.

References

  1. 1.
    Panko, R.R.: What we know about spreadsheet errors. J. End User Comput. 10, 15–21 (2008)CrossRefGoogle Scholar
  2. 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. 3.
  4. 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/
  5. 5.
    Jones, S.P., Blackwell, A., Burnett, M.: A user-centred approach to functions in excel. ACM Sigplan Not. 38, 165–176 (2003)CrossRefGoogle Scholar
  6. 6.
    Erwig, M.: Software engineering for spreadsheets. IEEE Softw. 26, 25–30 (2009)CrossRefGoogle Scholar
  7. 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. 8.
    Mireault, P.: Structured spreadsheet modeling and implementation. In: 2nd Workshop on Software Engineering Methods in Spreadsheets, Firenze, IT (2015)Google Scholar
  9. 9.
    Mireault, P.: Structured Spreadsheet Modelling and Implementation: A Methodology for Creating Effective Spreadsheets. SSMI International, Montréal (2016)Google Scholar

Copyright information

© Springer International Publishing AG 2016

Authors and Affiliations

  1. 1.SSMI InternationalMontréalCanada

Personalised recommendations