How do i average only certain cells

[Guest]

oks o i have a row of information but to keep this simple here it is


a1 b1 c1 d1 e1 f1 g1 h1 i1
5 2.5 5 2.3 0 2.4 4 2.8 7

i need to average the cells a1,c1,e1,g1,and i1
b1,d1,f1,g1 is info i dont need so to skip or ignore these values
also i need the average of a1,c1,e1,g1,and i1 while ignoreing the zeros


thanks

-
RG

 

[Guest]

RG,
I think you can type:
=AVERAGE() and within the paranthesis, type or ctrl+click the cells you want
included.

 

[T. Valko]

Try this array formula** :

=AVERAGE(IF(MOD(COLUMN(A2:I2)-COLUMN(A2),2)=0,IF(A2:I2<>0,A2:I2)))

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

 

[RagDyer]

Try this *array* formula:

=AVERAGE(IF((MOD(COLUMN(A1:I1)+1,2)=0)*(A1:I1<>0),A1:I1))

 

[Guest]

Allright i was able to finagle the formula to work in my sheet here is what
it is
{=AVERAGE(IF((MOD(COLUMN(F38:AA38)-2,5)=0)*(F38:AA38<>0),F38:AA38))}

In some rare occasions each cell that this formula pulls from might all
contain zeros
And I was wondering if there is a way to decipher from that if I get a DIV/0
error to show something different like "none"
Thanks

 

[Guest]

messed with it a little more and i was able to figure it out really easy.
unless theres is another way but this works

{=IF(AND(G38=0,L38=0,Q38=0,V38=0,AA38=0),"None",AVERAGE(IF((MOD(COLUMN(F38:AA38)-2,5)=0)*(F38:AA38<>0),F38:AA38)))}