Too Many Nested IF Statements!

  • Thread starter Thread starter Tiziano
  • Start date Start date
T

Tiziano

Hi.
I have created the following formula which Excel refuses to accept
because I have exceeded the maximum number of nested IF statements allowed.
(I believe the max. number is set at seven nested IF statements.)
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.
 
Hi

One way
=IF(W4="",
IF(OR(LEFT(L4,5)="1C050",LEFT(L4,5)="1H00E"),R4*0.38,
IF(OR(LEFT(L4,5)="1F0VE",LEFT(L4,5)="1FACC",LEFT(L4,5)="1F0VT"),R4*0.45,
IF(OR(J4="X",L4="1HACCH1MEL000H00E",L4="1HACCH2CNT000H00E",
L4="1HACCH5CNV000H00E",L4="1HACCH6SBI000H00E"),R4*0.38,
IF(OR(L4="1HACCH1MEL000H00E",L4="1HACCH2CNT000H00E",
L4="1HACCH5CNV000H00E",L4="1HACCH6SBI000H00E"),R4*0.38,"")))))
 
It is not just IFs that can't be nested more than 7 levels, it applies to all functions.
In Excel 2007 you can nest 64 levels. Not that that makes your formulas any more readable....

Look here for alternatives:

http://www.j-walk.com/ss/excel/usertips/tip080.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi.
| I have created the following formula which Excel refuses to accept
| because I have exceeded the maximum number of nested IF statements allowed.
| (I believe the max. number is set at seven nested IF statements.)
| 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.
| --
| tb
|
|
 
Try

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

You could perhaps shorten further if any value beginning "1HACCH" should
always return 0.38*R4
 
Back
Top