Creating average over DIV/0 inclusive range

[Sayth]

HI

If I have a simple range G4:G17 and I want to calculate an average of the range you would use Average(G4:G17). However if the range sometimes contains DIV/0 in a field how would I calculate the average of the range.

I thought that if I said to sumif the range if not div/0 and divide it by the countif not div/0 I would get the range average. But I must be writing it wrong its not working.

My Code:

=(SUMIF((G4:G17,"<>#DIV/0!")))/(COUNTIF(IF(G4:G17,"<>#DIV/0!")))

 

[repairman615]

Hello,

In 2007 there is an AVERAGEIF()

Maybe that could help.

Jeff

 

[repairman615]

<TABLE style="WIDTH: 305pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=406><COLGROUP><COL style="WIDTH: 305pt; mso-width-source: userset; mso-width-alt: 7424" width=406><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #eaf1dd; WIDTH: 305pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=406>=AVERAGEIF(L11:L16,"<>#DIV/0!")</TD></TR></TBODY></TABLE>

 

[Sayth]

thanks

 

[Aladin Akyurek]

HI

If I have a simple range G4:G17 and I want to calculate an average of the range you would use Average(G4:G17). However if the range sometimes contains DIV/0 in a field how would I calculate the average of the range.

I thought that if I said to sumif the range if not div/0 and divide it by the countif not div/0 I would get the range average. But I must be writing it wrong its not working.

My Code:

=(SUMIF((G4:G17,"<>#DIV/0!")))/(COUNTIF(IF(G4:G17,"<>#DIV/0!")))

These two functions can be made to work...

=SUMIF(G4:G17,"<"&9.99E+307)/MAX(1,COUNTIF(G4:G17,"<"&9.99E+307))

If G4:G17 is guaranteed to solely house numeric values >= 0, then:

=SUMIF(G4:G17,">0")/MAX(1,COUNTIF(G4:G17,">=0"))

 

[T. Valko]

HI

If I have a simple range G4:G17 and I want to calculate an average of the range you would use Average(G4:G17). However if the range sometimes contains DIV/0 in a field how would I calculate the average of the range.

I thought that if I said to sumif the range if not div/0 and divide it by the countif not div/0 I would get the range average. But I must be writing it wrong its not working.

My Code:

=(SUMIF((G4:G17,"<>#DIV/0!")))/(COUNTIF(IF(G4:G17,"<>#DIV/0!")))

In Excel 2007 or later...

Book1
	G
4	88
5	39
6	82
7	#DIV/0!
8	52
9	49
10	15
11	#DIV/0!
12	23
13	52
14	86
15	25
16	82
17	52
Sheet1

=AVERAGEIF(G4:G17,"<1E100")