? avoid changing sum function as rows added?

[JClark]

Hello Group:
I'm obviously new. To keep it simple, here's how I'll pose the
question:
Suppose I have multiple rows and two columns.
Column A contains names of people
Column B contains each person's age
The last cell in column B is to be an average of the ages, so in that
cell I insert the function =AVERAGE(B1:B100)
This works fine, but as I add rows, I have to change the formula to
include the recently added row, B101, then B102, etc)
Is there any way I can make the function automatically include the
last cell? Sort of like a wild symbol?
It's not a big problem, but I'm just wondering if that's possible.

Thanks.

Jack

 

[Sandy Mann]

JClark,

Try using the OFFSET() Function:

=AVERAGE(B1:OFFSET(B100,-1,0))

To avoid the #DIV/0! error:

=IF(COUNT(B1:OFFSET(B100,-1,0))=0,"",AVERAGE(B1:OFFSET(B100,-1,0)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[JClark]

JClark,

Try using the OFFSET() Function:

=AVERAGE(B1:OFFSET(B100,-1,0))

To avoid the #DIV/0! error:

=IF(COUNT(B1:OFFSET(B100,-1,0))=0,"",AVERAGE(B1:OFFSET(B100,-1,0)))

Sandy,
Thanks for the suggestion. I think it will require a bit of research
on my part before I can understand it. I'll copy your note and work on
it this weekend. Seemed to me as though there ought to be a way to do
it.

Best regards,

Jack

 

[Sandy Mann]

Jack,

the range being averaged starts in B1 and goes to the cell that the formula
is in (B100) and then offsets -1 rows ie the row above, and zero columns
right ie the same column. The range is therefore B1:B99. When you sit in
row 100 and insert a new row, XL will insert an new row 100 and move the
formula down to row 101. The offset part formula will be indexed so that it
will then say OFFSET(B101,-1,0) ie still the row above

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

JClark,
Try this:
At the bottom of your column A (names) & column B (ages) Insert
a blank cell in both columns A & B. When Excel ask you to move cells,Select
down.
Check your last cell formula, make sure it added the last cell in the formula.
Eventhough it is blank.
The next time you want to add a name select these new cells (empty ones),
insert and down as above.
Your Average formula will adjust automatically, even if you decide to insert
in the middle of your columns(as long as you insert two cells).
hth