CODEWITHSUNDEEP
excelexcel-formula
moe
moe

Reputation: 5249

How to remove #DIV/0 errors in excel

I am trying to remove or replace the DIV error with blank and i have tried to use the ISERROR function but still does not work. This is what it looks like my data:

       COLA  COLB   COLC
ROW1    $0    $0   #DIV/0
ROW2               #VALUE!

so i get these kind of errors when i have something like above and i would like to replace with blanks. Here is my formula that does not work. thanks

=IF((ISERROR(D13-C13)/C13),"",(D13-C13)/C13)

Upvotes: 3

Views: 62063

Answers (6)

Vladimir Reshetnikov
Vladimir Reshetnikov

Reputation: 11826

Select the whole spreadsheet, then under menu Home - Conditional Formatting - New Rule... - Select Format only cells that contain - Under Format only cells with select Errors - Click Format... button - Go to the Font tab - Under Color select the same font color as the background (such as white).

New Rule Dialog

Upvotes: 0

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15611

A better formula that appears to suit your question is

=IFERROR((D13-C13)/C13,"")

Incidentally, it is less prone to errors as using mismatched formulas for the condition tested and the result on no-error (the present case can be regarded of this type).

If you want to stick to ISERROR, then the solution by teylyn rules, of course.

Upvotes: 10

Magic Bullet Dave
Magic Bullet Dave

Reputation: 9076

Another approach is to leave the errors in the sheet and hide them. This is sometimes useful, for instance #NA errors in a column of data when plotted on a chart show as missing rather than zero.

To hide them use conditional formatting, in the formula box

=ISERROR(C13)

and in the format box make the font colour white.

Upvotes: 1

teylyn
teylyn

Reputation: 35925

The suggestions are all valid. The reason why your original formula does not work is the wrong placement of the round brackets. Try

=IF(ISERROR((D13-C13)/C13),"",(D13-C13)/C13)

Upvotes: 12

tigeravatar
tigeravatar

Reputation: 26650

Give this a try:

=IF(C13=0,0,(D13-C13)/C13)

Upvotes: 2

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32700

Why remove the error, and instead just don't divide by zero?

=IF(C13=0,"",(D13-C13)/C13)

Upvotes: 6

Related Questions