Formula to increment numbers 1,1,1,2,2,2,etc.

[mgirvin]

Dear Smartest Excelers In The World,

I would like a formula that I could copy down column A that would give me a column of numbers like this: 1;1;1;2;2;2;3;3;3;4;4;4 etc.

I have used formulas like this:

=MOD(ROWS(A$1:A1)-1,3)+1

to give me a column of numbers like this: 1;2;3;1;2;3;1;2;3 etc.

But I can not seem to think of one for 1;1;1;2;2;2 etc.

Any ideas?

 

[mhenk]

Dear Smartest Excelers In The World,

I would like a formula that I could copy down column A that would give me a column of numbers like this: 1;1;1;2;2;2;3;3;3;4;4;4 etc.

I have used formulas like this:

=MOD(ROWS(A$1:A1)-1,3)+1

to give me a column of numbers like this: 1;2;3;1;2;3;1;2;3 etc.

But I can not seem to think of one for 1;1;1;2;2;2 etc.

Any ideas?

Can't just type 1, 1, 1 in A1:A3, then A4 = A1+1, drag down?

 

[Jonmo1]

Try

=INT((ROW(A1)-1)/3)+1

or

=ROUNDUP(ROW(A1)/3,0)

 

[Aladin Akyurek]

Try

=INT((ROW(A1)-1)/3)+1

or

=ROUNDUP(ROW(A1)/3,0)

If you want to robustify...

=INT((ROW()-ROW($A$1))/3)+1

 

[Scott Huish]

Also:

=-INT(-ROWS($A$1:$A1)/3)

 

[dbrown]

Roundup(row()/3,0)

 

[mgirvin]

Thanks, mhenk, jonmo1, Aladin, HOTPEPPER, dbrown!!!

This definitely solves my problem!

However, formulas aside, they best thing that I learned from your answers is the new word:

Robustify!

I am adding this to vocabulary and I will use the word often! (I am even going to add it to my Spell Checker so that I do not get a red wiggle line under it!)

 

[jamilm]

If you want to robustify...

=INT((ROW()-ROW($A$1))/3)+1

This is the best trick i ever learned.