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!

 

[T. Valko]

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!

Pretty close!

Try it like this...

=AVERAGEIF(G6:G10000,">0")

 

[kpark91]

Try

=AVERAGEIF(G6:G10000, ">0")

 

[VoG]

Try

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

confirmed with CTRL + Shift + Enter.

 

[Scott Huish]

Excel Workbook
	G	H	I
6	0		4
7	7
8	9
9	-4
Sheet1

 

[Vintage79]

Thanks a lot for the fast responses!

The first two didn't work for me - they returned #NAME? I have no idea why, but I am using Office 2000, maybe I should have mentioned that.

VoG's one works perfectly - thanks!

UPDATE: Hotpepper, yours returns #NAME? too! I'm sure you three cannot all be wrong, so I wonder why those don't work for me?

 

[Scott Huish]

The AVERAGEIF function is only available in XL2007 or higher.

 

[VoG]

AVERAGEIF was introduced in Excel 2007, that's why.

 

[Vintage79]

The AVERAGEIF function is only available in XL2007 or higher.

Ok, that explains it!

 

[T. Valko]

I am using Office 2000

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.