#N/A
 

The below formula is returning #N/A after hitting enter.
How do I get rid of this?

=IF(OR(SUM($A2:$H2)=0,AND(OR($A2="==== VOID
<====<",$A2="* Non Budget Item *"),$B2<0,SUM($D2:$I2)
=0)),"",IF(AND($A20,$B20,$C20,$D20,$E2<0,IF($ Z20,OR
($Z2=TRUE),FALSE),$G20,$H2=0,$D2<=TODAY(),TRUNC( $J2)
=LOOKUP(TRUNC($J2),'Approved Codes'!$C$1:$C$287),OR(TRUNC
($J2)=LOOKUP(TRUNC($J2),'Approved Project Codes'!
$C$1:$C$224),OR$F2="C",$F2="M",$F2="S",$F2="I",$F2 ="F",$F2=
"R",$F2="B",$F2="W",$F2="V",$F2="P",$F2="T",$F2=" O",$F2="L
")),OR($C1<$C2,AND($A1=$A2,$B1=$B2,$C1=$C2,$D1=$ D2,OR
($F1<$F2,$G1<$G2,$H1<$H2))),OR($C2<$C3,AND
($A2=$A3,$B2=$B3,$C2=$C3,$D2=$D3,OR
($F2<$F3,$G2<$G3,$H2<$H3))),OR($I2=100,AND
($I20,$I2<100,OR(AND($C1=$C2,$E1=$E2,$J1<$J2),AN D
($C2=$C3,$E2=$E3,$J2<$J3))))),IF($C2=$C3,IF($C2=$ C4,IF
($C2=$C5,IF($C2=$C6,IF(OR
($C1=$C2,$I2+$I3+$I4+$I5+$I6=500,$I2+$I3+$I4+$I5+$ I6=100),"
","Error"),IF(OR
($C1=$C2,$I2+$I3+$I4+$I5=400,$I2+$I3+$I4+$I5=100), "","Error
")),IF(OR
($C1=$C2,$I2+$I3+$I4=300,$I2+$I3+$I4=100),"","Erro r")),IF
(OR
($C1=$C2,$I2+$I3=200,$I2+$I3=100),"","Error")),"") ,"Error")
)

#N/A
 

Asha,

One way is to simply delete the formula, though that probably isn't the
answer you were looking for.

Quoting from XL help file.

"The #N/A error value occurs when a value is not available to a function or
formula. If certain cells on your worksheet will contain data that is not
yet available, enter #N/A in those cells. Formulas that refer to those cells
will then return #N/A instead of attempting to calculate a value."
There is NO WAY that we can tell which of those cells doesn't contain a
value or whatever from this message.

If you do want to hunt for your error, I have a "hunch" the problem probably
occurs in one of the lookup statements. You can highlight various portions
of the formula in the formula bar and press F9 to evaluate it. That will
help to find where your problem occurs. You must press escape to undo the
evaluation prior to hitting enter. Sometimes when the formula is long,
Excel will not allow you to do the evaluation in the formula bar.

Good luck.

Regards,

Kevin



"Asha" wrote in message
...
The below formula is returning #N/A after hitting enter.
How do I get rid of this?

=IF(OR(SUM($A2:$H2)=0,AND(OR($A2="==== VOID
<====<",$A2="* Non Budget Item *"),$B2<0,SUM($D2:$I2)
=0)),"",IF(AND($A20,$B20,$C20,$D20,$E2<0,IF($ Z20,OR
($Z2=TRUE),FALSE),$G20,$H2=0,$D2<=TODAY(),TRUNC( $J2)
=LOOKUP(TRUNC($J2),'Approved Codes'!$C$1:$C$287),OR(TRUNC
($J2)=LOOKUP(TRUNC($J2),'Approved Project Codes'!
$C$1:$C$224),OR$F2="C",$F2="M",$F2="S",$F2="I",$F2 ="F",$F2=
"R",$F2="B",$F2="W",$F2="V",$F2="P",$F2="T",$F2=" O",$F2="L
")),OR($C1<$C2,AND($A1=$A2,$B1=$B2,$C1=$C2,$D1=$ D2,OR
($F1<$F2,$G1<$G2,$H1<$H2))),OR($C2<$C3,AND
($A2=$A3,$B2=$B3,$C2=$C3,$D2=$D3,OR
($F2<$F3,$G2<$G3,$H2<$H3))),OR($I2=100,AND
($I20,$I2<100,OR(AND($C1=$C2,$E1=$E2,$J1<$J2),AN D
($C2=$C3,$E2=$E3,$J2<$J3))))),IF($C2=$C3,IF($C2=$ C4,IF
($C2=$C5,IF($C2=$C6,IF(OR
($C1=$C2,$I2+$I3+$I4+$I5+$I6=500,$I2+$I3+$I4+$I5+$ I6=100),"
","Error"),IF(OR
($C1=$C2,$I2+$I3+$I4+$I5=400,$I2+$I3+$I4+$I5=100), "","Error
")),IF(OR
($C1=$C2,$I2+$I3+$I4=300,$I2+$I3+$I4=100),"","Erro r")),IF
(OR
($C1=$C2,$I2+$I3=200,$I2+$I3=100),"","Error")),"") ,"Error")
)