Excel - Averaging

C

Christy

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?
 
P

Peo Sjoblom

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
 
B

Bernard Liengme

If there number of cells to be averaged is small
=(A2+A4+A6+A8)/((A2<>0)+(A4<>0)+(A6<>0)+(A8<>0))
best wishes
 
T

T. Valko

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)
 
C

Christy

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top