Too Many IF Statements!

[tiziano1]

Hi.
I have created the following formula which Excel refuses to accept
because I have exceeded the maximum number of IF statements allowed.
(I believe the max. number is seven.)
Can anybody suggest how the formula could be optimized?

=IF(W4="",IF(LEFT(L4,5)="1C050",R4*0.38,IF(LEFT(L4,5)="1F0VE",R4*0.45,IF(LEFT(L4,5)="1F0VT",R4*0.45,IF(LEFT(L4,5)="1FACC",R4*0.45,IF(LEFT(L4,5)="1H00E",R4*0.38,IF(J4="X",R4*0.38,IF(L4="1HACCH1MEL000H00E",R4*0.38,IF(L4="1HACCH2CNT000H00E",R4*0.38,IF(L4="1HACCH5CNV000H00E",R4*0.38,IF(L4="1HACCH6SBI000H00E",R4*0.38,"")))))))))),"")

Thanks.

 

[Pete_UK]

You can set up a lookup table for the first part - for example in
Y1:Z5 make up the following table:

1C050 0.38
1F0VE 0.45
1F0VT 0.45
1FACC 0.45
1H00E 0.38

Then your formula would become:

=IF(W4="",IF(ISNA(VLOOKUP(LEFT(L4,5),Y1:Z5,1,0)),IF(OR(J4="X",L4="1HACCH1MEL000H00E",L4="1HACCH2CNT000H00E",L4="1HACCH5CNV000H00E",L4="1HACCH6SBI000­
H00E"),R4*0.38,""),R4*VLOOKUP(LEFT(L4,5),Y1:Z5,2,0)),"")

I'm not sure if this would still count as two many nested functions,
so you could put this list in Y10:Z13:

1HACCH1MEL000H00E
1HACCH­2CNT000H00E
1HACCH5CNV000H00E
1HACCH6SBI000­H00E

and test for this instead of the 4 comparisons with L4.

Hope this helps.

Pete

 

[Harlan Grove]

(e-mail address removed) wrote...

I have created the following formula which Excel refuses to accept
because I have exceeded the maximum number of IF statements allowed. ....
=IF(W4="",IF(LEFT(L4,5)="1C050",R4*0.38,IF(LEFT(L4,5)="1F0VE",R4*0.45,
IF(LEFT(L4,5)="1F0VT",R4*0.45,IF(LEFT(L4,5)="1FACC",R4*0.45,
IF(LEFT(L4,5)="1H00E",R4*0.38,IF(J4="X",R4*0.38,
IF(L4="1HACCH1MEL000H00E",R4*0.38,
IF(L4="1HACCH2CNT000H00E",R4*0.38,
IF(L4="1HACCH5CNV000H00E",R4*0.38,
IF(L4="1HACCH6SBI000H00E",R4*0.38,"")))))))))),"")

Another alternative.

=IF(W4<>"","",(OR(J4="X",COUNTIF(L4,{"1C050*","1H00E*",
"1HACCH1MEL000H00E","1HACCH2CNT000H00E","1HACCH5CNV000H00E",
"1HACCH6SBI000H00E"}))*0.38+OR(COUNTIF(L4,{"1F0VE*","1F0VT*",
"1FACC*"}))*0.45)*R4)