{=AVERAGE(IF((MOD(ROW(CLM10000!C3:C646),7)=3)*(C3: C646<""),C3:C646))}

I have this entered in cell Adjust PtsC1. As I understand it it gives the
average of a column using the data in every 7th row, starting with the 3rd
row. It seems to work well, but my problem is when I try to drag it down
(even with Ctrl+Drag).

I've changed the formula
{=AVERAGE(IF((MOD(ROW(CLM10000!C3:C646),7)=4)*(C3: C646<""),C3:C646))}, so it
should use every 7th row starting with the 4th row. Is there a trick to
copying, pasting or moving Array formulas?

Any help? Thanks!

How far down do you want to copy it?

You can have the MOD comparison increment from 3 to 4 then 5, 6. When it
increments to 7 or greater you'll get errors.

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
{=AVERAGE(IF((MOD(ROW(CLM10000!C3:C646),7)=3)*(C3: C646<""),C3:C646))}

I have this entered in cell Adjust PtsC1. As I understand it it gives the
average of a column using the data in every 7th row, starting with the 3rd
row. It seems to work well, but my problem is when I try to drag it down
(even with Ctrl+Drag).

I've changed the formula
{=AVERAGE(IF((MOD(ROW(CLM10000!C3:C646),7)=4)*(C3: C646<""),C3:C646))}, so
it
should use every 7th row starting with the 4th row. Is there a trick to
copying, pasting or moving Array formulas?

Any help? Thanks!

Thats exactly where I'm at. I need to be able to get to 7.

"T. Valko" wrote:

How far down do you want to copy it?

You can have the MOD comparison increment from 3 to 4 then 5, 6. When it
increments to 7 or greater you'll get errors.

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
{=AVERAGE(IF((MOD(ROW(CLM10000!C3:C646),7)=3)*(C3: C646<""),C3:C646))}

I have this entered in cell Adjust PtsC1. As I understand it it gives the
average of a column using the data in every 7th row, starting with the 3rd
row. It seems to work well, but my problem is when I try to drag it down
(even with Ctrl+Drag).

I've changed the formula
{=AVERAGE(IF((MOD(ROW(CLM10000!C3:C646),7)=4)*(C3: C646<""),C3:C646))}, so
it
should use every 7th row starting with the 4th row. Is there a trick to
copying, pasting or moving Array formulas?

Any help? Thanks!

Well, you can't get to 7 with that formula. The MOD function is used to
count the rows in increments of 7. Everytime the 7th row is reached the mod
restarts at 0 and starts to count "up" again. Like this:

3,4,5,6,0,1,2,3,4,5,6,0,1,2,3,4,5,6,0

If you want the same pattern, every 7th row, *starting* from the 3rd row and
then incrementing as you drag down, maybe this:

=AVERAGE(IF((MOD(ROW(CLM10000!C3:C$646),7)=3)*(C3: C$646<""),C3:C$646))

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Thats exactly where I'm at. I need to be able to get to 7.

"T. Valko" wrote:

How far down do you want to copy it?

You can have the MOD comparison increment from 3 to 4 then 5, 6. When it
increments to 7 or greater you'll get errors.

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
{=AVERAGE(IF((MOD(ROW(CLM10000!C3:C646),7)=3)*(C3: C646<""),C3:C646))}

I have this entered in cell Adjust PtsC1. As I understand it it gives
the
average of a column using the data in every 7th row, starting with the
3rd
row. It seems to work well, but my problem is when I try to drag it
down
(even with Ctrl+Drag).

I've changed the formula
{=AVERAGE(IF((MOD(ROW(CLM10000!C3:C646),7)=4)*(C3: C646<""),C3:C646))},
so
it
should use every 7th row starting with the 4th row. Is there a trick to
copying, pasting or moving Array formulas?

Any help? Thanks!

It figures that the last row I need would be the problem. When I tried your
suggestion it gives me a circular reference. I think this is because of this
=IF(C11,INT(C1/100)*60+MOD(C1,100),"") which is in cell S1. It changes the
time from say 111.00 to 71 seconds. So if there were a way to incorporate
{=AVERAGE(IF((MOD(ROW(CLM10000!$C$3:$C$640),7)=3)* (CLM10000!$C$3:$C$640<""),CLM10000!$C$3:$C$640))}
with ,=IF(C11,INT(C1/100)*60+MOD(C1,100),"") it might help.

Is that possible?

"T. Valko" wrote:

Well, you can't get to 7 with that formula. The MOD function is used to
count the rows in increments of 7. Everytime the 7th row is reached the mod
restarts at 0 and starts to count "up" again. Like this:

3,4,5,6,0,1,2,3,4,5,6,0,1,2,3,4,5,6,0

