Too Many Nested IF Statements!

[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.

 

[Roger Govier]

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,"")))))

 

[Niek Otten]

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
|
|

 

[daddylonglegs]

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

 

[Pete_UK]

See my reply to your other post in ...worksheetfunctions

Pete

 

[Tiziano]

Thanks everybody for your suggestions!