Filling cells in a number sequence with blanks inserted

[peteatlab]

Hello all,

I'm looking for a formula or a way to drag with the fill handle that will create a numbered series from 1 through 1,000, but that after every 15 numbers filled in, there are 10 blank cells that follow, before resuming the count to 1,000. So, the cells should go...


1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, <blank>,<blank>,<blank>,<blank>,<blank>,<blank>,<blank>,<blank>,<blank>,<blank>,16, 17, 18, 19, 20, etc....

Any thoughts would be greatly appreciated!
pete

[Marcol]

Try this
In A1

Please Login or Register  to view this content.

In A2

Please Login or Register  to view this content.

Drag/Fill Down as required.

Hope this helps.

[peteatlab]

Thanks for the reply!

That almost worked! Except that when the sequences starts again after the blanks, it goes back to 1-15, instead of carrying on with the sequence at 16, it starts back at 1. Any thoughts?

Thanks so much for the help so far!

pete

[Marcol]

My apologies, I didn't read the question properly ... .

In A1

Please Login or Register  to view this content.

In A2

Please Login or Register  to view this content.

Drag/Fill Down.

Hope that's better ...

[peteatlab]

Yes that worked! You are my hero!

If you don't at all mind, could you try to explain to me how this works? I've very curious!

pete

[Marcol]

Sorry for the delay in replying. Seems there was some more "Essential Management" going on with the Forum Database ... ...

1/. COUNT($A$1:$A1)
This counts the numbers in the range, it expands as you drag it down, so when you get to row 16 it counts the numbers in $A$1:$A15 i.e. 15

2/. At this point we have
MOD(COUNT($A$1:$A15),15) which translates as the remainder of COUNT($A$1:$A15) divided by 15 i.e. 0

3/. This is the TRUE part of the full formula i.e. IF(MOD(COUNTA($A$1:$A15),25)=0,MAX($A$1:$A15)+1,"")

So we now evaluate that
4/. MOD(COUNTA($A$1:$A15),25)=0
COUNTA($A$1:$A15) This counts the numbers and text in the range, (Formulas that return "" count as text)
So this translates as the remainder of COUNTA($A$1:$A15) divided by 25 i.e. 15 i.e. FALSE so "" (Blank) is returned

5/. Going now to Row 26
IF(MOD(COUNTA($A$1:$A25),25)=0 this now returns the True part of the IF() i.e. MAX($A$1:$A25)+1 i.e. 16

6/. If the above conditions are not met at any point then the if statement returns the preceding cell plus one, if the preceding cell is blank then a #vVALUE! error occurs which returns a blank.

I hate trying to put these bloody things into words ...


Try selecting a cell with the formula then put the cursor in the formula bar then click fx when the dialogue box is open you can change the position of the cursor in the formula bar and the relevant part of the equation will be displayed.

Use help on this function (bottom right) if you get stuck.

Hope this helps