Averaging non consecutive cells excluding zero's

  • Thread starter Thread starter stumpy
  • Start date Start date
S

stumpy

how can I average non consecutive cells in a row excluding zero's
eg.
A1=2
D1=6
F1=0
J1=4
all cells in the range (A1:K1)have values in them but only the sum of cetain
cell need to be averaged

any help is a appreciated.
 
=SUM(A1,D1,F1,J1)/(IF(A1<>0,1,0)+IF(J1<>0,1,0)+IF(F1<>0,1,0)+IF
(J1<>0,1,0))

I didn't condtiional the sum as adding zero wouldn't change anything.
 
Sorry, that should be:

=SUM(A1,D1,F1,J1)/(IF(A1<>0,1,0)+IF(D1<>0,1,0)+IF(F1<>0,1,0)+IF
(J1<>0,1,0))

A little too much copy & paste in the formula bar in the original
reply.
 
Not very cleaver but it works:
=(A1+D1+F1+J1)/(4-(A1=0)-(D1=0)-(F1=0)-(J1=0))
best wishes
 
Back
Top