#Value must be 0

  • Thread starter Conditional Formatting
  • Start date
C

Conditional Formatting

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

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

Thanks in advance
 
D

DaveB

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$4>AF7,0,IF(ISERROR(AF7-$AF$4),0,AF7-$AF$4))

Does that help?
 
W

William Horton

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$4>AF7,0,AF7-$AF$4))),IF($AF$4>AF7,0,AF7-$AF$4),IF(ERROR.TYPE(IF($AF$4>AF7,0,AF7-$AF$4))=3,0,IF($AF$4>AF7,0,AF7-$AF$4)))

Hope this helps.

Bill Horton
 
Top