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

#### afs24

##### Board Regular
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!

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
ABCDE
15101520
25505
312#DIV/0!4
4
5
6Normal#DIV/0!
7Array2.333333
8
9
Sheet1

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

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

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
ABCD
15101520
25505
31#DIV/0!4
4
5
6Normal#DIV/0!
7Array1.666667
9
Sheet1

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.

Nogslaw said:
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.

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

Big V said:
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,"--")

Replies
5
Views
349
Replies
3
Views
175
Replies
2
Views
116
Replies
2
Views
305
Replies
9
Views
333

1,221,214
Messages
6,158,571
Members
451,501
Latest member
andysacko

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back