#Value must be 0
 

I have a time formula.
=IF($AF$4AF7,0,AF7-$AF$4)

Can you help me fina a way when the result is #value it must turn to zero.

Thanks in advance

Without seeing your sheet it's a little confusing what is causing the #value
to appear, in the following example i'm assuming it is the subtraction. If
not, nest the ISERROR(...) check appropriately.

=IF($AF$4AF7,0,IF(ISERROR(AF7-$AF$4),0,AF7-$AF$4))

Does that help?
--
Regards,

DavidB


"Conditional Formatting" wrote:

I have a time formula.
=IF($AF$4AF7,0,AF7-$AF$4)

Can you help me fina a way when the result is #value it must turn to zero.

Thanks in advance

Use the ISERROR function, e.g. IF(ISERROR(something),0,something)
--

Simon


"Conditional Formatting" wrote:

I have a time formula.
=IF($AF$4AF7,0,AF7-$AF$4)

Can you help me fina a way when the result is #value it must turn to zero.

Thanks in advance

Try the below formula. If the formula produces the #Value error only the
result will be 0. Otherwise the result will be whatever it is, even if it is
another type of error.

=IF(NOT(ISERROR(IF($AF$4AF7,0,AF7-$AF$4))),IF($AF$4AF7,0,AF7-$AF$4),IF(ERROR.TYPE(IF($AF$4AF7,0,AF7-$AF$4))=3,0,IF($AF$4AF7,0,AF7-$AF$4)))

Hope this helps.

Bill Horton

"Conditional Formatting" wrote:

I have a time formula.
=IF($AF$4AF7,0,AF7-$AF$4)

Can you help me fina a way when the result is #value it must turn to zero.

Thanks in advance