Creating automatically series of repeated sequential numbers in Excel

Even though excel can be very handy for many user-types and for many different applications, there are some cases where it seems that its developers were not carrying much about the built-in functionalities. As a result, they probably considered that all user are able to produce small pieces of code or are born with programming way of thinking, which obviously is not the case in our everyday life. After this small introduction, let's go straight to the point:

I'll summarise here three different ways of producing automatically and with very little effort a series of repeated sequential numbers. What does that mean exactly?
- Sequential numbers: Even though it is self-explanatory, obviously we're talking about a series of (user specified) numbers, that are within a specified interval,and there are no numbers missing in the sequense. e.g. 0,1,2,3,4,5,6,7,8,9 is a series of 10 sequential numbers in [0,9]

- Repeated: with this we imply that each number is repeated X times before we move to the next digit. In our example, if we wanted to repeat X=3 times, our series of repeated sequential numbers is 0,0,0,1,1,1,2,2,2,3,3,3,4,4,4,5,5,5,6,6,6,7,7,7,8,8,8,9,9,9.

Obviously, if you have such a limited series, you don't need any kind of automation, you just go to the related excel cells and fill in the numbers, period. BUT what do you do if you have a series of 85 numbers that are repeated 10.000 times? Obviously, you cannot fill in those 850.000! lines manually.

While struggling to find a solution and with quite some time spent on the internet, i've figured out the following three solutions, in order of preference for their use: 

• using the command 'MOD', which provides the remainder of a number when it's divided with a specific denominator, nested in an 'IF' conditional.
• using the command 'INT', which rounds downwards a given number, to the closest integer.
• using an 'IF' conditional formula.

In detail, we will use as an example a series with 20 numbers repeated for 10 times (remember: how many times these numbers are repeated is irrelevant to the formulas needed to input in excel). It's easier to just calculate those numbers in a separate sheet, then copy only the values to your destination cells, and discard the formulas:

1.  For the first solution we follow these steps:
   - Insert the first number, e.g. 0, in our first cell, e.g. A1
   - Insert in the cell below, e.g. A2, the following command: =IF(MOD(ROW(),20)=1,A1+1,A1)
   - Drag down the formula to the desired number of cells, e.g. till A200 in our example.
   - Obviously, the only adjustable parameter is the number '20' which can be set according to the needs of the user.
2. For the second solution we follow this step:
   - Insert in the first cell, e.g. A1, the command: =INT((ROW()+19)/20)
   - Drag down the formula to the desired number of cells, e.g. till A200 in our example.
   - In this case, the adjustable parameters are obvious, '19' and '20'.
3. For the third solution we follow these steps:
   - Insert a zero (0) in each cell of A1:A20 (twenty cells)
   - Insert in the cell below, e.g. A21, the following formula: =IF(A20=E1,A20+1,A20)
   - Drag down the formula to the desired number of cells, e.g. till A200 in our example.
   
   - This solution is obviously harder to update and the least effective of the three, but i kept it in the list for the crazy ones that may prefer it.

Regarding the first two approaches, they work equally well (like a charm) and have saved me plenty of precious time.

This way of producing repeatable sequential numbers can be very handy for anyone working on Monte Carlo methods, uncertainty analysis and/or sensitivity analysis of models as well as for any other data handling purpose. It is well expected that anyone could argue and point out "Why work on excel for a Monte Carlo application instead of Matlab or R" and I could immediately agree. But this discussion is out of the scope of my post.

For any questions or remarks, feel free to leave a comment or contact me by email.