Part 2 of this article series covered the key functions required for repeatable calculations. The third part of this series will explore a formula for creating labels in a model.
You can refer to our example Excel file here.
The formula for creating the labels is as follows:
=LET(counter, MOD(SEQUENCE(Scenarios * Block) - 1, Block) + 1, IF(counter > Calcs, "", MAP(counter, LAMBDA(x, OFFSET(Labels, x,)))))
It might look horrific at first glance, but really, it isn't. Note I have used the convention that "formula level range names" defined by the LET function which will not be recognised outside of the LET function are lower case (eg, counter), whereas regular range names start with a capital letter (eg, ;Scenarios, Block). This theoretically makes range names easier to follow.
We have already noted we require a counter block of Scenarios * Block rows (in our example, this will be 60). SEQUENCE(Scenarios * Block) will generate this. Then, the formula
MOD(SEQUENCE(Scenarios * Block) - 1, Block) + 1
will generate the array vector:
{1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6}
for a corresponding set of 60 values. LET simply names this sequence as counter. If the value selected is greater than the number of rows in the calculation block (defined as Calcs, with a present value of four [4]), the result will be blank (“”), otherwise the formula
MAP(counter, LAMBDA(x, OFFSET(Labels, x,)))
is used. MAP LAMBDA is a great formula combination sequence to add to your modelling kitbag. This maps the parameter x in the LAMBDA value to the counter, which rotates through the integer values one [1] through six [6] a total of 10 times. For each value, OFFSET(Labels, counter,) is returned. This isn't going to work directly on its own (try it and see) – hence the need for this "MAP LAMBDA" trick.
The range name Labels simply refers to the cell directly above the labels for the Control Account, viz.
This will then generate a repeated cycle of the four descriptions with two blank rows before the next repetition.
The next part of this series will explore how to use dynamic arrays in the main formula of the model.
Further resources
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.