Follow-up AVERAGEIF question

[Ken]

Office Excel 2007

The other day, someone here pointed me to the AVERAGEIF function as a way of
averaging a range of cells, but excluding from the computation any cell in
the range that had a zero value.

I've been able to get that to work...partially. But if I insert new rows,
the formula does not update to include those rows.

For example, if I have number entries in cells A1 through A5, and if cell A6
is =AVERAGEIF(A1:A4,"<>0"), I get a result in A6. However, if I then insert
a new row above A6 and enter a value there...of course the old A6 becomes
A7, but the formula in the new A7 still read =AVERAGEIF(A1:A4,"<>0"). I
would have expected it to change to (A1:A5,"<>0")

This is what happens with the AVERAGE function. (Or the SUM function, for
that matter.)

How do I get the AVERAGEIF function to update to include newly-added rows?

Thanks!

Ken Isaacson
SILENT COUNSEL, a legal thriller
www.KenIsaacson.com

 

[JE McGimpsey]

One way:

A6: =AVERAGEIF(A$1:INDEX(A:A,ROW()-2),"<>0")

 

[T. Valko]

It depends on where you insert the new row. You said:

if I then insert a new row above A6 and enter a value there

But you didn't say *exactly* where you inserted the new row. If you insert a
new row that is *within* the referenced range then Excel will include that
row in the calculation and adjust the range references accordingly. For
example:

AVERAGEIF(A1:A4,"<>0")

If you insert a new row 5 Excel does nothing since row 5 is not *within* the
referenced range. If you insert a new row 1 Excel adjusts the range to
A2:A5. If you insert a new row 4 then Excel will include that new row 4 and
adjust the references to A1:A5.