Formula evaluates to #VALUE

[glen.e.mettler]

I am computing the days between dates. I am using Vlookup() to capture
the appropriate dates in another worksheet. Basically it is this
(where B2 & C2 are in another sheet):

B2 C2 D2
1/1/06 1/20/06 19 (C2-B2)
1/1/06 2/1/06 31
3/1/06 empty -38777 if C is empty and B is present I get
a large negative number
empty 3/15/06 38791 if C is present and B is empty, I get
a large positive number
empty empty 0 if both C and B are empty, I get 0
NA empty #VALUE! If either C or B is a text, then C-B
evaluates to an error (#VALUE!)

I have an elaborate formula that should take care of that:

=IF(OR(VLOOKUP($A9,'Proposal
Status'!$G$4:$AH$307,COLUMN(),0)-VLOOKUP($A9,'Proposal
Status'!$G$4:$AH$307,COLUMN()-G$1,0)<0,VLOOKUP($A9,'Proposal
Status'!$G$4:$AH$307,COLUMN(),0)-VLOOKUP($A9,'Proposal
Status'!$G$4:$AH$307,COLUMN()-G$1,0)>3000,ISERROR(VLOOKUP($A9,'Proposal
Status'!$G$4:$AH$307,COLUMN(),0)-VLOOKUP($A9,'Proposal
Status'!$G$4:$AH$307,COLUMN()-G$1,0))),0,VLOOKUP($A9,'Proposal
Status'!$G$4:$AH$307,COLUMN(),0)-VLOOKUP($A9,'Proposal
Status'!$G$4:$AH$307,COLUMN()-G$1,0))

In simplified terms, it is:
=If(or(C-B<0,0,C-B>2000,0,ISERROR(C-B),0,C-B))

If C-B < 0 it works - evaluates to 0
If C-B > 2000 it works - evaluates to 0
If either C or B contains text (ie NA, TBD), then C-B evaluates to and
error - #VALUE!
However, in the formula I have accounted for that but I still get
#VALUE!

Anybody have a solution?

Glen

 

[Pete_UK]

Using your simplified formula, I think you should put the ISERROR first
- otherwise you might have errors in C-B, i.e.:

=IF(ISERROR(C-B),0,IF(OR(C-B<0,C-B>2000),0,C-B))

Hope this helps.

Pete

 

[glen.e.mettler]

That did the trick Pete. Thanks.

Glen

 

[Pete_UK]

Thanks for feeding back.

Pete

 

[DJH]

Try:

=IF(AND(C10>0,B10>0),C10-B10,"")