Eliminate #DIV/0! error in Avergage formula

[DJPaton]

Hi
I am trying to eliminate the #DIV/0! error when the cells are empty in a simple average formula =AVERAGE(F75:H75). I have tried =IF(D75=0,0,(AVERAGE(B75:D75))) which eliminates the error but doesn't return the average of cells F and G. I am sure the answer is in front of me but can't see it so I thought I would ask the MATERS! Any help will be greatly appreciated.

 

[Colin Legg]

This will only return an average if at least 1 cell has a number

=IF(COUNT(F75:H75),AVERAGE(F75:H75),0)

 

[DJPaton]

Thank you very much Colin that has worked perfectly!

All the best.

Derek

P.S. GO ENGLAND!!

 

[scottylad2]

While Colins formula will work with numbers, it would also include 0 values. If it's just numbers greater than 0 to be averaged, =AVERAGEIF(H4:H11,">"&0,H4:H11)

or if your version doesn't have Averageif

=SUM(H4:H11)/COUNTIF(H4:H11,">"&0)

 

[CWatts]

I'm always partial to these:

Excel 2007+:
=IFERROR(AVERAGE(F75:H75),"")

Excel 2003-:
=IF(ISERROR(AVERAGE(F75:H75)),"",AVERAGE(F75:H75))

 

[DJPaton]

Hi scottylad2

It would only be values over 0 that I would be using - that or blank until the value is known.

When I tried the =AVERAGEIF(H4:H11,">"&0,H4:H11) formula I get an #NAME? error which I assume is becuase my version doesn't support the formula. The =SUM(H4:H11)/COUNTIF(H4:H11,">"&0) formula still returns a #DIV/0! error - am I doing something wrong?
Thanks for your help.

 

[DJPaton]

Hi CWatts
Yes that works brilliantly and I like it especially because it returns a blank cell until the values are entered / known, even better!
Thanks so much and thanks to all.
Cheers

Derek

 

[scottylad2]

Excel Workbook
	E	F
2	5.4	10
3		3
4		7
5
6		2
7
8		5
Sheet1

Excel 2010

Cell Formulas
Range	Formula
E2	=IFERROR(SUM(F2:F8)/COUNT(IF(F2:F8>0,F2:F8,""))"")

What version of Excel are you using?

this will also remain blank till values are added

 

[DJPaton]

Thanks scottylad2, that works also.
Nice one.
Again, thanks everyone.
Cheers
Derek

 

[T. Valko]

Hi scottylad2

It would only be values over 0 that I would be using...
The =SUM(H4:H11)/COUNTIF(H4:H11,">"&0) formula still returns a #DIV/0! error - am I doing something wrong?
Thanks for your help.

That formula will return the #DIV/0! error if there are no numbers >0 in the range.

However, if the numbers you're dealing with will only be >0 then there's no need to use the COUNTIF(H4:H11,">"&0). You can simply use AVERAGE like Colin suggested.