working with Large Non Contiguous Data

[asihsimanis]

Hello Everyone,

Im pretty desperate. I've got a column with around 9000 rows of data and I need to average non-contiguous rows. So I need to average row 1,4,7,10 (every 3rd row or so), and I have no idea how to do it. I just found out that we can use VBA to do this, but I havent figured out a way. I wonder if anyone could please please help me...

 

[Aladin Akyurek]

Hello Everyone,

Im pretty desperate. I've got a column with around 9000 rows of data and I need to average non-contiguous rows. So I need to average row 1,4,7,10 (every 3rd row or so), and I have no idea how to do it. I just found out that we can use VBA to do this, but I havent figured out a way. I wonder if anyone could please please help me...

Control+shift+enter, not just enter:

=AVERAGE(IF(MOD(ROW(A2:A9000)-ROW(A2),3)=0,A2:A9000))

If you have something say in column B tht marks every 3rd entry, try AVERAGEIF instead.

 

[beastman59]

If it is always every third row, you could use a For loop such as For a=1 9000 step 3
Assuming the data is in row A, the code would be something like this:

for a=1 to 9000 step 3
k=k+1
i="A" & A
J=ActiveWorkshee.Range(i)+J
next a
activeworksheet.Range("B1") = "AVERAGE " & J/K

 

[asihsimanis]

thank you for your reply... so glad to read such an easy (seemingly) wasy way to do it without VBA...really sorry for asking again, but what does this formula actually mean? I typed it in and to my delight I received a number, but I dont know which average it actually gives... because I want to group row 1,4,7,10, etc to make an average out of that, and also I would like to group 2,5,8,11, etc and 3,6,9,12,etc and find the average of that. How do I do that? thank you soooo much for your answer!!

 

[beastman59]

I just recently began learning Excel and wasn't aware of the AVERAGE function. I used to be a programmer. Excel has a lot you can do withot VBA. With VBA you can do more. One of the questions I asked, I ended up solving myself and someone showed me a shorter VBA, but it had so many quotes and commas. I liked mine better (though longer) because I could read and understand what it was doing.

 

[asihsimanis]

Reallt\y thank you for your reply! Im sure if I use the VBA, theres gonna be lots more possibility. but I really dont have a grip on it yet, thank you sooo much for your reply though :D

 

[Aladin Akyurek]

thank you for your reply... so glad to read such an easy (seemingly) wasy way to do it without VBA...really sorry for asking again, but what does this formula actually mean? I typed it in and to my delight I received a number, but I dont know which average it actually gives... because I want to group row 1,4,7,10, etc to make an average out of that, and also I would like to group 2,5,8,11, etc and 3,6,9,12,etc and find the average of that. How do I do that? thank you soooo much for your answer!!

What the formula means is: If mod of row num is divisible by 3, pick out the figure it houses for averaging.

Given A2:A9000...

B2, control+shift+enter, not just enter, and copy down:

=IF(COUNT(A2:$A$9000)>=4,
  AVERAGE(IF(MOD(ROW(A2:$A$9000)-ROW(A2),3)=0,
   IF(ISNUMBER(A2:$A$9000),A2:$A$9000))),"")

Note that when this formula is copied to too many cells, the file can become slow.

 

[beastman59]

What the formula means is: If mod of row num is divisible by 3, pick out the figure it houses for averaging.

Given A2:A9000...

B2, control+shift+enter, not just enter, and copy down:

=IF(COUNT(A2:$A$9000)>=4,
  AVERAGE(IF(MOD(ROW(A2:$A$9000)-ROW(A2),3)=0,
   IF(ISNUMBER(A2:$A$9000),A2:$A$9000))),"")

Note that when this formula is copied to too many cells, the file can become slow.

So row 1, 4, 7, 10, etc will not be included in the average because they are not divisible by 3.

If you use the VBA code, you could change the step to be whatever you want. If no step is given, it defaults to 1.