# #div/0!

#### Silo

##### Active Member
Hello

How do I get excel when using “SUM” and “Average” to disregard cells that have #DIV/0! The reason some cells would have #DIV/0! Is because there maybe no values yet. I still need the sum or average values for those cell that do have data in them

Thanks

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### texasalynn

##### Well-known Member
fix the cells with the error. Example

=if(iserror(a1/b1),0,a1/b1)

#### BAlGaInTl

##### Well-known Member
Hello

How do I get excel when using “SUM” and “Average” to disregard cells that have #DIV/0! The reason some cells would have #DIV/0! Is because there maybe no values yet. I still need the sum or average values for those cell that do have data in them

Thanks

There are a couple of ways to do this. I'm not sure which is better, but I've used both.

You can check for the error first

=IF(ISERROR(SUM(A1:A5)),"",SUM(A1:A5))

Or, you can check your range to make sure there are values first

=IF(COUNT(A1:A5)>0,SUM(A1:A5),"")

#### DonkeyOte

##### MrExcel MVP
Always best to avoid #DIV/0! in first instance by altering your formula to cater for 0 divisor (see Column D in sample), if you can't do that you need to think of arrays but handling DIV/0 in first instance would be preferred approach.

Excel Workbook
ABCDEF
11#DIV/0! 0.40.4
2240.50.5
3360.50.5
44#DIV/0!
55250.20.2
66#DIV/0!
77#DIV/0!
88#DIV/0!
99#DIV/0!
1010#DIV/0!
Sheet1

#### Silo

##### Active Member
Sorry Guys, these didn't work

DonkeyOte, I couldn't figure that that

These are the cells were I'm having that #DIV/0! as a result

=SUM(G5:G24)
=AVERAGE(I5:I24)
=SUM(L5:L24)
=AVERAGE(N5:N24)

Any help would be appriciated

Thanks

#### texasalynn

##### Well-known Member
Sorry Guys, these didn't work

DonkeyOte, I couldn't figure that that

These are the cells were I'm having that #DIV/0! as a result

=SUM(G5:G24)
=AVERAGE(I5:I24)
=SUM(L5:L24)
=AVERAGE(N5:N24)

Any help would be appriciated

Thanks

That's because somewhere within the range is a #DIV/0 error. From these suggestions it is recommended that you find those cells and fix with an if statement. HTH

How do I that?

#### texasalynn

##### Well-known Member
ok find a cell that has #div/0!

Ok here's one...

=E18/C18+F18

#### Scott Huish

##### MrExcel MVP
EDIT: Use the formula in the post below this one, it is correct.

The reason this works is because numerically speaking 0 equates to FALSE, and any non-zero number equates to TRUE.

So, what this formula is saying is if C18 equals any other number than zero, which would equate to TRUE, to go ahead and do the equation, if C18 was 0 that would equate to FALSE and would return blank ""

Last edited:

Replies
1
Views
551
Replies
3
Views
814
Replies
4
Views
595
Replies
3
Views
420
Replies
1
Views
299

1,191,693
Messages
5,988,136
Members
440,128
Latest member
virginia356

### 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