Auto-fill numbers in groups of 4

V

vtrud

I would like to fill a column with progressive numbers in sets of 4 each.
For instance, A1 - A4 will have 1000 in them then A5-A8 will have 1001 and so
on.

Ideas?
Thx, V
 
J

Jim Thomlinson

One way...

In A1 1000
In A2:A4 =A$1
In A5 =A1+1
Drag the formula in A5 down as necessary.
 
V

vtrud

Yep, I get how to do that but I don't want to do this all the way up to 2000.
Easier way to auot fill or something?
 
C

Chip Pearson

In the first cell of the list, enter your starting number, e.g., 1000.
Then, in the cell below that, enter

=$A$5+TRUNC((ROW()-ROW($A$5))/4,0)

Change $A$5 to the reference to the first cell in which you entered
1000. Then, copy this formula down for as many rows as you need.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
J

Jim Thomlinson

It is just dragging the formula in A5. You do nt need to write any more
formulas than what is posted.
 
J

Jim Thomlinson

I do not have a problem with the formula posted but it is good to note that
this formula includes Row which is a volatile function. That means that it
not very efficient in terms of calculation. If you have thousands of these
formulas you could see a performance hit when the spreadsheet calculates. It
is a judgement call as to whether it is a problem or not.
 
V

vtrud

Jim,
I don't know what I did the first time that didn't work, but it is working
now. And thank you for this tip too.
Have a great weekend.
 
G

Gord Dibben

Doesn't take too long to drag A5 down to A2000.

But you can select A5 and in the namebox type A5:A2000>ENTER

Then CTRL + D to copy down.


Gord Dibben MS Excel MVP
 
V

vtrud

Okay, now Jim and Gord are my most favorite people in the land! You have
made my day!
THX.
 
Top