Blanks or zeros in formulas

[Dannn78]

Hi All,

Can someone please help me.

Im doing a very simple formula

=D5-C5
=D6-C6
=D7-C7 etc etc etc

But on the cells in column c or d occasionally it will be blank or "0".

Everytime one of these cells is blank or zero i get the response #VALUE

Is there a formula I can use so that instead of getting the error message

 

[Gord Dibben]

You should not be getting an error if the cells are truly blank or 0

If "0" then you would get the error because "0" is text, not a number.

If the cells contain formulas that return "" you will get the error

If you get an error with a blank cell, perhaps it is not really blank but
contains a <space> or two.


Gord Dibben MS Excel MVP

 

[Pete_UK]

You didn't seem to finish your sentence, so I will guess that you want
a blank to show instead of the error message. Try this:

=IF(ISERROR(D5-C5),"",D5-C5)

Then copy this down. You could change the "" to whatever you like.

I suspect you don't have a true zero or blank in those offending cells
- more like "0" or "" to give you the #VALUE error.

Hope this helps.

Pete

 

[Dave Peterson]

If you're getting that #Value! error, that means that at least one of the two
cells (maybe both) really aren't empty.

It could contain a space character or any whitespace character.

Or it could contain a formula that evaluates to ""
like
=if(x1="ok","","Error")

Or it could have been a formula that evaluated to "" and then was converted to
values. (That cell won't be empty, but will look blank.)

I'd just select the range (a1 and/or b1) and hit the delete key to clear the
contents.

Then the cell(s) will really be empty.