MOD would work in the case of A1, A3, and A5, where you want every-other
column. The array formula would be

=AVERAGE(IF(MOD(ROW(A1:A5),2)=1,IF(A1:A5<0,A1:A5) ))

But that will not work for the example/solution you posted most recently,
which refers to cells C26, G26, and K26. The column numbers are 3, 7, and 11,
so there's no numeric relationship between them.

OTOH, if you wanted C26, G26, and *J26*, you could use

MOD(COLUMN(C26:J26),4)=3

because that's every 4th column.


On Thu, 3 Mar 2005 15:25:06 -0800, "Mike"
wrote:

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