Formula adjust for inserted row above current row

  • Thread starter Thread starter tulip
  • Start date Start date
T

tulip

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?
 
Try,

=AVERAGE(INDIRECT("A1:A33"))
Click to expand...

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.
 
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?
 
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 - 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"
Click to expand...


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.
 
I will try that. Thanks for your help.

Spiky said:
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.
Click to expand...
 
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
 
Back
Top