#DIV/0 error

duewaynec

Board Regular
Joined
Jan 21, 2005
Messages
65
hi,

how do i get rid of this error when values are zero's and it needs to be divided. is there a way to get it to display a zero as well?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

jdc

Active Member
Joined
Nov 23, 2004
Messages
263
Hi

Use ISERROR in your formula,

ie =IF(ISERROR(A1/B1),0,A1/B1)

John
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
duewaynec said:
hi,

how do i get rid of this error when values are zero's and it needs to be divided. is there a way to get it to display a zero as well?

Filter the divisor with an IF, no need for ISERROR.
 

duewaynec

Board Regular
Joined
Jan 21, 2005
Messages
65

ADVERTISEMENT

ok so heres one formula that produces the #DIV/0 error i'm trying to get rid of.

=SUM(C31/(C21+C22+C25+C26+C27))
the result is in percentage format.
this results in the error if any of the cells being added has a value of zero.
 

~Zodiac~

Board Regular
Joined
Jan 10, 2005
Messages
70
Range("C21").Select
Cell1=ActiveCell.Address
Range("C22").Select
...

if Cell1="0" or Cell2="0" or Cell3="0" or Cell4="0" or Cell5="0" then
range(TargetCell).Select 'Change this to whatever your calculating cell is
ActiveCell.Value="0"
Else
range(TargetCell).Select
ActiveCell.Formula="=SUM(C31/(C21+C22+C25+C26+C27)) "
end if


HTH

edit: defining Cells and putting the value Cellnumber in the if.
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
duewaynec said:
ok so heres one formula that produces the #DIV/0 error i'm trying to get rid of.

=SUM(C31/(C21+C22+C25+C26+C27))
the result is in percentage format.
this results in the error if any of the cells being added has a value of zero.

Try,

=C31/SUM(MAX(1,(C21:C22,C25:C27)))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,840
Messages
5,766,729
Members
425,375
Latest member
Bradleyckx

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
Top