| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Editing and Moviing from 6 If's to 7 Ifs (Nested)
The following 6 deep Nested If is working fine (630 char long)
=IF(AND(D1048="Cash",NOT((K1048="SCA/FACS NONCARILION"))),"Cash",IF(AND(OR($D1048="Discover" ,D1048="MASTER",D1048="Master card",D1048="VISA"),NOT((K1048="SCA/FACS NONCARILION"))*NOT((K1048="SCA/NORTH CAROLINA"))),"VMD",IF(AND(D1048="null",C10480,OR( K1048="SCA/FACS NONCARILION",K1048="SCA/NORTH CAROLINA")),"bkdf-SCA",IF(AND(D1048="null",C1048<0,OR(K1048="SCA/FACS NONCARILION",K1048="SCA/NORTH CAROLINA")),"bkdf-SCA-Crs",IF(AND(OR(D1048="MASTER",D1048="VISA"),K1048= "SCA/FACS NONCARILION"),"vsmc-sca-NonC",IF(AND(D1048="null",C1048<0,NOT((K1048="SCA/FACS NONCARILION"))*NOT((K1048="SCA/NORTH CAROLINA"))),"bkdf-Crs","bkdf")))))) But when I try and insert a 7th if (below) in between #1 and #3 If's (also adding an additional ")" at the end IF(AND(D1048="Cash",K1048="SCA/FACS NONCARILION"),"SCA_CASH" it will not accept the formula, Highlighting the NOT only!! located from the end as below: NOT((K1048="SCA/FACS NONCARILION"))*NOT((K1048="SCA/NORTH CAROLINA"))),"bkdf-Crs","bkdf"))))))) Any ideas as to why this is occuring? Thanks in Advance for any help you can offer... Jim May |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Editing and Moviing from 6 If's to 7 Ifs (Nested)
break it down to multiple cells Jim, that is a nightmare formula.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim May" wrote in message ... The following 6 deep Nested If is working fine (630 char long) =IF(AND(D1048="Cash",NOT((K1048="SCA/FACS NONCARILION"))),"Cash",IF(AND(OR($D1048="Discover" ,D1048="MASTER",D1048="Master card",D1048="VISA"),NOT((K1048="SCA/FACS NONCARILION"))*NOT((K1048="SCA/NORTH CAROLINA"))),"VMD",IF(AND(D1048="null",C10480,OR( K1048="SCA/FACS NONCARILION",K1048="SCA/NORTH CAROLINA")),"bkdf-SCA",IF(AND(D1048="null",C1048<0,OR(K1048="SCA/FACS NONCARILION",K1048="SCA/NORTH CAROLINA")),"bkdf-SCA-Crs",IF(AND(OR(D1048="MASTER",D1048="VISA"),K1048= "SCA/FACS NONCARILION"),"vsmc-sca-NonC",IF(AND(D1048="null",C1048<0,NOT((K1048="SCA/FACS NONCARILION"))*NOT((K1048="SCA/NORTH CAROLINA"))),"bkdf-Crs","bkdf")))))) But when I try and insert a 7th if (below) in between #1 and #3 If's (also adding an additional ")" at the end IF(AND(D1048="Cash",K1048="SCA/FACS NONCARILION"),"SCA_CASH" it will not accept the formula, Highlighting the NOT only!! located from the end as below: NOT((K1048="SCA/FACS NONCARILION"))*NOT((K1048="SCA/NORTH CAROLINA"))),"bkdf-Crs","bkdf"))))))) Any ideas as to why this is occuring? Thanks in Advance for any help you can offer... Jim May |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Editing and Moviing from 6 If's to 7 Ifs (Nested)
Hi Jim,
The limit is 7 nested functions, IF's or other functions, so that includes NOT's and AND's Look he http://www.j-walk.com/ss/excel/usertips/tip080.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Jim May" wrote in message ... | The following 6 deep Nested If is working fine (630 char long) | | =IF(AND(D1048="Cash",NOT((K1048="SCA/FACS | NONCARILION"))),"Cash",IF(AND(OR($D1048="Discover" ,D1048="MASTER",D1048="Master | card",D1048="VISA"),NOT((K1048="SCA/FACS NONCARILION"))*NOT((K1048="SCA/NORTH | CAROLINA"))),"VMD",IF(AND(D1048="null",C10480,OR( K1048="SCA/FACS | NONCARILION",K1048="SCA/NORTH | CAROLINA")),"bkdf-SCA",IF(AND(D1048="null",C1048<0,OR(K1048="SCA/FACS | NONCARILION",K1048="SCA/NORTH | CAROLINA")),"bkdf-SCA-Crs",IF(AND(OR(D1048="MASTER",D1048="VISA"),K1048= "SCA/FACS | NONCARILION"),"vsmc-sca-NonC",IF(AND(D1048="null",C1048<0,NOT((K1048="SCA/FACS NONCARILION"))*NOT((K1048="SCA/NORTH CAROLINA"))),"bkdf-Crs","bkdf")))))) | | But when I try and insert a 7th if (below) in between #1 and #3 If's (also | adding an additional ")" at the end | | IF(AND(D1048="Cash",K1048="SCA/FACS NONCARILION"),"SCA_CASH" | | it will not accept the formula, Highlighting the NOT only!! located from the | end as below: | NOT((K1048="SCA/FACS NONCARILION"))*NOT((K1048="SCA/NORTH | CAROLINA"))),"bkdf-Crs","bkdf"))))))) | | Any ideas as to why this is occuring? | | Thanks in Advance for any help you can offer... | | Jim May |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Editing and Moviing from 6 If's to 7 Ifs (Nested)
The limit is seven nested functions - not just IFs - and I see that
you have a lot of ANDs and ORs and NOTs which will contribute to this count. I'm not sure why you have this: NOT((K1048="SCA/FACS NONCARILION")) Can't you use: K1048<"SCA/FACS NONCARILION" instead? Hope this helps. Pete On Oct 3, 12:05 pm, Jim May wrote: The following 6 deep Nested If is working fine (630 char long) =IF(AND(D1048="Cash",NOT((K1048="SCA/FACS NONCARILION"))),"Cash",IF(AND(OR($D1048="Discover" ,D1048="MASTER",D1048="Ma*ster card",D1048="VISA"),NOT((K1048="SCA/FACS NONCARILION"))*NOT((K1048="SCA/NORTH CAROLINA"))),"VMD",IF(AND(D1048="null",C10480,OR( K1048="SCA/FACS NONCARILION",K1048="SCA/NORTH CAROLINA")),"bkdf-SCA",IF(AND(D1048="null",C1048<0,OR(K1048="SCA/FACS NONCARILION",K1048="SCA/NORTH CAROLINA")),"bkdf-SCA-Crs",IF(AND(OR(D1048="MASTER",D1048="VISA"),K1048= "SC*A/FACS NONCARILION"),"vsmc-sca-NonC",IF(AND(D1048="null",C1048<0,NOT((K1048="SCA/F*ACS NONCARILION"))*NOT((K1048="SCA/NORTH CAROLINA"))),"bkdf-Crs","bkdf")))))) But when I try and insert a 7th if (below) in between #1 and #3 If's (also adding an additional ")" at the end IF(AND(D1048="Cash",K1048="SCA/FACS NONCARILION"),"SCA_CASH" it will not accept the formula, Highlighting the NOT only!! located from the end as below: NOT((K1048="SCA/FACS NONCARILION"))*NOT((K1048="SCA/NORTH CAROLINA"))),"bkdf-Crs","bkdf"))))))) Any ideas as to why this is occuring? Thanks in Advance for any help you can offer... Jim May |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Editing and Moviing from 6 If's to 7 Ifs (Nested)
In fiddling with it (some time back) excel intuatively assigned this I think
- as it works with 6 Ifs; but I'll try altering to see If I can get the final answer. Thanks for your input Jim "Pete_UK" wrote: The limit is seven nested functions - not just IFs - and I see that you have a lot of ANDs and ORs and NOTs which will contribute to this count. I'm not sure why you have this: NOT((K1048="SCA/FACS NONCARILION")) Can't you use: K1048<"SCA/FACS NONCARILION" instead? Hope this helps. Pete On Oct 3, 12:05 pm, Jim May wrote: The following 6 deep Nested If is working fine (630 char long) =IF(AND(D1048="Cash",NOT((K1048="SCA/FACS NONCARILION"))),"Cash",IF(AND(OR($D1048="Discover" ,D1048="MASTER",D1048="Ma-ster card",D1048="VISA"),NOT((K1048="SCA/FACS NONCARILION"))*NOT((K1048="SCA/NORTH CAROLINA"))),"VMD",IF(AND(D1048="null",C10480,OR( K1048="SCA/FACS NONCARILION",K1048="SCA/NORTH CAROLINA")),"bkdf-SCA",IF(AND(D1048="null",C1048<0,OR(K1048="SCA/FACS NONCARILION",K1048="SCA/NORTH CAROLINA")),"bkdf-SCA-Crs",IF(AND(OR(D1048="MASTER",D1048="VISA"),K1048= "SC-A/FACS NONCARILION"),"vsmc-sca-NonC",IF(AND(D1048="null",C1048<0,NOT((K1048="SCA/F-ACS NONCARILION"))*NOT((K1048="SCA/NORTH CAROLINA"))),"bkdf-Crs","bkdf")))))) But when I try and insert a 7th if (below) in between #1 and #3 If's (also adding an additional ")" at the end IF(AND(D1048="Cash",K1048="SCA/FACS NONCARILION"),"SCA_CASH" it will not accept the formula, Highlighting the NOT only!! located from the end as below: NOT((K1048="SCA/FACS NONCARILION"))*NOT((K1048="SCA/NORTH CAROLINA"))),"bkdf-Crs","bkdf"))))))) Any ideas as to why this is occuring? Thanks in Advance for any help you can offer... Jim May |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Editing and Moviing from 6 If's to 7 Ifs (Nested)
Maybe you can get that to work in a Pivot Table. I've had great luck using
Pivot Tables to overcome Excel's 7-condition-if-limit. Regards, Ryan--- -- RyGuy "Jim May" wrote: The following 6 deep Nested If is working fine (630 char long) =IF(AND(D1048="Cash",NOT((K1048="SCA/FACS NONCARILION"))),"Cash",IF(AND(OR($D1048="Discover" ,D1048="MASTER",D1048="Master card",D1048="VISA"),NOT((K1048="SCA/FACS NONCARILION"))*NOT((K1048="SCA/NORTH CAROLINA"))),"VMD",IF(AND(D1048="null",C10480,OR( K1048="SCA/FACS NONCARILION",K1048="SCA/NORTH CAROLINA")),"bkdf-SCA",IF(AND(D1048="null",C1048<0,OR(K1048="SCA/FACS NONCARILION",K1048="SCA/NORTH CAROLINA")),"bkdf-SCA-Crs",IF(AND(OR(D1048="MASTER",D1048="VISA"),K1048= "SCA/FACS NONCARILION"),"vsmc-sca-NonC",IF(AND(D1048="null",C1048<0,NOT((K1048="SCA/FACS NONCARILION"))*NOT((K1048="SCA/NORTH CAROLINA"))),"bkdf-Crs","bkdf")))))) But when I try and insert a 7th if (below) in between #1 and #3 If's (also adding an additional ")" at the end IF(AND(D1048="Cash",K1048="SCA/FACS NONCARILION"),"SCA_CASH" it will not accept the formula, Highlighting the NOT only!! located from the end as below: NOT((K1048="SCA/FACS NONCARILION"))*NOT((K1048="SCA/NORTH CAROLINA"))),"bkdf-Crs","bkdf"))))))) Any ideas as to why this is occuring? Thanks in Advance for any help you can offer... Jim May |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF Statement (Nested IF's??) | Excel Worksheet Functions | |||
| Nested IF's with Rank Problem | Excel Worksheet Functions | |||
| Nested If's | Excel Worksheet Functions | |||
| How many IF's can you have in a nested IF function? | Excel Worksheet Functions | |||
| How many nested IF's??? | Excel Discussion (Misc queries) | |||
Linear Mode
