mean calculation of each column of each group

[climate]

Hi
I have a matrix on sheet1 (2000 * 60), I2 TO BN2001, ,columns E,F,G is date
(year,month,day).
First row I2 to BN2 is labled.i sorted this matrix based on column G
(day)descending and expanded.
Then sorted again, based on column F (month) ascending and expanded.
As a result, my matrix is sorted and classified to many groups. for example:
first group include all days (31 Jan) of all years, and second group include
all days (30 Jan) all of years.
I need to a macro, when i run it,at first separate groups with 5 blank rows,
and then calculate mean of any column in each group and write
them(mean)under any column in to the first blank row.

Would you please help me, because i have 60 worksheet similar above, and
manual arrangment and calculation need to spend very time.

best regards

 

[Joel]

I assume when you say MEAN you mean AVERAGE and not MEDIAN.

Sub MakeGroups()

RowCount = 3
FirstRow = RowCount
Do While Range("E" & RowCount) <> ""
If Range("E" & RowCount) <> Range("E" & (RowCount + 1)) Or _
Range("F" & RowCount) <> Range("F" & (RowCount + 1)) Or _
Range("G" & RowCount) <> Range("G" & (RowCount + 1)) Then

Rows(RowCount + 1).Insert
Rows(RowCount + 1).Insert
Rows(RowCount + 1).Insert
Rows(RowCount + 1).Insert
Rows(RowCount + 1).Insert

Range("I" & (RowCount + 1)).Formula = _
"=Average(I" & FirstRow & ":I" & RowCount & ")"
Range("I" & (RowCount + 1)).Copy _
Destination:=Range("I" & (RowCount + 1) & ":BN" & (RowCount + 1))

RowCount = RowCount + 6
FirstRow = RowCount
Else
RowCount = RowCount + 1
End If
Loop

End Sub

 

[climate]

Hi again, Joel
When i copy your code to sheet1 where my matrix there,(right click and view
code and paste), and run it, it can't separate groups, but separate any row
on the whole of matrix, and calculate mean for any row which seperated and
write it under any row, as a result, my matrix extend to 10000 rows.

regards

 

[Joel]

The code seperates the rows whenevfer there is a differennce in Columns E, F,
or G in any two adjacent rows. The code expects the worksheet to be sorted
by rows E, F, and G. I may of been slightly confused by your original
posting. I just need to clearly know what criteria I should use to determine
when a group starts and ends.

 

[climate]

Hi Joel
Ok, i need to your e-mail , because i want to send you 1) my original data
2) sorted data with column G,F(not E), and 3) data grouped(colored) and
4)what i expect.
I arrange 4 above item on sheet1 ,2 ,3,4 .
regards

 

[Joel]

Lets see if remving E from the Testing will work. I will send you my e-mail
if this doesn't work


Sub MakeGroups()

RowCount = 3
FirstRow = RowCount
Do While Range("F" & RowCount) <> ""
If Range("F" & RowCount) <> Range("F" & (RowCount + 1)) Or _
Range("G" & RowCount) <> Range("G" & (RowCount + 1)) Then

Rows(RowCount + 1).Insert
Rows(RowCount + 1).Insert
Rows(RowCount + 1).Insert
Rows(RowCount + 1).Insert
Rows(RowCount + 1).Insert

Range("I" & (RowCount + 1)).Formula = _
"=Average(I" & FirstRow & ":I" & RowCount & ")"
Range("I" & (RowCount + 1)).Copy _
Destination:=Range("I" & (RowCount + 1) & ":BN" & (RowCount + 1))

RowCount = RowCount + 6
FirstRow = RowCount
Else
RowCount = RowCount + 1
End If
Loop

End Sub

 

[climate]

Hi Joel
Results of removing E column had positive effect, your code works correctly
and very excellent. i must express my gratitude to you.

Best regards