Here is an array example of how MOD could come into play;

=AVERAGE(IF((MOD(COLUMN(A1:P1)-1,3)=0)*(A1:P1<0),A1:P1))

with your values in A1:P1, averaging every 3rd column if < 0.





"Mike" wrote in message
...
Myrna,
Thanks, but not sure if I follow your idea. I "simply" want to average
only
those cells in A1, A3, and A5 that have a value <0 . Not sure how MOD
would
work in this case.

"Myrna Larson" wrote:

But A2:A7 is not a group of non-contiguous cells. I think the example he
gave
was A1, A3, A5

That can be solved with an array formula that involves MOD and the the
row
number, but if he wants something like A2:A7, B9:B13, C1:C3, etc., that
won't
work, either.

Another possibility is to select the various cells and assign a name to
them.
Then a simple =AVERAGE(MyRange) will do.

On Thu, 3 Mar 2005 23:00:00 -0000, "Andy Brown"

wrote:

"Mike" wrote in message
...
Averaging noncontiguous numbers ignoring zeros?

=AVERAGE(IF(A2:A7<0, A2:A7,"")) . I've not been able to revise this
formula to calculate noncontiguous data. Any ideas?

=SUM(A2:A7)/COUNTIF(A2:A7,"0")

, possibly.

HTH,
Andy