Average without counting zeros

[Vintage79]

Hello all,

I know this is terribly basic, but I wasn't able to find the answer in the Excel Help file, or by searching the forum.

I have a list of numbers in G6:G10000 and would like to find the average value, without counting any zero values. I thought it would be something like:

=AVERAGEIF(G6:G10000>0)

But this didn't work!

 

[VoG]

Ok, then try this array entered version which will work in all versions of Excel.

=AVERAGE(IF(G6:G10000>0,G6:G10000))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Hmm http://www.mrexcel.com/forum/showpost.php?p=2707869&postcount=4

 

[T. Valko]

Hmm http://www.mrexcel.com/forum/showpost.php?p=2707869&postcount=4

I thought I'd include the >0 version as it seemed to more closely emulate the OP's "pseudo-formula".