Blanks or zeros in formulas
 

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

Blanks or zeros in formulas
 

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

On Thu, 21 Aug 2008 16:49:00 -0700, Dannn78
wrote:

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

Blanks or zeros in formulas
 

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

On Aug 22, 12:49*am, Dannn78
wrote:
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

Blanks or zeros in formulas
 

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.



Dannn78 wrote:

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

--

Dave Peterson