FORMULA

[BRIAN]

Hi
I am having difficulty in entering a formula
i have created a time sheet and am using TExt to check
the differnce between cells / times this is fine
but when i leave a cell blank it gives me the error code
#value!
and this messes up my total box
can i change the #value! to be seen as 0
any help will be appreciated

 

[Leo Heuser]

Hi Brian

Try:

=IF(ISERROR(YourFormula),0,YourFormula)

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only, please.

 

[Mike]

The next time you post a question, please include the
formula that gives the problem. There are different
possible solutions, depending on your formula.

The simple solution is to use the ISERROR function wrapped
around your function, and contained within an IF function.
=IF(ISERROR(YourFormula),0,YourFormula)

But often only one piece of YourFormula is responsible for
the error, so you should extract that bit to use as the
ISERROR parameter. For example, if you have a formula
=INDEX(A:A,MATCH(B1,C:C,0)), the MATCH portion might cause
an error, but the INDEX portion won't, so you would use:
=IF(ISERROR(MATCH(B1,C:C,0)),0,INDEX(A:A,MATCH(B1,C:C,0)))

Alternately you could just test a cell to see what it
contains using something like this.
=IF(A1="",0,YourFormula)