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

Ed,

Do you mean something like

=AVERAGE(G1:G9 G5:I6)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ed" wrote in message
...
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

Bob - Sorry for the delay in the reply. I got caught by some work and then
it was time to log off. From what I saw of the AVERAGE worksheet function
Help, your syntax might not work, if I understand it (not saying that I do,
mind you).

What I'm looking at is (for example) a named range covering A3:F19. I want
to place a formula in B20, C20, D20, E20, and F20 that will AVERAGE only the
intersection of that column with the named range. So the formula in B20
would pick up only the values in both Column B and the named range, ignoring
anything in rows 1 and 2. That same formula in C20 could pick up only Col.
C as it intersects the range.

I'm using XL2000. Can you recommend something I should be looking at to
construct this?

Ed

"Bob Phillips" wrote in message
...
Ed,

Do you mean something like

=AVERAGE(G1:G9 G5:I6)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ed" wrote in message
...
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

Ed, that would work if you used say
=AVERAGE(B1:B19 test) where test is the named range. You can't use the whole
column as this formula will be in B20, so you will get a circular
reference.

But I am not seeing the advantage here, why not just use =AVERAGE(B1:B19).
If it is because you don't know what cell the formula is in, and therefore
where the last cell to be averaged is, just use
=AVERAGE($C$1:OFFSET(C20,-1,0))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ed" wrote in message
...
Bob - Sorry for the delay in the reply. I got caught by some work and
then
it was time to log off. From what I saw of the AVERAGE worksheet function
Help, your syntax might not work, if I understand it (not saying that I
do,
mind you).

What I'm looking at is (for example) a named range covering A3:F19. I
want
to place a formula in B20, C20, D20, E20, and F20 that will AVERAGE only
the
intersection of that column with the named range. So the formula in B20
would pick up only the values in both Column B and the named range,
ignoring
anything in rows 1 and 2. That same formula in C20 could pick up only
Col.
C as it intersects the range.

I'm using XL2000. Can you recommend something I should be looking at to
construct this?

Ed

"Bob Phillips" wrote in message
...
Ed,

Do you mean something like

=AVERAGE(G1:G9 G5:I6)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ed" wrote in message
...
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

Thanks for staying with me on this, Bob. It's quite possible I have an idea
stuck sideways in my head and need it kicked free!

What I'm looking at are several of these ranges stacked under each other.
My first named range may cover rows 3-20, the second 24-30, etc. I wanted
to see if there was a formula syntax that would work in any column, picking
up the column number and intersecting that with the named range. If the
user decides to add or remove rows from the range, I have code that will
redefine the range limits. But given this possibility, I thought it better
to use the range name in the formula than to have to run across 20 columns
adjusting the cell limits of the function.

So if the user deletes 4 rows from Range1 (rows 3-20), it now covers rows
3-16, and my formula in B21 that did say $B$3:$B$20 is now in B17. Ditto
for the other 10 or 15 or whatever columns. Thus, I thought it best to use
the name, rather than cell limits. Am I going about this the wrong way?

Ed

"Bob Phillips" wrote in message
...
Ed, that would work if you used say
=AVERAGE(B1:B19 test) where test is the named range. You can't use the
whole
column as this formula will be in B20, so you will get a circular
reference.

But I am not seeing the advantage here, why not just use =AVERAGE(B1:B19).
If it is because you don't know what cell the formula is in, and therefore
where the last cell to be averaged is, just use
=AVERAGE($C$1:OFFSET(C20,-1,0))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ed" wrote in message
...
Bob - Sorry for the delay in the reply. I got caught by some work and
then
it was time to log off. From what I saw of the AVERAGE worksheet
function
Help, your syntax might not work, if I understand it (not saying that I
do,
mind you).

What I'm looking at is (for example) a named range covering A3:F19. I
want
to place a formula in B20, C20, D20, E20, and F20 that will AVERAGE only
the
intersection of that column with the named range. So the formula in B20
would pick up only the values in both Column B and the named range,
ignoring
anything in rows 1 and 2. That same formula in C20 could pick up only
Col.
C as it intersects the range.

I'm using XL2000. Can you recommend something I should be looking at to
construct this?

Ed

"Bob Phillips" wrote in message
...
Ed,

Do you mean something like

=AVERAGE(G1:G9 G5:I6)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ed" wrote in message
...
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

ED,

The formula I gave you

=AVERAGE($B$3:OFFSET(B20,-1,0))

But then again, even if you use =AVERAGE($B$3:$B$20) this should be okay
even deleting rows. Have you tried it, and if so, what problems did you get?

I have to say you're solution (appears) is more complex than seems
necessary.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ed" wrote in message
...
Thanks for staying with me on this, Bob. It's quite possible I have an
idea
stuck sideways in my head and need it kicked free!

What I'm looking at are several of these ranges stacked under each other.
My first named range may cover rows 3-20, the second 24-30, etc. I wanted
to see if there was a formula syntax that would work in any column,
picking
up the column number and intersecting that with the named range. If the
user decides to add or remove rows from the range, I have code that will
redefine the range limits. But given this possibility, I thought it
better
to use the range name in the formula than to have to run across 20 columns
adjusting the cell limits of the function.

So if the user deletes 4 rows from Range1 (rows 3-20), it now covers rows
3-16, and my formula in B21 that did say $B$3:$B$20 is now in B17. Ditto
for the other 10 or 15 or whatever columns. Thus, I thought it best to
use
the name, rather than cell limits. Am I going about this the wrong way?

Ed

"Bob Phillips" wrote in message
...
Ed, that would work if you used say
=AVERAGE(B1:B19 test) where test is the named range. You can't use the
whole
column as this formula will be in B20, so you will get a circular
reference.

But I am not seeing the advantage here, why not just use
=AVERAGE(B1:B19).
If it is because you don't know what cell the formula is in, and
therefore
where the last cell to be averaged is, just use
=AVERAGE($C$1:OFFSET(C20,-1,0))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ed" wrote in message
...
Bob - Sorry for the delay in the reply. I got caught by some work and
then
it was time to log off. From what I saw of the AVERAGE worksheet
function
Help, your syntax might not work, if I understand it (not saying that
I
do,
mind you).

What I'm looking at is (for example) a named range covering A3:F19. I
want
to place a formula in B20, C20, D20, E20, and F20 that will AVERAGE
only
the
intersection of that column with the named range. So the formula in
B20
would pick up only the values in both Column B and the named range,
ignoring
anything in rows 1 and 2. That same formula in C20 could pick up only
Col.
C as it intersects the range.

I'm using XL2000. Can you recommend something I should be looking at
to
construct this?

Ed

"Bob Phillips" wrote in message
...
Ed,

Do you mean something like

=AVERAGE(G1:G9 G5:I6)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ed" wrote in message
...
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