Formula adjust for inserted row above current row
 

I am averaging a column of numbers =Average(A1:A33) If I insert a row
"above" A1 the formula does not adjust to include the inserted row. Is there
a way to make this work when you insert above the current row?

Formula adjust for inserted row above current row
 

Try,

=AVERAGE(INDIRECT("A1:A33"))

Mike

"tulip" wrote:

I am averaging a column of numbers =Average(A1:A33) If I insert a row
"above" A1 the formula does not adjust to include the inserted row. Is there
a way to make this work when you insert above the current row?

Formula adjust for inserted row above current row
 

On Sep 18, 1:12 pm, Mike H wrote:
Try,

=AVERAGE(INDIRECT("A1:A33"))

That will leave out the new A34 after the insertion, though.

tulip, where is the formula? You can use this much INDIRECT("A1:A"
of Mike's formula and then perhaps an OFFSET or ROW function to keep
the bottom number dynamic, too.

Formula adjust for inserted row above current row
 

That works to include the row inserted above the current row - but when you
insert the row above, it "pushes" the current rows down, so data in A33 is
now A34. How do I include the last row of data as it moves "down" because of
the insertion at the top?

"Mike H" wrote:

Try,

=AVERAGE(INDIRECT("A1:A33"))

Mike

"tulip" wrote:

I am averaging a column of numbers =Average(A1:A33) If I insert a row
"above" A1 the formula does not adjust to include the inserted row. Is there
a way to make this work when you insert above the current row?

Formula adjust for inserted row above current row
 

Spiky - can you help me with the suggestions you made? I don't know how to
use the ROW or OFFSET to capture the new A34. =AVERAGE(INDIRECT("A1:A"

"Spiky" wrote:

On Sep 18, 1:12 pm, Mike H wrote:
Try,

=AVERAGE(INDIRECT("A1:A33"))

That will leave out the new A34 after the insertion, though.

tulip, where is the formula? You can use this much INDIRECT("A1:A"
of Mike's formula and then perhaps an OFFSET or ROW function to keep
the bottom number dynamic, too.

Formula adjust for inserted row above current row
 

On Sep 18, 1:44 pm, tulip wrote:
Spiky - can you help me with the suggestions you made? I don't know how to
use the ROW or OFFSET to capture the new A34. =AVERAGE(INDIRECT("A1:A"


Well, if the formula is directly below the list it is averaging, like
in A34 in your original example:
=AVERAGE(INDIRECT("A1:A"&ROW()-1))

It matters where you enter this formula. My example calculates from
where it is, less one row. That keeps it dynamic so you can insert
either at the top or bottom of the list.

Formula adjust for inserted row above current row
 

I will try that. Thanks for your help.

"Spiky" wrote:

On Sep 18, 1:44 pm, tulip wrote:
Spiky - can you help me with the suggestions you made? I don't know how to
use the ROW or OFFSET to capture the new A34. =AVERAGE(INDIRECT("A1:A"


Well, if the formula is directly below the list it is averaging, like
in A34 in your original example:
=AVERAGE(INDIRECT("A1:A"&ROW()-1))

It matters where you enter this formula. My example calculates from
where it is, less one row. That keeps it dynamic so you can insert
either at the top or bottom of the list.

Formula adjust for inserted row above current row
 

If you can move your Average() formula out of Column A, you might try:

=Average(A:A)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"tulip" wrote in message
...
I will try that. Thanks for your help.

"Spiky" wrote:

On Sep 18, 1:44 pm, tulip wrote:
Spiky - can you help me with the suggestions you made? I don't know
how to
use the ROW or OFFSET to capture the new A34. =AVERAGE(INDIRECT("A1:A"


Well, if the formula is directly below the list it is averaging, like
in A34 in your original example:
=AVERAGE(INDIRECT("A1:A"&ROW()-1))

It matters where you enter this formula. My example calculates from
where it is, less one row. That keeps it dynamic so you can insert
either at the top or bottom of the list.

Formula adjust for inserted row above current row
 

Hi,

Simply convert the range to a list by going to Data List. One of the
benefits of converting a range to a list is that it auto expands.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"tulip" wrote in message
...
I am averaging a column of numbers =Average(A1:A33) If I insert a row
"above" A1 the formula does not adjust to include the inserted row. Is
there
a way to make this work when you insert above the current row?