Fill down will a series 1 2 2 3 3 3 etc

**andrewljones2000** · 03-20-2009, 06:28 AM

Hi,

i am trying to fill down with the below series so i get a column that looks like the following:

1
2
2
3
3
3
4
4
4
4
5
5
5
5
5

is there an easy way of doing this as i need to get it down to 400! so to manually type would take forever.

Any help would be much appreciated.

AJ.

**DonkeyOte** · 03-20-2009, 06:43 AM

If you're running 2003 you won't have a sufficient number of rows available to actually list the list... I believe you would need 80200 rows to list 1-400 with each digit repeated according to it's own value (ie 1row + 2row + 3row + 4row ... + 400row)

**andrewljones2000** · 03-20-2009, 06:49 AM

i am using 2007 so should have enough rows but just trying to figure out how to do it! its got me stumped.

**DonkeyOte** · 03-20-2009, 06:51 AM

[removed - hideously inefficient - see subsequent post]

**DonkeyOte** · 03-20-2009, 06:54 AM

In terms of formulae this would be a little quicker to calc I think...

A1 = 1
A2: =A1+(OFFSET(A2,A1*-1,)=A1)
copied down to A80200

Once calculated copy paste values A1:A80200 to dispose of formulae... OFFSET is Volatile so you don't want to keep them in your file.

**andrewljones2000** · 03-20-2009, 06:56 AM

Your a life saver thanks, i might just do it to 200 rather than 400 as your right ... i tried it wilth 400 and excel ceased operation!!!

Once again thanks
AJ.

**martindwilson** · 03-20-2009, 07:04 AM

=FLOOR(0.5+SQRT(0.25-2*(1-ROW())),1) from row 1
this is non volatile

**daddylonglegs** · 03-20-2009, 07:12 AM

You could avoid OFFSET and use this formula in A2 copied down

=A1+(ROWS(A$1:A2)>(1+A1)/2*A1)

**DonkeyOte** · 03-20-2009, 07:14 AM

Hi Martin, some would/have argued that ROW is Volatile -- I asked a couple of MS Excel MVP's about this as I've heard that documentation suggest it is Volatile but reality would imply it is not so, ie does not appear to "act" as though it's Volatile... one of those odd ones....I try to use ROWS eg ROWS($A$1:$A1) instead wherever possible.