Trouble with skipping rows
 

OK. Here's the story. I have a large sheet of data containing around 10000
rows and around 60 columns. I have figured out a formula to skip every 195th
row in order to generate conversion factors. The data is grouped in the
following manner, and starts column A row 2:

Block Column Row
1 1 1
1 1 2
1 1 3

etc to

48 14 14

The formula I have to skip rows is:

=IF(MOD(ROW()+196*2,196)=2,MEDIAN(BE2:BE197),""), where column BE contains
values I need the median of. With this formula I can generate 48 median
factors, 1 for each block. Now, I need to apply each median conversion
factor to each column/row entry. I would like to be able to do this in
formula form, instead of typing
=U2*BF$2, dragging down for all 196 entries per block, then typing the
absolute reference for the next median value. I have tried address and
indirect, but I seem to be stuck in the decision making part. Any help is
appreciated.

wazooli

Try something like:

=U2*INDIRECT("BF"&2+196*(INT((ROW()-2)/196)))



"Wazooli" wrote:

OK. Here's the story. I have a large sheet of data containing around 10000
rows and around 60 columns. I have figured out a formula to skip every 195th
row in order to generate conversion factors. The data is grouped in the
following manner, and starts column A row 2:

Block Column Row
1 1 1
1 1 2
1 1 3

etc to

48 14 14

The formula I have to skip rows is:

=IF(MOD(ROW()+196*2,196)=2,MEDIAN(BE2:BE197),""), where column BE contains
values I need the median of. With this formula I can generate 48 median
factors, 1 for each block. Now, I need to apply each median conversion
factor to each column/row entry. I would like to be able to do this in
formula form, instead of typing
=U2*BF$2, dragging down for all 196 entries per block, then typing the
absolute reference for the next median value. I have tried address and
indirect, but I seem to be stuck in the decision making part. Any help is
appreciated.

wazooli