Re: Formula evaluates to #VALUE

Try:

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


<glen.e.mettler@xxxxxxxx> wrote in message
news:1143565218.691218.65800@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am computing the days between dates. I am using Vlookup() to capture
the appropriate dates in another work***. Basically it is this
(where B2 & C2 are in another ***):

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



.