Averaging non consecutive cells excluding zero'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.

 

[google]

=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.

 

[google]

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.

 

[Bernard Liengme]

Not very cleaver but it works:
=(A1+D1+F1+J1)/(4-(A1=0)-(D1=0)-(F1=0)-(J1=0))
best wishes

 

[stumpy]

Thanks for the speedy replys much appreciated.

Not very cleaver but it works:
=(A1+D1+F1+J1)/(4-(A1=0)-(D1=0)-(F1=0)-(J1=0))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email