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

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

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.

--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
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