Macro to return averages within a column

[Guest]

I have a spreadsheet of data that will be increasing in number of rows and
number of columns, but within that data starting from cell "J1" I need to
average together the data in every five rows then move over 5 columns and
average together every five rows in that column. This needs to continue
until the column is blank (except for the value in the header in row 1).
Actually, if it helps the columns that I need to identify these averages will
always have the title "Output" in header row.

Any assistance with code would be greatly appreciated!

Thanks,
Holly

 

[Guest]

Hi Holly,

I love a challenge and it keeps me occupied. However, some of confirmation
of what is required and more info needed.

Am I correct in assuming that you have a header row so the first average
will be average of J2:J6 then J7:J11 then J12:J16 and so on to the end of the
data. This will be repeated for K2:K6, K7:K11, K12:K16 and for each column to
the end of the data.

What cells are the average results to be placed in? Can they be on another
worksheet?

How much data do you anticipate having?

Regards,

OssieMac

 

[Guest]

Holly,

Further to my previous post, I have been reading and re-reading your
description and I am sure that I have not really comprehended what you want.
Can you post a sample of what it should look like?

Regards,

OssieMac

 

[Guest]

OssieMac,

Yes, sorry I wasn't very clear to begin with. You are correct the averages
would be J2:J6, J7:J11 and so on. I would like those averages posted in
another worksheet within the same workbook. I would, however, need that
averaging to repeat every 5 columns, so it would pick back up at O2:O6,
O7:O11 and so on. This will be a continually growing amount of data and I
anticipate it growing to be a great deal of data (In fact I'm worried that
they'll run out of columns before they run out of data, but they are kind of
set on recording the data this way).

As an example (please note, in the interest of conserving space I only have
3 columns between data on the example, but in actuality there are 5)...
SHEET A
Series ID | Date | Study | Output | Date | Study | Output
A 6/1 A1 2.5 6/15 A2 3
B 6/1 B1 3.67 6/15 B2 2.8
C 6/1 C1 3.1 6/15 C2 1.4
D 6/1 D1 2.6 6/15 D2 2.5
E 6/1 E1 2.0 6/15 E2 2.1

I need the code to then calculate the average of those 5 rows in the columns
with the header "Output" and place those averages in Sheet B, i.e...

SHEET B
Date | Average Output | Date | Average Output
6/1 2.774 6/15 2.36

Hope that clarifies. Thanks, in advance for brainstorming this.

Holly

 

[Guest]

Can do what you want but still need more info. Your example indicates that
you want the Dates displayed with the Averages so I need to know where the
Dates are in relation to the Output. Can you let me know which column has the
Dates for the Output in column J and which column has the Dates for Output in
Column O. (I am assuming that it is probably columns E and N.)

Regards,

OssieMac

 

[Guest]

Hi Again Holly,

Further to my previous post, I have done some work on a macro which I think
will do what you want but it still needs a little tidying up.

I must have been in fairy land when I worked out which columns that I
thought the dates would be in. Having looked a bit closer I have made the
assumption that for Output in columns J, O & T the Dates are probably in
columns F, K & P and the rest will follow that pattern across the worksheet.
The macro is easy to change if this is not right.

Now some more questions:-

Please confirm if the above is correct and if not advise accordingly?

What is the name of the worksheet with your data so I can use it in the
macro and you will not have to edit it.

I have named the new worksheet 'Averages'. Is that OK and if not what would
you like to call it?

How conversant are you with macros? Do you need any instructions for copying
it into the workbook and running it etc.

Regards,

OssieMac