? avoid changing sum function as rows added?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JClark - 25 Oct 2006 22:31 GMT

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

Reply to this Message

Sandy Mann - 25 Oct 2006 23:25 GMT

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)))

Signature

HTH

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

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

> Hello Group:
> I'm obviously new. To keep it simple, here's how I'll pose the
[quoted text clipped - 13 lines]
>
> Jack

Reply to this Message

JClark - 26 Oct 2006 22:58 GMT

>JClark,
>
[quoted text clipped - 5 lines]
>
>=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

Reply to this Message

Sandy Mann - 26 Oct 2006 23:47 GMT

Jack,

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

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

Signature

HTH

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

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

>>JClark,
>>
[quoted text clipped - 14 lines]
>
> Jack

Reply to this Message

FloMM2 - 28 Oct 2006 20:37 GMT

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

:-)

> Hello Group:
> I'm obviously new. To keep it simple, here's how I'll pose the
[quoted text clipped - 13 lines]
>
> Jack

Reply to this Message