#div/0!

Silo

Active Member
Joined
Mar 8, 2004
Messages
447
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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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),"")
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
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 :)
 
Upvote 0
ok find a cell that has #div/0!
copy that formula back here and someone will help you.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top