In budgets, forecasts, financial models and even reporting models repeating the numbers 1 to 12 can be useful. The SEQUENCE and MOD functions can make it easy and scalable.

The solution I will work through will initially use helper cells and progress through to using the new LET function. SEQUENCE and LET are currently only available in the subscription version of Excel.

You can download the example file using the button at the bottom of the post.

SEQUENCE function

Let’s take the example of a three year financial model.

We need the numbers from 1 to 36 for the full model and then 1 to 12 for each year in the model.

The complication is we may want the model to be a five year model.

The SEQUENCE function provides the flexibility to change from three to five years. The MOD function simplifies creating a repeating series.

The image below show the 36 month sequence. The formula on cell D1 is

=SEQUENCE(1,B1*12)

This is a dynamic array function. This means it is only entered in one cell (D1) and it populates the values in other cells (D1:AM1) but there are no formulas in the other cells. If you select one of the other cells the formula is greyed out.

Change cell B1 to 5 and the sequence updates – see image below.

We can use the sequence in row 1 to create our series of repeating

numbers 1 to 12 for the months of the year. The formula in cell D2 that provides the series is

=IF(MOD(D1#,12)=0,12,MOD(D1#,12))

The reference to D1# means to refer to the dynamic array range created by the formula in cell D1. The # symbol is a new formula symbol used for referring to dynamic array ranges.

The MOD function provides an unusual result. It divides one number by another and it returns the remaining value, once the number has been evenly divided into the other number. So =MOD(7,5) would return 2 and =MOD(15,4) would return 3.

In our 1 to 12 case the MOD function will provide all the numbers we need except for the periods 12, 24 and 36 because the MOD returns zero as 12 divides evenly into those numbers. There is no remainder.

So the formula uses an IF function to identify those numbers and it returns 12 for them. It lets the MOD function handle the other numbers.

Standalone formula

Let’s say we wanted to create the 1 to 12 series without row 1. It is possible but the formula starts to expand. The formula in cell D1 in the image below is

=IF(MOD(SEQUENCE(1,B1*12),12)=0,12,MOD(SEQUENCE(1,B1*12),12))

Notice that the MOD and SEQUENCE function combination is repeated and the number 12 appears five times. That duplication is a good use case for the new LET function.

LET function

The LET function doesn’t perform any calculations, but it does allow you to set up variables to reduce formula repetition.

The LET version of the above function in cell D1 is shown below – sell also the image below.

=LET(m,12,x,MOD(SEQUENCE(1,B1*m),m),IF(x=0,m,x))

The LET function uses the m variable to capture the value of 12. The x variable captures the result of the MOD and SEQUENCE combination formula. Note the m variable was used in the definition of the x variable. Having captured these entries we then create a formula using the variables

Wherever we used 12 in the previous formula we can use the m variable and wherever we used the MOD and SEQUENCE combination we can use the x variable. So the IF function becomes the last argument (part) of the LET function. It has been radically shortened with the use of variables.

IF(x=0,12,x)

The LET function is handy when using an IF function to test a complex formula for a certain result and handle that result differently, as we did when the MOD and SEQUENCE combination returned a zero.

Download Example File