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.

CodePudding user response：

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.

CodePudding user response：

I got what I wanted by @JvdV's formula suggestions CEILING().

In this case I did

=CEILING(indexcolumn/sequencenumber,1)

or

=CEILING(B6:B38/C3,1)