Editing and Moviing from 6 If's to 7 Ifs (Nested)

Editing and Moviing from 6 If's to 7 Ifs (Nested)

Post by SmltIE1he » Thu, 04 Oct 2007 20:05:01


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",C1048>0,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
 
 
 

Editing and Moviing from 6 If's to 7 Ifs (Nested)

Post by Bob Philli » Thu, 04 Oct 2007 20:54:29

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)

 
 
 

Editing and Moviing from 6 If's to 7 Ifs (Nested)

Post by Niek Otte » Thu, 04 Oct 2007 20:56:07

Hi Jim,

The limit is 7 nested functions, IF's or other functions, so that includes NOT's and AND's

Look here:

http://www.yqcomputer.com/


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| 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",C1048>0,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
 
 
 

Editing and Moviing from 6 If's to 7 Ifs (Nested)

Post by Pete_U » Thu, 04 Oct 2007 21:14:36

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
 
 
 

Editing and Moviing from 6 If's to 7 Ifs (Nested)

Post by SmltIE1he » Thu, 04 Oct 2007 21:28:02

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
 
 
 

Editing and Moviing from 6 If's to 7 Ifs (Nested)

Post by Sandy Man » Thu, 04 Oct 2007 21:42:43

Just for clarifiction, because it confused me when I first started XL, that
is a limit of 7 *NESTED* functions, not a limit of 7 functions per formula.
Jim's original formula has a total of 18 IF's, AND's, NOT's & OR's.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

XXXX@XXXXX.COM
Replace @mailinator.com with @tiscali.co.uk
 
 
 

Editing and Moviing from 6 If's to 7 Ifs (Nested)

Post by cnlndXk3Mj » Thu, 04 Oct 2007 22:25:00

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