Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average,Countif
{=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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average,Countif
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average,Countif
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average,Countif
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average,Countif
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average,Countif
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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average,Countif
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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average,Countif
OK. Column B is set up like this:
ALB 1st 2nd 3rd 4th AP 1st 2nd 3rd 4th ASD 1st 2nd 3rd 4th Each identifier has 4 subsequent sub-catagories, labled 1st, 2nd, 3rd, 4th. What I need to accomplish is first calculating an average of all the identifiers (ALB+AP+ASD/3) not counting any 0 or empty cells. Then repeat for the 1st's, 2nd's and so on. Since all of the sub-catagories are the same the only way to identify is by the idendtifier thus the Mod(row()). I'm not stuck on doing it this way, open to suggestiongs. Thanks, MT. "T. Valko" wrote: 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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average,Countif
Hi
Why not create a helper column, I used column D In the 1st cell of the helper column enter =B2&"0" in the second cell enter =IF(ISNUMBER(--(LEFT(B2))),LEFT(C1,LEN(C1)-1)&LEFT(B2),B2&"0") and copy down You will now have ALB0, ALB1, ALB2, ALB3,ALB4,AP0, AP1 etc. Now you can just use {=AVERAGE(IF($C$1:$C$646=$C1,$D$1:$D$646,""))} Adjust ranges to suit. -- Regards Roger Govier "M.A.Tyler" <Great Lakes State wrote in message ... OK. Column B is set up like this: ALB 1st 2nd 3rd 4th AP 1st 2nd 3rd 4th ASD 1st 2nd 3rd 4th Each identifier has 4 subsequent sub-catagories, labled 1st, 2nd, 3rd, 4th. What I need to accomplish is first calculating an average of all the identifiers (ALB+AP+ASD/3) not counting any 0 or empty cells. Then repeat for the 1st's, 2nd's and so on. Since all of the sub-catagories are the same the only way to identify is by the idendtifier thus the Mod(row()). I'm not stuck on doing it this way, open to suggestiongs. Thanks, MT. "T. Valko" wrote: 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! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average,Countif
Based on that sample from column B the increment is not 7 rows, it's 5. So,
I'm guessing you left something out, maybe 2 empty rows between sets? So what you need are 5 formulas, or, 1 formula copied to 5 cells. Avg all identifiers Avg all 1st Avg all 2nd Avg all 3rd Avg all 4th Take a look at this sample file. 1 formula copied to 5 cells. http://cjoint.com/?ggfKyB4xNF Biff "M.A.Tyler" <Great Lakes State wrote in message ... OK. Column B is set up like this: ALB 1st 2nd 3rd 4th AP 1st 2nd 3rd 4th ASD 1st 2nd 3rd 4th Each identifier has 4 subsequent sub-catagories, labled 1st, 2nd, 3rd, 4th. What I need to accomplish is first calculating an average of all the identifiers (ALB+AP+ASD/3) not counting any 0 or empty cells. Then repeat for the 1st's, 2nd's and so on. Since all of the sub-catagories are the same the only way to identify is by the idendtifier thus the Mod(row()). I'm not stuck on doing it this way, open to suggestiongs. Thanks, MT. "T. Valko" wrote: 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! |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average,Countif
Your correct I did leave two rows out because I'm just not using them, sorry
for that confusion. Your solution looks perfect and I've messed around with it some, however I can't seem to get it to work? Perhaps it's because the data is on sheet CL10000! and I'm attempting to have the averages appear on sheet ADJ PTS! So the range to be averaged is CL10000!C3:C644 every 7th row, or rows 3,7,10...With the averages to appear on sheet ADJ PTS!C1:C5 identifier C1,1st C2, 2nd C3,3rd C4, 4th C5...Then I would need to copy across thru column Q. Is that possible? "T. Valko" wrote: Based on that sample from column B the increment is not 7 rows, it's 5. So, I'm guessing you left something out, maybe 2 empty rows between sets? So what you need are 5 formulas, or, 1 formula copied to 5 cells. Avg all identifiers Avg all 1st Avg all 2nd Avg all 3rd Avg all 4th Take a look at this sample file. 1 formula copied to 5 cells. http://cjoint.com/?ggfKyB4xNF Biff "M.A.Tyler" <Great Lakes State wrote in message ... OK. Column B is set up like this: ALB 1st 2nd 3rd 4th AP 1st 2nd 3rd 4th ASD 1st 2nd 3rd 4th Each identifier has 4 subsequent sub-catagories, labled 1st, 2nd, 3rd, 4th. What I need to accomplish is first calculating an average of all the identifiers (ALB+AP+ASD/3) not counting any 0 or empty cells. Then repeat for the 1st's, 2nd's and so on. Since all of the sub-catagories are the same the only way to identify is by the idendtifier thus the Mod(row()). I'm not stuck on doing it this way, open to suggestiongs. Thanks, MT. "T. Valko" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average, Countif | Excel Discussion (Misc queries) | |||
COUNTIF and AVERAGE only cells in unhidden rows | Excel Discussion (Misc queries) | |||
Excluding Zero's from Average (SumIF / CountIF) | Excel Worksheet Functions | |||
Countif cell greater than average | Excel Worksheet Functions | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions |