And to you Ed. See you in the New Year.

Bob


"Ed" wrote in message
...
Bob, it looks like I'm asking questions that aren't as meaningful as I
thought, so the answers don't seem to make sense to me. I think I need to
sit back and take a better look at this and get a better handle on what I
need to do. Then maybe I can ask better questions, and get a better grasp
on both the problem and the answers, rather than having both of us chase
this thing around in circles.

Thanks for all your input. You've given me some things to chew on over the
holidays. Because I'm doing all this at work, I may not be back 'til after
the New Year, so I hope you and yours have a very happy holiday season.

Ed

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

It still looks complex to me. You have to have named ranges that define
the
start and end rows, so where is the difference?

Could you not add some identifier to say column A for each named range,
and
do a SUMIF on that value?

--

HTH

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


"Ed" wrote in message
...
Bob: The formula
=AVERAGE($B$3:OFFSET(B20,-1,0))
does work, even with adding and deleting rows. Thank you.

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

And I'm sorry if I'm making this much more convoluted than necessary.
But
if I have this:
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.
for four ranges of 20 columns each, then won't I need to go in and
determine
the top and bottom rows of each range, then loop 80 times setting
formulas
with the correct cell references? I thought if there was a simple
syntax
that would pick up the column number of the active cell and intersect
that
column with the name of the range I'm working with, there would be
much
less
chance for error, as it would be the same syntax repeated for each
cell
that
needs a formula.

Too much like work?
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