Too Many Nested IF Statements!
 

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="1HACCH1MEL000H0 0E",R4*0.38,IF(L4="1HACCH2CNT000H00E",R4*0.38,IF(L 4="1HACCH5CNV000H00E",R4*0.38,IF(L4="1HACCH6SBI000 H00E",R4*0.38,"")))))))))),"")


Thanks.
--
tb

Too Many Nested IF Statements!
 

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(L 4,5)="1F0VT"),R4*0.45,
IF(OR(J4="X",L4="1HACCH1MEL000H00E",L4="1HACCH2CNT 000H00E",
L4="1HACCH5CNV000H00E",L4="1HACCH6SBI000H00E"),R4* 0.38,
IF(OR(L4="1HACCH1MEL000H00E",L4="1HACCH2CNT000H00E ",
L4="1HACCH5CNV000H00E",L4="1HACCH6SBI000H00E"),R4* 0.38,"")))))

--
Regards
Roger Govier



"Tiziano" wrote in message
...
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="1HACCH1MEL000H0 0E",R4*0.38,IF(L4="1HACCH2CNT000H00E",R4*0.38,IF(L 4="1HACCH5CNV000H00E",R4*0.38,IF(L4="1HACCH6SBI000 H00E",R4*0.38,"")))))))))),"")


Thanks.
--
tb

Too Many Nested IF Statements!
 

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

"Tiziano" wrote in message ...
| 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="1HACCH1MEL000H0 0E",R4*0.38,IF(L4="1HACCH2CNT000H00E",R4*0.38,IF(L 4="1HACCH5CNV000H00E",R4*0.38,IF(L4="1HACCH6SBI000 H00E",R4*0.38,"")))))))))),"")
|
|
| Thanks.
| --
| tb
|
|

Too Many Nested IF Statements!
 

Try

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

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

"Niek Otten" wrote:

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

"Tiziano" wrote in message ...
| 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="1HACCH1MEL000H0 0E",R4*0.38,IF(L4="1HACCH2CNT000H00E",R4*0.38,IF(L 4="1HACCH5CNV000H00E",R4*0.38,IF(L4="1HACCH6SBI000 H00E",R4*0.38,"")))))))))),"")
|
|
| Thanks.
| --
| tb
|
|

Too Many Nested IF Statements!
 

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

Pete

On Sep 29, 7:19 am, "Tiziano" wrote:
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(LE*FT(L4,5)="1F0VT",R4*0.45 ,IF(LEFT(L4,5)="1FACC",R4*0.45,IF(LEFT(L4,5)="1H00 E*",R4*0.38,IF(J4="X",R4*0.38,IF(L4="1HACCH1MEL000 H00E",R4*0.38,IF(L4="1HACCH*2CNT000H00E",R4*0.38,I F(L4="1HACCH5CNV000H00E",R4*0.38,IF(L4="1HACCH6SBI 000*H00E",R4*0.38,"")))))))))),"")

Thanks.
--
tb

Too Many Nested IF Statements!
 

Thanks everybody for your suggestions!
--
tb