One way:

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


In article ,
"Ken" wrote:

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