If you want the same pattern, every 7th row, *starting* from the 3rd row and
then incrementing as you drag down, maybe this:

=AVERAGE(IF((MOD(ROW(CLM10000!C3:C$646),7)=3)*(C3: C$646<""),C3:C$646))

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Thats exactly where I'm at. I need to be able to get to 7.

"T. Valko" wrote:

How far down do you want to copy it?

You can have the MOD comparison increment from 3 to 4 then 5, 6. When it
increments to 7 or greater you'll get errors.

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
{=AVERAGE(IF((MOD(ROW(CLM10000!C3:C646),7)=3)*(C3: C646<""),C3:C646))}

I have this entered in cell Adjust PtsC1. As I understand it it gives
the
average of a column using the data in every 7th row, starting with the
3rd
row. It seems to work well, but my problem is when I try to drag it
down
(even with Ctrl+Drag).

I've changed the formula
{=AVERAGE(IF((MOD(ROW(CLM10000!C3:C646),7)=4)*(C3: C646<""),C3:C646))},
so
it
should use every 7th row starting with the 4th row. Is there a trick to
copying, pasting or moving Array formulas?

Any help? Thanks!

If you were to give us a more detailed explanation of what cells you want to
average it would help.

Using your original formula you want to average rows 3, 10, 17, 24, 31 etc.

Now you want to copy the formula down. What rows do you want averaged for
this? And how many rows down will the formula be copied to? Is there any
unique identifier to what rows need averaged? Maybe something like a date.
I'm guessing that since you're working in increments of 7 this is for
weekdays or weekly data? So, you want the average of all Mondays? If there
were an identifier like this it would be much easier than calculating
offsets using MOD(ROW()).

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
It figures that the last row I need would be the problem. When I tried
your
suggestion it gives me a circular reference. I think this is because of
this
=IF(C11,INT(C1/100)*60+MOD(C1,100),"") which is in cell S1. It changes
the
time from say 111.00 to 71 seconds. So if there were a way to incorporate
{=AVERAGE(IF((MOD(ROW(CLM10000!$C$3:$C$640),7)=3)* (CLM10000!$C$3:$C$640<""),CLM10000!$C$3:$C$640))}
with ,=IF(C11,INT(C1/100)*60+MOD(C1,100),"") it might help.

Is that possible?

"T. Valko" wrote:

Well, you can't get to 7 with that formula. The MOD function is used to
count the rows in increments of 7. Everytime the 7th row is reached the
mod
restarts at 0 and starts to count "up" again. Like this:

3,4,5,6,0,1,2,3,4,5,6,0,1,2,3,4,5,6,0

If you want the same pattern, every 7th row, *starting* from the 3rd row
and
then incrementing as you drag down, maybe this:

=AVERAGE(IF((MOD(ROW(CLM10000!C3:C$646),7)=3)*(C3: C$646<""),C3:C$646))

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Thats exactly where I'm at. I need to be able to get to 7.

"T. Valko" wrote:

How far down do you want to copy it?

You can have the MOD comparison increment from 3 to 4 then 5, 6. When
it
increments to 7 or greater you'll get errors.

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
{=AVERAGE(IF((MOD(ROW(CLM10000!C3:C646),7)=3)*(C3: C646<""),C3:C646))}

I have this entered in cell Adjust PtsC1. As I understand it it
gives
the
average of a column using the data in every 7th row, starting with
the
3rd
row. It seems to work well, but my problem is when I try to drag it
down
(even with Ctrl+Drag).

I've changed the formula
{=AVERAGE(IF((MOD(ROW(CLM10000!C3:C646),7)=4)*(C3: C646<""),C3:C646))},
so
it
should use every 7th row starting with the 4th row. Is there a trick
to
copying, pasting or moving Array formulas?

Any help? Thanks!

Hi

Isn't your problem just that you have not made the references absolute
{=AVERAGE(IF((MOD(ROW(CLM10000!$C$3:$C$646),7)=4)* ($C$3:$C$646<""),$C$3:$C$646))}
--
Regards

Roger Govier


"M.A.Tyler" <Great Lakes State wrote in message
...
{=AVERAGE(IF((MOD(ROW(CLM10000!C3:C646),7)=3)*(C3: C646<""),C3:C646))}

I have this entered in cell Adjust PtsC1. As I understand it it gives
the
average of a column using the data in every 7th row, starting with the
3rd
row. It seems to work well, but my problem is when I try to drag it
down
(even with Ctrl+Drag).

I've changed the formula
{=AVERAGE(IF((MOD(ROW(CLM10000!C3:C646),7)=4)*(C3: C646<""),C3:C646))},
so it
should use every 7th row starting with the 4th row. Is there a trick
to
copying, pasting or moving Array formulas?

Any help? Thanks!