Can't find the error in this IF statement

**globulous** · 10-26-2009, 06:51 AM

=IF(K154="","",
If(AND(BI154="Hm",AC154="W",F154<0),100,if(AND(BI154="hm",AC154="W",d154>0),F154,
if(AND(BI154="Hm",AC154="L",F154<0),F154,if(AND(BI154="hm",AC154="L",f154>0),-100,
if(AND(BI154="aw",AC154="L",D154>0),D154,if(AND(BI154="aw",AC154="L",D154<0),100,
if(AND(BI154="aw",AC154="W",D154<0),D154,if(AND(BI154="aw",AC154="W",D154>0),-100,
if(AND(BI154="OT",BJ154="W"),320,-100))))))))))

it's telling me there's an error and highlights the AND
i went over this about 20 times and can't find anything..
it's also almost 6am and i'm not too sharp now..
but can anyone help with this??
[removed].. i just can't see it..

**DonkeyOte** · 10-26-2009, 07:01 AM

exceeding nested IF limitations.

Can you confirm that the reference for Hm / W with output of F154 is definitely D154>0 and not F154 -- all other Hm references use F154
(whereas seemingly only Aw use D154)

I was putting together a truth table and your formulae implies:

Code:

BI154	AC154	F154	D154	Output
Hm	W	<0		100
Hm	W		>0	F154
Hm	L	<0		F154
Hm	L	>0		-100
aw	L		>0	D154
aw	L		<0	100
aw	W		<0	D154
aw	W		>0	-100

Is that correct ?

**globulous** · 10-26-2009, 01:05 PM

Donkey.. i found the correction you noted and made it..

HM/W and HM/L should be referring to F154...
I have made that correction, however i still get the same error.

**globulous** · 10-26-2009, 03:02 PM

Is there a maximum # of IF's i can embed??

i took the last bit out...

if(AND(BI154="aw",AC154="W",D154>0),-100,
if(AND(BI154="OT",BJ154="W"),320,-100))))))))))

and it works fine..

But i can't figure out why that last part doesn't work..

Even if you just make it...

if(AND(BI154="aw",AC154="W",D154>0),-100,""))))))))

it still won't work..

**NBVC** · 10-26-2009, 05:33 PM

You haven't addressed DonkeyOte's question regarding what to do if D154 and/or F154 actually equals 0?

**Whizbang** · 10-26-2009, 05:43 PM

Taken from the help files:

Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. (See Example 3 for a sample of nested IF functions.) If you want to test more than seven conditions, consider using the LOOKUP, VLOOKUP, or HLOOKUP function. (See Example 4 for a sample of the LOOKUP function.)

**NBVC** · 10-26-2009, 05:57 PM

If you change some of those conditions to >= 0 instead of having another condition when you D or F equalling 0, then this works to cover the conditions you have imposed. (Note: I didn't change any to >= or <=, I will leave to you which to change).

Code:

=IF(K154="","",IF(BI154="HM",IF(AC154="W",IF(F154<0,100,F154),IF(D154<0,F154,-100)),IF(BI154,"aw",IF(AC154="L",IF(F154>0,D154,100),IF(F154<0,D154,IF(AND(BI154="OT",BJ154="W"),320,-100))))))

you could probably use the lookup kind of table, but you added a totally outside set of variables at the end..... and not sure what else you'll change.