#DIV/0 error

duewaynec

Board Regular
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jdc

Active Member
Hi

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

John

duewaynec

Board Regular
thanks. works perfectly

Brian from Maui

MrExcel MVP
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
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
Range("C21").Select
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
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)))

Replies
1
Views
48
Replies
8
Views
125
Replies
2
Views
202
Replies
10
Views
167
Replies
0
Views
45

1,181,994
Messages
5,933,162
Members
436,883
Latest member
RyanI1986

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.

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

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