#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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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

Forum statistics

Threads
1,147,697
Messages
5,742,688
Members
423,747
Latest member
Shadeslayers09

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