How to drag down numerical value but repeat 2 rows

Hi all,

I am trying to drag a numerical value down (X4-X10) but I need it to repeat two rows in between and simultaneously keep the numbering in sequence only after the repeats 2 rows in between . So far, I have to manually enter or when I select cell X4 and X5 together ( having a value of 1 and 2 respectively) and drag, the value does not repeat 2 rows in between and does not keep the numbering in sequence after the 2 rows.

At Present:
X4 1
X5 2
X6 3
X7 4
X8 5
X9 6
X10 7

I need to have

X4 1
X5 1
X6 1
X7 2
X8 2
X9 2
X10 3
X11 3
X12 3

and so on


I have a 2000+ rows to manually input and hence it is not practical!. Please advise!

Do you want a simple formula that adds 1 to the value above, once there are three of that umber?

Then try the following in column A. Type three 1's first, then follow with the formula, this can be dragged down as far as you like...
1
1
1
=IF(COUNTIF(A2:A4,A4)=3,A4+1,A4)
=IF(COUNTIF(A3:A5,A5)=3,A5+1,A5)
=IF(COUNTIF(A4:A6,A6)=3,A6+1,A6)
=IF(COUNTIF(A5:A7,A7)=3,A7+1,A7)
=IF(COUNTIF(A6:A8,A8)=3,A8+1,A8)
=IF(COUNTIF(A7:A9,A9)=3,A9+1,A9)

Simple!

Have a great day...

Or, you could input:
=ROUNDUP(ROW()/3,0)

or, if you are not wanting your numbering to start counting from row 1, put a moderator in as follows:

=ROUNDUP((ROW()-4)/3,0)

This will start counting the first of the 1's from Row 5 (note the extra set of brackets around 'ROW()' and '-4'...

Chiz

Rob-Can-Do

Thanks for the replies! However, the solutions are inadequate as i need the formula to work again if i put paste the proposed excel formula into the cell later in the column. . I will repost this thread with the added description.

Hi all, i need the formula =INT((ROW()-3)/3)+1 to start from 1 and repeat two rows in between and simultaneously keep the numbering in sequence only after the repeats 2 rows in between when i enter into any other row like X85 or x198 and not just X4.

Please advise!

i tried. It worked well for the first entry. However when i re-tried the formula in X86 it the sequencing carried on frm X85.. The crux is, i need the sequencing to restart from the value 1 when i enter the solution into x86, AND also in other rows as i wish in the column.

Any advice is greatly appreciated!

Thanks for the time! i will be reposting this problem. Kindly look at it if you can spare more time.

So you mean if there are a few blank rows, you need the numbering to continue where you left off? And not to be related to the row number where the formula is entered?

Put this in a column and it ignores blank cells and text between numerical values:

Type 1's on the first three rows:
1
1
1

Then paste this formula in as many or as few of the following cells as you like:

=IF(COUNTIF(X$1:X6,MAX(X$1:X6))=3,1,0)+MAX(X$1:X6)
'2923.30
=IF(COUNTIF(X$1:X8,MAX(X$1:X8))=3,1,0)+MAX(X$1:X8)
=IF(COUNTIF(X$1:X9,MAX(X$1:X9))=3,1,0)+MAX(X$1:X9)
=IF(COUNTIF(X$1:X10,MAX(X$1:X10))=3,1,0)+MAX(X$1:X10)
<<Blank cell as below>>

=IF(COUNTIF(X$1:X13,MAX(X$1:X13))=3,1,0)+MAX(X$1:X13)
<<Any characters>>
<<Any text>>
=IF(COUNTIF(X$1:X16,MAX(X$1:X16))=3,1,0)+MAX(X$1:X16)
=IF(COUNTIF(X$1:X17,MAX(X$1:X17))=3,1,0)+MAX(X$1:X17)
Any number formatted as text.
=IF(COUNTIF(X$1:X19,MAX(X$1:X19))=3,1,0)+MAX(X$1:X19)
="Any other value or formula that returns a non-numeric value"