Filling down text in increments of three

[Josh W]

How can I fill a worksheet all the way down with the following:
A1=2 steps
A2=2 steps
A3=2 steps
A4=3 steps
A5=3 steps
A6=3 steps
A7=4 steps
A8=4 steps
A9=4 steps
And so on, each in increments of three, all the way down the sheet. I asked
the same question yesterday for numbers only without text and got some
excellent formulas for that. Today I specifically need to enter the number
and the text in the same cell - "4 steps". Thanks. Excel 2003

 

[Sam Wilson]

Do you want the text "2 Steps" to appear in A1 etc?

If so, put the formula =(INT((ROW(B1)-1)/3)+2)&" Steps"
in cell A1 and copy it down.

Sam

 

[Dave Curtis]

Hi,

Or just use whatever formula you were already using, and apply a custom
nuber format of

general" steps"

which will automatically add a space followed by the word steps to any
number in those cells.

Dave

 

[Josh W]

Indeed, Sam, I did want the full text. Your formula works wonders. Thanks

 

[Josh W]

Dave, that was an excellent tip. Thanks a mill.

Hi,

Or just use whatever formula you were already using, and apply a custom
nuber format of

general" steps"

which will automatically add a space followed by the word steps to any
number in those cells.

Dave

 

[Dave Curtis]

Hi,

Glad to help.
As a matter of interest, I use similar formulas to generate many different
series, and I have the following listed to generate the series you mentioned.

=ROUNDUP(ROW()/3,0)+1
=INT((ROW()-1)/3)+2
=CEILING(ROW()/3,1)+1
=ROUNDDOWN((ROW()-1)/3,0)+2
=FLOOR((ROW()+5)/3,1)
=MROUND(ROW()+1,3)/3+1
=CEILING(ROW(),3)/3+1

Dave

 

[Josh W]

Thanks Dave. It is very interesting. Do you have some good links where I can
learn more about creating my own formulas instead of having to ask each time?
Josh.

 

[Dave Curtis]

Hi Josh,

What sort of formulas are you looking for? For generating series, or other
things too?
I used to ask all the time on various forums, but eventually got better at
doing them myself. I also got together with some guys from here and
eventually built up a library of potentially useful formulas.

Dave