Generating a pattern using formula

  • Thread starter Thread starter anita1999
  • Start date Start date
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
(e-mail address removed).

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..
 
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
 
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...
 
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?
 
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
 
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...
 
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
 
Back
Top