• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 132
  • Last Modified:

Getting rid of the dreaded #DIV/0!

When I put in a formula with a division I get #DIV/0! anytime there is a zero or null value, how can this me returned as just blank?

Sample

=SUM(J12/J13)
0
Matt Pinkston
Asked:
Matt Pinkston
1 Solution
 
MINDSUPERBCommented:
You may try to use an IF function like:

=IF(J13=0,0,J12/J13)

Sincerely,

Ed
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

test the values of the cells first, especially the J13 cell. Also, you don't need to wrap a division into a Sum() function.

=if(J13>0,J12/J13,"")

This formula will only undertake the division if the value in cell J13 is greater than Zero. If it is not greater than zero, it will just return an empty text value.

cheers, teylyn
0
 
SteveCommented:
I would tend towards the rather handy IFERROR as this will capture more errors than just DIV0:

=IFERROR(J12/J13,"")
0
 
helpfinderIT ConsultantCommented:
use your formula with IFERROR like this:
=IFERROR(SUM(J12/J13);"")

see in my sample
sample.xlsx
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now