E
Ed
On Sheet1, I will have a column of item numbers, with each item having
perhaps 20 columns of numbers following. I need to separate these items
into groups, and take the average of all the numbers in that group in each
column. Say Group1 is rows 3-10, numbers in col B-J. In B11, I need to
place the average of B3-B10, ditto for C11, etc. If Group2 is rows 12-15,
the averages will go into row 16 across all the columns.
Since the lengths of these groups can vary, it was my thought to use VBA to
create a named range for each group. Then I could find the last row of the
range, insert a new row, and run across the row in the required cells to
insert a formula to average the numbers above that belong to the named
range.
Where I'm stuck is how to reference the intersection of the particular
column and the named range. So in B11 (for my example above), I would have
a formula that says "=AVERAGE (intersection of this column and the named
range for Group1)". It seems like it should be easy, but I can't get my
brain out of neutral! Any kick-starts would be appreciated.
Ed
perhaps 20 columns of numbers following. I need to separate these items
into groups, and take the average of all the numbers in that group in each
column. Say Group1 is rows 3-10, numbers in col B-J. In B11, I need to
place the average of B3-B10, ditto for C11, etc. If Group2 is rows 12-15,
the averages will go into row 16 across all the columns.
Since the lengths of these groups can vary, it was my thought to use VBA to
create a named range for each group. Then I could find the last row of the
range, insert a new row, and run across the row in the required cells to
insert a formula to average the numbers above that belong to the named
range.
Where I'm stuck is how to reference the intersection of the particular
column and the named range. So in B11 (for my example above), I would have
a formula that says "=AVERAGE (intersection of this column and the named
range for Group1)". It seems like it should be easy, but I can't get my
brain out of neutral! Any kick-starts would be appreciated.
Ed