I'm trying to get the average for a range of #'s with #DIV/0

[afs24]

I'm trying to calculate the average for a range of cells [100-500-500,000-DIV/0!]. The answer should be 16,686 because it shouldn't count DIV. I'm using the following formula:
=IF(ISERROR(B4:E4),"",SUM(B4:E4)/COUNT(B4:E4))
but when #DIV/0! is in one of the cells I get #DIV/0! as my answer. If my range of cells is all numbers [100-500-500,000-1,000]I get the average that I'm looking for. Is there a formula to use so that if I have DIV in my range I get my average not counting the DIV cell and what if I have a 0 instead of DIV. thanks!

 

[PaddyD]

Hi,

using the data as per the example below, you could use an arry formula:

=AVERAGE(IF(A2:D2,A3:D3))

this needs to be entered using control + shift + enter, not just enter. The formula is checking for values<> 0 in the range that's causing the #div/0 errors, & just averageing the corresponding cells in the relevant range.

Change the references to match your data:

Book2
	A	B	C	D	E
1	5	10	15	20
2	5	5	0	5
3	1	2	#DIV/0!	4
4
5
6		Normal	#DIV/0!
7		Array	2.333333
8
9
Sheet1

Paddy
This message was edited by PaddyD on 2002-10-01 21:29

 

[Aladin Akyurek]

On 2002-10-01 21:18, afs24 wrote:
I'm trying to calculate the average for a range of cells [100-500-500,000-DIV/0!]. The answer should be 16,686 because it shouldn't count DIV. I'm using the following formula:
=IF(ISERROR(B4:E4),"",SUM(B4:E4)/COUNT(B4:E4))
but when #DIV/0! is in one of the cells I get #DIV/0! as my answer. If my range of cells is all numbers [100-500-500,000-1,000]I get the average that I'm looking for. Is there a formula to use so that if I have DIV in my range I get my average not counting the DIV cell and what if I have a 0 instead of DIV. thanks!

I suppose this new question is related to the previous...

http://216.92.17.166/board/viewtopic.php?topic=23673&forum=2

You should avoid producing #DIV/0! errors. The template for that is

=IF(denominator,nominator/denominator,"")

Having said that...

=SUMIF(A1:A4,"<>#DIV/0!")/MAX(1,SUMPRODUCT((ISNUMBER(A1:A4)+0)))

computes the desired average while it ignores #DIV/0! errors.

If you also want to ignore the 0 values (as per your previous question)...

=SUMIF(A1:A4,"<>#DIV/0!")/MAX(1,SUMPRODUCT((ISNUMBER(A1:A4)+0))-COUNTIF(A1:A4,0))

Or, array-enter (using control+shift+enter)...

=AVERAGE(IF(ISNUMBER(A1:A4),IF(A1:A4,A1:A4)))
This message was edited by Aladin Akyurek on 2002-10-02 00:44

 

[PaddyD]

Just FYI - Aladin's first formula compensates for blanks / test entries in the range as well as #DIV/0 errors (the one I posted would only compensate for text entries & the error):

Book2.xls
	A	B	C	D
1	5	10	15	20
2	5	5	0	5
3	1		#DIV/0!	4
4
5
6		Normal	#DIV/0!
7		Array	1.666667
8		Aladin'ssumif	2.5
9
Sheet1

Paddy

 

[Nogslaw]

I have the same issue, but my numbers are not in a sequential array. My data is every 18 rows. My formula example is

=AVERAGE(A1,A19,A37,A55) . . . etc, etc.

How do I create the array so that it correctly looks at all those numbers?
I tried this formula entered as an array formula, but it said there was an error.

=AVERAGE(IF({A1,A19,A37,A55},{A1,A19,A37,A55}))

I am at a loss on this one.

 

[Aladin Akyurek]

I have the same issue, but my numbers are not in a sequential array. My data is every 18 rows. My formula example is

=AVERAGE(A1,A19,A37,A55) . . . etc, etc.

How do I create the array so that it correctly looks at all those numbers?
I tried this formula entered as an array formula, but it said there was an error.

=AVERAGE(IF({A1,A19,A37,A55},{A1,A19,A37,A55}))

I am at a loss on this one.

[1] If no error values in the range of interest:

=AVERAGE(IF(MOD(ROW($A$1:$A$700)-ROW($A$1)+0,18)=0,$A$1:$A$700))

[2] Otherwise:

=AVERAGE(IF(ISNUMBER((MOD(ROW($A$1:$A$700)-ROW($A$1)+0,18)=0)*($A$1:$A$700)),$A$1:$A$700,""))

Each of these formulas must be confirmed with control+shift+enter instead of just with enter.

BTW, if you just have 4 cells to average, you can get away with a non-expensive formula that checks each cell for a number.

 

[Big V]

Not sure if this helps but if you use an If(iserror in any division formula you can avoid #DIV/0 errors

Eg =IF(ISERROR(H6/G6),"--",H6/G6) retuns "--" instead of #DIV/0

I tend to use the if(iserror formual as standard now whihc solves a lot of these sort of problems

 

[Aladin Akyurek]

Not sure if this helps but if you use an If(iserror in any division formula you can avoid #DIV/0 errors

Eg =IF(ISERROR(H6/G6),"--",H6/G6) retuns "--" instead of #DIV/0

I tend to use the if(iserror formual as standard now whihc solves a lot of these sort of problems

Standard? Why not:

=IF(G6,H6/G6,"--")

=IF(N(G6),H6/G6,"--")

Or even:

=IF(ISNUMBER(H6/G6),H6/G6,"--")