Generating a pattern using formula

A

anita1999

I need to be more specific with the pattern in my previous thread.

I would like to generate the following but am having difficulty. The
following pattern needs to be in a single column. Please respond to
[email protected].

THanks

1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,....72,72,72,72,72
1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,....72,72,72,72,72
73,73,73,73,73,74,74,74,74,74....,144,144,144,144,144
73,73,73,73,73,74,74,74,74,74....,144,144,144,144,144
145,145,145,145,145,146,146,146,146,146....,216,216,216,216,216
etc..
 
C

CLR

Try this..........put a 1 in cells A1,A2,A3,A4,AND A5.......

Then in A6 put this formula and copy down as far as you want.........

=IF(AVERAGE(A1:A5)=A5,A5+1,A5)

Vaya con Dios,
Chuck, CABGx3
 
A

anita1999

Thanks for the response.

I tried the formula however it does not follow the pattern exactly a
it does not repeat the sets twice and do the increment.

I need it to be in groups of 2 and then it increments by 1. Fo
example, the pattern 1..72 gets repeated twice and then we increment t
73. Then the pattern 73...144 gets repeated twice and then i
increments to 145. All of this has to be in a single column. Do yo
think it's possible? I've been stuck on this since yesterda
afternoon. Thanks again for your quick response.
Anita

1,1,1,1,1,2,2,2,2,2.....upto 72,72,72,72,72
1,1,1,1,1,2,2,2,2,2.....upto 72,72,72,72,72
73,73,73,73,73,74,74,74,74,74.....144,144,144,144,144
73,73,73,73,73,74,74,74,74,74.....144,144,144,144,144
145,145,145,145,145,146,146,146,146,146...
 
M

MrShorty

Does it have to be a single formula to generate the pattern? I would do
it this way:

1) Enter 1 in A1 through A5.
2) A6=A1+1
3) select A6 and copy down to A360 (should get you to the 5th 72)
4) select A1:A360 and copy
5) paste in A361 (should generate the second set from 1 to 72).
6) paste again in A721 (should generate a third set from 1 to 72)
7) replace the 1's in A721 through A725 with 73's
8) repeat until you fill the column as full as you need it.

I realize it's a little more tedious than a single formula to do it
all, but it would still work.

Is that good enough or do you want to continue pushing for a single
formula to do it all.

With a minutes more thought:

After you get the first two sets from 1 to 72 in
6) A721=IF(a361=a1,a716+1,a361)
7) select A721, copy, and paste down.

I haven't tested this latter solution, but it seems like it should
work.

Does that help?
 
M

mangesh_yadav

Hi Anita,

Enter the following formula in cell A1 and copy down.
=IF(ROW()>(2*5*72),INDIRECT("A"&ROW()-2*5*72)+72,(IF(ROW()>(5*72),INDIRECT("A"&ROW()-5*72),(INT((ROW()-1)/5)+1))))

Note: I haven't tried it in any other cell as I use row referencing. I
would need to add some more stuff to this formula to make it work at
rows other than 1. Just wanted to know if this is what you want.


Mangesh
 
M

Mangesh Yadav

Thanks you :)

Mangesh



CLR said:
Beyond brilliance!..........that is such a cool thing..........way to go
Mangesh!!!


Vaya con Dios,
Chuck, CABGx3



"mangesh_yadav"
Click to expand...
wrote in message
Hi Anita,

Enter the following formula in cell A1 and copy down.
Click to expand...
=IF(ROW()>(2*5*72),INDIRECT("A"&ROW()-2*5*72)+72,(IF(ROW()>(5*72),INDIRECT("
Click to expand...
 
M

Mangesh Yadav

Here's a generalised formula:

=IF(ROW()-(ROW($A$2)-1)>(2*5*72),INDIRECT("A"&ROW()-(ROW($A$2)-1)-2*5*72)+72
,(IF(ROW()-(ROW($A$2)-1)>(5*72),INDIRECT("A"&ROW()-(ROW($A$2)-1)-5*72),(INT(
(ROW()-(ROW($A$2)-1)-1)/5)+1))))


You need to put the starting cell instead of ROW($A$2) in the above
formula to work it anywhere. Also All 'A's should be replaced by the
respective column number for it to work in columns other than 'A'.


Mangesh
 
Top