|
mean calculation of each column of each group
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 |
mean calculation of each column of each group
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" wrote: 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 |
mean calculation of each column of each group
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" wrote: 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" wrote: 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 |
mean calculation of each column of each group
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" wrote: 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" wrote: 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" wrote: 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 |
mean calculation of each column of each group
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
mean calculation of each column of each group
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
mean calculation of each column of each group
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 "Joel" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
| All times are GMT +1. The time now is 01:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com