Excel 2003 Suppressing Errors

  • Thread starter Thread starter Chris Hankin
  • Start date Start date
C

Chris Hankin

Hello,

The following function works fine, however, if there are no dates
populated into my spreadsheet, the resultant cells in column H show the:
#VALUE! error.

=+IF(AND($B$1-G3<-24,$B$1-G3>-31),"D",IF($B$1-G3<0,"C",IF($B$1-G3>=0,"E"
," ")))

Could someone please advise on how I can suppress this error using an
Excel function such as ISERROR or ISBLANK?

Any help would be greatly appreciated.

Kind regards,

Chris.
 
Try:

=IF(ISERROR(your formula),"",your formula)

You could check if either of the dates is blank

=IF(OR(date1="",date2=""),"",your formula)

Regards

Trevor
 
Thanks Trevor for your help.

Please note that my original formula is:

=+IF(AND($B$1-G3<-24,$B$1-G3>-31),"D",IF($B$1-G3<0,"C",IF($B$1-G3>=0,"E"
," ")))

Your suggested solution is:

=IF(ISERROR(your formula),"",your formula)


So, I ended up with the following formula which for some unkown reason
Excel 2003 does not like:

=IF(ISERROR(=+IF(AND($B$1-G3<-24,$B$1-G3>-31),"D",IF($B$1-G3<0,"C",IF($B
$1-G3>=0,"E","
")))),"",=+IF(AND($B$1-G3<-24,$B$1-G3>-31),"D",IF($B$1-G3<0,"C",IF($B$1-
G3>=0,"E"," "))))

Could you please advise if I have entered in the formula correctly?

Your assistance is most appreciated.

Thanks,

Chris.
 
The equals sign (=) designating a formula should only appear as the first
character. And you don't need the plus sign (+).

So:

=IF(ISERROR(IF(AND($B$1-G3<-24,$B$1-G3>-31),"D",IF($B$1-G3<0,"C",IF($B
$1-G3>=0,"E","
")))),"",IF(AND($B$1-G3<-24,$B$1-G3>-31),"D",IF($B$1-G3<0,"C",IF($B$1-
G3>=0,"E"," "))))

I think it would be more efficient to check for blank dates but if it works
....

Regards

Trevor
 
Thanks again Trevor - this works very well now - excellent work - very
much appreciated.

Cheers,

Chris.
 
Back
Top