I need to average multiple cells that are not continuous and ignore zero
values. The formula to ignore =AVERAGE(IF(A2:A7<0, A2:A7,"")) does not work
for single cell entries (i.e. A2,A4, A6, A8) Is there a way to do this?

Is it always every other cell you can use

=AVERAGE(IF((MOD(ROW(A2:A100)-ROW(A2),2)=0)*(A2:A100<0),A2:A100))

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom

"Christy" wrote in message
...
I need to average multiple cells that are not continuous and ignore zero
values. The formula to ignore =AVERAGE(IF(A2:A7<0, A2:A7,"")) does not
work
for single cell entries (i.e. A2,A4, A6, A8) Is there a way to do this?

If there number of cells to be averaged is small
=(A2+A4+A6+A8)/((A2<0)+(A4<0)+(A6<0)+(A8<0))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Christy" wrote in message
...
I need to average multiple cells that are not continuous and ignore zero
values. The formula to ignore =AVERAGE(IF(A2:A7<0, A2:A7,"")) does not
work
for single cell entries (i.e. A2,A4, A6, A8) Is there a way to do this?

Try this array formula** :

=AVERAGE(IF((MOD(ROW(A2:A8)-ROW(A2),2)=0)*(A2:A8<0),A2:A8))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Christy" wrote in message
...
I need to average multiple cells that are not continuous and ignore zero
values. The formula to ignore =AVERAGE(IF(A2:A7<0, A2:A7,"")) does not
work
for single cell entries (i.e. A2,A4, A6, A8) Is there a way to do this?

No, unfortunately not. Sometimes it is every other, sometimes it can be
every third or fourth cell.

"Peo Sjoblom" wrote:

Is it always every other cell you can use

=AVERAGE(IF((MOD(ROW(A2:A100)-ROW(A2),2)=0)*(A2:A100<0),A2:A100))

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom

"Christy" wrote in message
...
I need to average multiple cells that are not continuous and ignore zero
values. The formula to ignore =AVERAGE(IF(A2:A7<0, A2:A7,"")) does not
work
for single cell entries (i.e. A2,A4, A6, A8) Is there a way to do this?

try this

=AVERAGE(IF(A2:A7<0,A2:A7)) ( use ctrl + shift + enter )



On Dec 10, 2:12*am, Christy wrote:
I need to average multiple cells that are not continuous and ignore zero
values. The formula to ignore =AVERAGE(IF(A2:A7<0, A2:A7,"")) does not work
for single cell entries (i.e. A2,A4, A6, A8) *Is there a way to do this?