 Highlighted
New Contributor

# Creation of formula

Hello everybody,

I need a formula that puts a column of numbers as following:

every number of the column should appear 8 times, instead of only one time. After that, in the column on the left, each of the 8 equal numbers should be numbered from 1 to 8.

It would be great, if you could help me, please!

Thank you.

3 Replies
Highlighted

# Re: Creation of formula

@chiarastmn Please have a look at the attached workbook. It generates sequences of numbers 1-8 in column E, depending on the count of numbers entered in column C. Then, in F1, you'll find a formula that needs to be copied down as far as needed in order to generate the list of numbers from C in groups of eight.

Highlighted

# Re: Creation of formula

@Riny_van_Eekelen thank you so, so much!

Highlighted

# Re: Creation of formula

This is a task that becomes trivial with Office 365.

First define a named formula 'k' that provides a zero base index

= SEQUENCE(8*ROWS(numbers),1,0)

The cycle of occurrence numbers is then given by

= 1+MOD(k,8)

whilst the blocks of numbers are returned by

= INDEX( numbers, 1 + QUOTIENT(k, 8)

If the source list is a table named 'numbers' the output grows dynamically as values are added to the table.  Why oh why wasn't Excel always like this?

ps Without SEQUENCE, 'k' could be defined by

= ROW( INDIRECT("1:"&8*N) ) - 1