Increment sequentially every third row

[Oscar Noriega]

Hello,

I"m trying to increment every third row sequentially by 1 and filling in the previous rows with the previous number.

Ex.
1
1
1
2
2
2
3
3
3

Your help would be greatly appreciated. Thank you.

Take care
Oscar

 

[NARAYANK991]

Hi Oscar ,

How far do you want this to happen ? Can you enter the starting number , and the end number , as well as the cell address from where this should start ? For example , A1 could contain the starting number , say 7 , A2 the ending number , say 13 , and A3 could contain a cell address such as J7 ; this should then fill up the column J with the numbers as follows :

J7 : 7
J8 : 7
J9 : 7

J10 : 8
J11 : 8
J12 : 8

.
.
.

J22 : 12
J23 : 12
J24 : 12

J25 : 13
J26 : 13
J27 : 13

Is this what you want ?

Narayan

 

[SirJB7]

Hi, Oscar Noriega!

As a new user you might want to (I'd say should and must) read this, but I assume you yet did it since have passed thru Introduce yourself:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, ry this in A2 and copy down as required:
=ENTERO((FILA()-1+2)/3) -----> in english: =INT((ROW()-1+2)/3)

I know you can optimize the formula to this:
=INT((ROW()+1)/3)
but the original has this general format:
=INT((ROW()-<title rows>+<no. of repetitions - 1>)/<no. of repetitions>)

Regards!

 

[sudipto3003]

hi SirJB7,

if i change "ROW" to "COLUMN" then will it work for column wise like this>111222333444...............
=INT((COLUMN()-1+2)/3)

 

[Somendra Misra]

@sudipto3003
@Oscar Noriega

Another formula can be below:
For row:
=INT((ROWS($A$1:A1)-1)/3+1)
For Column:
=INT((COLUMNS($A$1:A1)-1)/3+1)

In general, in formula the /3 decide the number of times you want the repetion. So, /4 will repeat for 4 times. and so on.

Regards,

 

[sudipto3003]

@Somendra Misra
great, now what if the increament will on % basis, say at the first row(A1) is 20000 and will be increase @15% each after third row/column.

 

[Somendra Misra]

@sudipto3003

For row:
In F1 put 20000 manually
In F2 Put formula & copy down =IF(MOD(ROWS($F$1:F1)-1,3)+1<3,F1,F1+(F1*15%))

For Column:
In F1 put 20000
In G1 put formula and copy right =IF(MOD(COLUMNS($G$1:G1)-1,3)+1<3,F1,F1+(F1*15%))

Just check them and advise if any issue.

Regards,

 

[sudipto3003]

thank you somendra

 

[Somendra Misra]

Welcome anytime.

 

[Oscar Noriega]

Thank you sirJB7 and others. Your solutions worked. I originally used the fill down functions but didn't work that well. I like the formula's better. I will evaluate them to see how they work to understand them and not just copy and paste and move on. I will like to become proficient in writing formulas in Excel it's a powerful tool to have.
Thanks for your help

I sign up yesterday and did introduced myself.

Take care,

 

[SirJB7]

if i change "ROW" to "COLUMN" then will it work for column wise like this>111222333444...............
=INT((COLUMN()-1+2)/3)

Hi, sudipto3003!
I think it will.
Regards!

 

[SirJB7]

Hi, Oscar Noriega!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!