I have a column or list called "Index", which contain numbers from 1 to n with an increment of 1.
I also have a column or list called "Number" which I want to populate based on the index column and the sequence number given in cell C3.
Now the sequence number is 5 – therefore want to populate index 1-5 in numbers column with 1, and for example index 16-20 with 4. If the sequence number was 10, then I would want to populate index 1-10 in numbers column with 1, and index 11-20 with 2.
Basically I want the number column to contain number, but increase with one after populating as many cells as the sequence number.
I need a formula in the number column that can do this for me.
>Solution :
Something like:
=CEILING(<INDEX>/n,1)
Where ” is your range of index-numbers and ‘n’ what you call the sequence-number. In your sample this would translate to =CEILING(B6:B38/C3,1) for versions of Excel that work with dynamic arrays and can spill the result in a single go, or =CEILING(B6/C3,1) for older versions which you need to drag down.