Trying to create a repeating list of numbers

[Jinjunwei]

Hello Everyone.

Im trying to create a list of repeating numbers excel. For example, I'd like to have a column (say column A) count up: 1,2,3,4,5....1000. Then in cell A1001, I'd like the count to revert back to 1. I did a lot of searching and found the following formula:

A1: =(IF(MOD((ROW()-ROW(List_start)+1)/$D$1, 1)=0, 1, MOD((ROW()-ROW(List_start)+1)/$D$1, 1)))*$D$1

where D1 is the number i'd like to repeat, in my case, 1000. I can't get the formula to work. Any advice?

Thank you in advance.

Brian

 

[shg]

In A1:A1000, enter 1000

In A1001 and copy down, =A1+1000

 

[Rick Rothstein]

Hmm. I tired both of the above.. and just got a count down: a1: 1000, a2: 999, a3: 998. Maybe I made a mistake?

My mistake... I just skimmed your question and made a (bad) assumption as to what you wanted. Sorry. Try putting this formula in A1 and copying it down...

=INT((999+ROW())/1000)*1000

 

[Scott Huish]

=-INT(-ROW()/1000)*1000

Copy down.

 

[Jinjunwei]

My mistake... I just skimmed your question and made a (bad) assumption as to what you wanted. Sorry. Try putting this formula in A1 and copying it down...

=INT((999+ROW())/1000)*1000

Tada. Wow.. that is it. Thanks!

 

[Jinjunwei]

=-INT(-ROW()/1000)*1000

Copy down.

I got this to work too. Thanks so much!

 

[Jinjunwei]

In A1:A1000, enter 1000

In A1001 and copy down, =A1+1000

This works too. But why? It seems so simple.. I understand why cells a1001 would be 2000.. but why does this formula result in cell a3001 being 3000?

 

[shg]

It is simple. The first formula refers to a cell 1000 rows above, and the first 1000 cells are constants.

 

[Jinjunwei]

It is simple. The first formula refers to a cell 1000 rows above, and the first 1000 cells are constants.

You're right. That is simple. Thanks.