Excel 2003 Suppressing Errors

[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.

 

[Trevor Shuttleworth]

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

 

[Chris Hankin]

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.

 

[Trevor Shuttleworth]

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

 

[Chris Hankin]

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

Cheers,

Chris.