I need help writing a macro that will divide a 5000 row table into
groups of 30 rows, and find the average of each group?

I've got a data table with blood pressure readings taken at 1-second
intervals. I need help writing a macro that will take the table,
divide the data into groups of 30 rows and spit out the average of
certain columns in each group (I need one column for systolic,
diastolic, MAP, etc., however, the table contains columns of
irrelevant data), which will in effect give me averages other 30-
second intervals.

To make things more complex, the actual readings start at row 48.

this is an excel spreadsheet basically...just raw numbers in a
spreadsheet.

to be more specific, there are exactly 5120 rows in the spreadsheet
and 26 columns. I need to divide the 5120 rows into groups of 30 rows,
and for each group, find the average of columns B,G,H,and I.

A non-programming approach using a helper column:

Insert a column (or use an empty column next to your table - I will use
column A). Assuming you have headers in row 47, entering this in cell A48

="Group "&INT((ROWS(A$48:A48)-1)/30)+1

will give you 30 consecutive 1's (then 2, 3, so on). Then copy column A,
and click Edit/Paste Special/Values to hardcode the formula. Then select
your table (including column A) and click Data/Subtotals, at each change in
column A, use function average on columns B, G, H, and I.



" wrote:

I need help writing a macro that will divide a 5000 row table into
groups of 30 rows, and find the average of each group?

I've got a data table with blood pressure readings taken at 1-second
intervals. I need help writing a macro that will take the table,
divide the data into groups of 30 rows and spit out the average of
certain columns in each group (I need one column for systolic,
diastolic, MAP, etc., however, the table contains columns of
irrelevant data), which will in effect give me averages other 30-
second intervals.

To make things more complex, the actual readings start at row 48.

this is an excel spreadsheet basically...just raw numbers in a
spreadsheet.

to be more specific, there are exactly 5120 rows in the spreadsheet
and 26 columns. I need to divide the 5120 rows into groups of 30 rows,
and for each group, find the average of columns B,G,H,and I.

I really appreciate your help, however, it probably defeats the
purpose of a shortcut if I have to repeat this process 5120/30 times?

On Jul 2, 6:38 pm, JMB wrote:
A non-programming approach using a helper column:

Insert a column (or use an empty column next to your table - I will use
column A). Assuming you have headers in row 47, entering this in cell A48

="Group "&INT((ROWS(A$48:A48)-1)/30)+1

will give you 30 consecutive 1's (then 2, 3, so on). Then copy column A,
and click Edit/Paste Special/Values to hardcode the formula. Then select
your table (including column A) and click Data/Subtotals, at each change in
column A, use function average on columns B, G, H, and I.

" wrote:
I need help writing a macro that will divide a 5000 row table into
groups of 30 rows, and find the average of each group?

I've got a data table with blood pressure readings taken at 1-second
intervals. I need help writing a macro that will take the table,
divide the data into groups of 30 rows and spit out the average of
certain columns in each group (I need one column for systolic,
diastolic, MAP, etc., however, the table contains columns of
irrelevant data), which will in effect give me averages other 30-
second intervals.

To make things more complex, the actual readings start at row 48.

this is an excel spreadsheet basically...just raw numbers in a
spreadsheet.

to be more specific, there are exactly 5120 rows in the spreadsheet
and 26 columns. I need to divide the 5120 rows into groups of 30 rows,
and for each group, find the average of columns B,G,H,and I.

this is an excel spreadsheet basically...just raw numbers in a
spreadsheet.

From your OP, I thought you only had one table. When I said select your
table and click Data/Subtotals - I was talking about all 5120 rows.

Do you have spaces in your data? I was assuming you didn't.

If it still does not work for you, please post some example of the layout.



" wrote:

I really appreciate your help, however, it probably defeats the
purpose of a shortcut if I have to repeat this process 5120/30 times?

On Jul 2, 6:38 pm, JMB wrote:
A non-programming approach using a helper column:

Insert a column (or use an empty column next to your table - I will use
column A). Assuming you have headers in row 47, entering this in cell A48

="Group "&INT((ROWS(A$48:A48)-1)/30)+1

will give you 30 consecutive 1's (then 2, 3, so on). Then copy column A,
and click Edit/Paste Special/Values to hardcode the formula. Then select
your table (including column A) and click Data/Subtotals, at each change in
column A, use function average on columns B, G, H, and I.

" wrote:
I need help writing a macro that will divide a 5000 row table into
groups of 30 rows, and find the average of each group?

I've got a data table with blood pressure readings taken at 1-second
intervals. I need help writing a macro that will take the table,
divide the data into groups of 30 rows and spit out the average of
certain columns in each group (I need one column for systolic,
diastolic, MAP, etc., however, the table contains columns of
irrelevant data), which will in effect give me averages other 30-
second intervals.

To make things more complex, the actual readings start at row 48.

this is an excel spreadsheet basically...just raw numbers in a
spreadsheet.

to be more specific, there are exactly 5120 rows in the spreadsheet
and 26 columns. I need to divide the 5120 rows into groups of 30 rows,
and for each group, find the average of columns B,G,H,and I.

Another approach, you could enter this formula into an empty cell (I used D48
- change D48 in the formula if you enter it into a different cell) and copy
down as far as needed. Again, I assume there are no empty rows in your data.

=AVERAGE(INDEX(A$48:A$5120,((ROWS(D$48:D48)-1)*30)+1):INDEX(A$48:A$5120,MIN(((ROWS(D$48:D48))* 30),ROWS(A$48:A$5120))))

But I like subtotals better - easier to see which numbers are included in
each group and you can collapse/expand the table as needed.





" wrote:

I really appreciate your help, however, it probably defeats the
purpose of a shortcut if I have to repeat this process 5120/30 times?

On Jul 2, 6:38 pm, JMB wrote:
A non-programming approach using a helper column:

Insert a column (or use an empty column next to your table - I will use
column A). Assuming you have headers in row 47, entering this in cell A48

="Group "&INT((ROWS(A$48:A48)-1)/30)+1

will give you 30 consecutive 1's (then 2, 3, so on). Then copy column A,
and click Edit/Paste Special/Values to hardcode the formula. Then select
your table (including column A) and click Data/Subtotals, at each change in
column A, use function average on columns B, G, H, and I.

" wrote:
I need help writing a macro that will divide a 5000 row table into
groups of 30 rows, and find the average of each group?

I've got a data table with blood pressure readings taken at 1-second
intervals. I need help writing a macro that will take the table,
divide the data into groups of 30 rows and spit out the average of
certain columns in each group (I need one column for systolic,
diastolic, MAP, etc., however, the table contains columns of
irrelevant data), which will in effect give me averages other 30-
second intervals.

To make things more complex, the actual readings start at row 48.

this is an excel spreadsheet basically...just raw numbers in a
spreadsheet.

to be more specific, there are exactly 5120 rows in the spreadsheet
and 26 columns. I need to divide the 5120 rows into groups of 30 rows,
and for each group, find the average of columns B,G,H,and I.