Average without counting zeros

T. Valko

Well-known Member

Joined

May 9, 2009

Messages

16,623

• May 6, 2011

• #2

Vintage79 said:

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!

Click to expand...

Pretty close!

Try it like this...

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

 

Upvote 0

K

kpark91

Well-known Member

Joined

Jul 15, 2010

Messages

1,582

• May 6, 2011

• #3

Try

Rich (BB code):

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

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• May 6, 2011

• #4

Try

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

confirmed with CTRL + Shift + Enter.

 

Upvote 0

Scott Huish

MrExcel MVP

Joined

Mar 17, 2004

Messages

20,649

Office Version

1. 365

Platform

1. Windows

• May 6, 2011

• #5

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

 

Upvote 0

Vintage79

Board Regular

Joined

May 29, 2007

Messages

187

Office Version

1. 2019

Platform

1. Windows

• May 6, 2011

• #6

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?

 

Upvote 0

Scott Huish

MrExcel MVP

Joined

Mar 17, 2004

Messages

20,649

Office Version

1. 365

Platform

1. Windows

• May 6, 2011

• #7

The AVERAGEIF function is only available in XL2007 or higher.

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• May 6, 2011

• #8

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

 

Upvote 0

Vintage79

Board Regular

Joined

May 29, 2007

Messages

187

Office Version

1. 2019

Platform

1. Windows

• May 6, 2011

• #9

HOTPEPPER said:

The AVERAGEIF function is only available in XL2007 or higher.

Click to expand...

Ok, that explains it!

 

Upvote 0

T. Valko

Well-known Member

Joined

May 9, 2009

Messages

16,623

• May 6, 2011

• #10

Vintage79 said:

I am using Office 2000

Click to expand...

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.

 

Upvote 0