getting rid of #DIV/o!

Randi_M

New Member
Joined
May 20, 2003
Messages
14
Is there a way to get rid of that #DIV/0! error? I have a workbook I use as a template for different projects, and it starts out blank each time. If I don't use a certain row in a certain project, then I have a column which reads #div/0!

Example: =sum(g13/c13) c13=0, but a number will be filled in later.

Please! Help!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Randi_M said:
Is there a way to get rid of that #DIV/0! error? I have a workbook I use as a template for different projects, and it starts out blank each time. If I don't use a certain row in a certain project, then I have a column which reads #div/0!

Example: =sum(g13/c13) c13=0, but a number will be filled in later.

Please! Help!

Why are you summing a single value?
 
Upvote 0
Your formula quoted was:

=sum(g13/c13)

G13/C13 is a number so there is no need for the SUM().
 
Upvote 0
I think this will give you the desired...

Put the following in the corresponding cell reference (only the blue text):

A1 - 0
B1 - 1000
C1 - =IF(ISERROR(B1/A1),"N/A",B1/A1)


The "N/A" ref in the C1 formula will display a N/A for each #DIV/0! error message... If you wish it to say something different (text wise), type whatever you wish between the quotes in the formula... If you wish the cell to display nothing, just remove the N/A from the formula but LEAVE the quotes... The quotes must be left to display nothing (i.e =IF(ISERROR(B1/A1),"",B1/A1))... Also note if you wish a numeric value to be displayed or another formula to occur in the event this eror message happens, you must type that value/formula in the area of the formula that reads "N/A" (i.e =IF(ISERROR(B1/A1),sum(A1:B1),B1/A1))(yes, remove the quotes in this case - quotes are only used to denote that a text field will be placed in the cell)... Good Luck.
 
Upvote 0
ddubnansky said:
I think this will give you the desired...

Put the following in the corresponding cell reference (only the blue text):

A1 - 0
B1 - 1000
C1 - =IF(ISERROR(B1/A1),"N/A",B1/A1)


The "N/A" ref in the C1 formula will display a N/A for each #DIV/0! error message... If you wish it to say something different (text wise), type whatever you wish between the quotes in the formula... If you wish the cell to display nothing, just remove the N/A from the formula but LEAVE the quotes... The quotes must be left to display nothing (i.e =IF(ISERROR(B1/A1),"",B1/A1))... Also note if you wish a numeric value to be displayed or another formula to occur in the event this eror message happens, you must type that value/formula in the area of the formula that reads "N/A" (i.e =IF(ISERROR(B1/A1),sum(A1:B1),B1/A1))(yes, remove the quotes in this case - quotes are only used to denote that a text field will be placed in the cell)... Good Luck.

Your use of ISERROR is like swatting flies with a sledgehammer. See Aladin's recommendation above for a more "surgical" approach.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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