Average excluding zero-value cells

[andrez]

Hello everyone.

Here is the situation

I have 6 non-adjacent cells with some changing values.
Sometimes these are zero values.

How to calculate total average of those cells excluding zero values?


Thank Yo

 

[Leo Heuser]

Hello Andrez

One way:

=SUM(C2,C4,C5,C7,C9,C11)/
((C2<>0)+(C4<>0)+(C5<>0)+(C7<>0)+(C9<>0)+(C11<>0))

 

[Peo Sjoblom]

Another way

=SUM(Lst)/SUMPRODUCT(--(LARGE((Lst),ROW(INDIRECT("1:"&COUNT(Lst))))<>0))

where the different cells are a named range called Lst

for a small numbers of cells it's hardly worth it but for a greater number
it is



--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

Hello Andrez

One way:

=SUM(C2,C4,C5,C7,C9,C11)/
((C2<>0)+(C4<>0)+(C5<>0)+(C7<>0)+(C9<>0)+(C11<>0))

 

[Leo Heuser]

Nice one, Peo!

 

[Harlan Grove]

Another way

=SUM(Lst)/SUMPRODUCT(--(LARGE((Lst),ROW(INDIRECT("1:"&
COUNT(Lst))))<>0))

where the different cells are a named range called Lst

for a small numbers of cells it's hardly worth it but for a greater
number it is

....

For a large number of cells, that LARGE could become mighty slow. So another
way would be

=SUM(Lst)/INDEX(FREQUENCY(Lst,{0}),2)

if the nonzero values would all be positive, or

=SUM(Lst)/(COUNT(Lst)-INDEX(FREQUENCY(Lst,{-1,0}),2))

if the nonzero values could be positive or negative.