| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 | | |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 | | |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Too Many Nested IF Statements!
Thanks everybody for your suggestions!
-- tb |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| nested if statements | Excel Worksheet Functions | |||
| Nested IF Statements | Excel Discussion (Misc queries) | |||
| Help with Nested If Statements | Excel Discussion (Misc queries) | |||
| I want to use more than 7 nested if then statements | Excel Worksheet Functions | |||
| Do I need nested IF statements? | Excel Worksheet Functions | |||
Linear Mode
