Average with zeroes obstacle

[apwdweb]

HI,

I have column for which i'd like to the average, however the column has about 4 numbers and the rest are zeroes. I'd like excel to give me the average of this column but ignore any zeroes as values to include.

= AVG( ???)

or maybe you know a better way. I"m open to your ideas.

Thanks

 

[Aladin Akyurek]

In addition to other fine contributions, with blanks and text entries in the midst, you may also want to try the following non-array formulation ...

=SUM(A1:A10)/SUMPRODUCT(--(A1:A10<>0)*(ISNUMBER(A1:A10)))

A "fine" superexpensive contribution. Congrats. Beautiful too: A gracious combination of -- and *.

 

[Yogi Anand]

Hi Aladin:

You had compiled a list of various submissions -- the last one I submitted is another formulation. I recognize it is not economical, and thanks for pointing out the redundant coercing with --

By the way, why not leave the sarcasm out and talk to each other professionally -- let me know if that is asking for too much!

 

[Aladin Akyurek]

...

the last one I submitted is another formulation. I recognize it is not economical...

Then you shouldn't submit.

 

[Yogi Anand]

...

the last one I submitted is another formulation. I recognize it is not economical...

Then you shouldn't submit.

OK buddy -- if it makes you feel better to keep riding the high horse -- it is OKay by me!