Excel 2003 Suppressing Errors
 

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.

*** Sent via Developersdex http://www.developersdex.com ***

Excel 2003 Suppressing Errors
 

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" wrote in message
...
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.

*** Sent via Developersdex http://www.developersdex.com ***

Excel 2003 Suppressing Errors
 

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.


*** Sent via Developersdex http://www.developersdex.com ***

Excel 2003 Suppressing Errors
 

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" wrote in message
...
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.


*** Sent via Developersdex http://www.developersdex.com ***

Excel 2003 Suppressing Errors
 

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

Cheers,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***