A formula that is too complex for Excel?

trex005 · May 23, 2006

Okay, I have written longer formulas than this and what I thought wa
more complex, but for some reason excel says that there is an error i
this formula!

I have checked it over and over and there are no syntax errors.... wha
is the problem?

Code

Biff · May 23, 2006

what is the problem?

You've exceeded the nested function limit of 7. Looks like you have 9.

Try explaining what you're wanting to do.

Biff

Bob Phillips · May 23, 2006

=IF(OR(C2+E2<1000,B2+D2<100),"none",
IF(OR(C2+E2<2000,B2+D2<200),"Bronze",
IF(OR(C2+E2<3000,B2+D2<300),"Silver",
IF(OR(C2+E2<4000,B2+D2<400),"Gold",
IF(OR(C2+E2<5000,B2+D2<500),"Platnum",
IF(OR(C2+E2<6000,B2+D2<600),"Diamond",
IF(OR(C2+E2<7000,B2+D2<700),"MoonRock","CarbonNanoTubes")))))))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

bgeier · May 23, 2006

Excel has a limit of 7 nested if statements.
It looks like you have at least 9

daddylonglegs · May 23, 2006

There's a limit of 7 nested functions in Excel, I think you've exceeded
that. At least one of your IF functions is superfluous....and all the
TRUEs and FALSEs are unnecessary. Try this

=IF((C2+E2>=7000)*(B2+D2>=700),"CarbonNanoTubes",IF((C2+E2>=6000)*(B2+D2>=600),"MoonRock",IF((C2+E2>=5000)*(B2+D2>=500),"Diamond",IF((C2+E2>=4000)*(B2+D2>=400),"Platinum",IF((C2+E2>=3000)*(B2+D2>=300),"Gold",IF((C2+E2>=2000)*(B2+D2>=200),"Silver",IF((C2+E2>=1000)*(B2+D2>=100),"Bronze","None")))))))

....although you could probably simplify further using a different
approach, i.e.

=CHOOSE(MIN(MATCH((C2+E2)/1000,{0,1,2,3,4,5,6,7}),MATCH((B2+D2)/100,{0,1,2,3,4,5,6,7})),"None","Bronze","Silver","Gold","Platinum","Diamond","MoonRock","CarbonNanoTubes")

David Biddulph · May 23, 2006

trex005 said:
Okay, I have written longer formulas than this and what I thought was
more complex, but for some reason excel says that there is an error in
this formula!

I have checked it over and over and there are no syntax errors.... what
is the problem?

Code:
--------------------

=IF(IF(AND(IF(C2+E2>=1000,TRUE,FALSE),IF(B2+D2>=100,TRUE,FALSE)),TRUE,FALSE)=FALSE,"none",IF(IF(AND(IF(C2+E2>=2000,TRUE,FALSE),IF(B2+D2>=200,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Bronze",IF(IF(AND(IF(C2+E2>=3000,TRUE,FALSE),IF(B2+D2>=300,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Silver",IF(IF(AND(IF(C2+E2>=4000,TRUE,FALSE),IF(B2+D2>=400,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Gold",IF(IF(AND(IF(C2+E2>=5000,TRUE,FALSE),IF(B2+D2>=500,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Platnum",IF(IF(AND(IF(C2+E2>=6000,TRUE,FALSE),IF(B2+D2>=600,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Diamond",IF(IF(AND(IF(C2+E2>=7000,TRUE,FALSE),IF(B2+D2>=700,TRUE,FALSE)),TRUE,FALSE)=FALSE,"MoonRock","CarbonNanoTubes")))))))

--------------------

The first stage of simplification is that you don't need to say to say
IF(AND(TestA,TestB),TRUE,FALSE) as this is just the same as
AND(Testa,TestB), which already returns a TRUE or FALSE result.

This then simplifies your expression to:

=IF(AND(C2+E2>=1000,B2+D2>=100)=FALSE,"none",IF(AND(C2+E2>=2000,B2+D2>=200)=FALSE,"Bronze",IF(AND(C2+E2>=3000,B2+D2>=300)=FALSE,"Silver",IF(AND(C2+E2>=4000,B2+D2>=400)=FALSE,"Gold",IF(AND(C2+E2>=5000,B2+D2>=500)=FALSE,"Platnum",IF(AND(C2+E2>=6000,B2+D2>=600)=FALSE,"Diamond",IF(AND(C2+E2>=7000,B2+D2>=700)=FALSE,"MoonRock","CarbonNanoTubes")))))))

if I've got my edits right, but of course that still exceeds the 7 limit
for nesting.

I think you might be able to try something like:
=CHOOSE(MIN(INT(MIN((C2+E2)/1000,(B2+D2)/100))+1,8),"None","Bronze","Silver","Gold","Platnum","Diamond","Moon
Rock","CarbonNanoTubes")

Harlan Grove · May 23, 2006

Bob Phillips wrote...

=IF(OR(C2+E2<1000,B2+D2<100),"none",
IF(OR(C2+E2<2000,B2+D2<200),"Bronze",
IF(OR(C2+E2<3000,B2+D2<300),"Silver",
IF(OR(C2+E2<4000,B2+D2<400),"Gold",
IF(OR(C2+E2<5000,B2+D2<500),"Platnum",
IF(OR(C2+E2<6000,B2+D2<600),"Diamond",
IF(OR(C2+E2<7000,B2+D2<700),"MoonRock","CarbonNanoTubes")))))))

....

If so,

=LOOKUP(MIN(INT((C2+E2)/1000),INT((B2+D2)/100)),{-1E300;1;2;3;4;5;6;7},
{"none";"Bronze";"Silver";"Gold";"Platnum";"Diamond";"MoonRock";"CarbonNanoTubes"})

David Biddulph · May 23, 2006

The first stage of simplification is that you don't need to say to say
IF(AND(TestA,TestB),TRUE,FALSE) as this is just the same as
AND(Testa,TestB), which already returns a TRUE or FALSE result.

This then simplifies your expression to:

=IF(AND(C2+E2>=1000,B2+D2>=100)=FALSE,"none",IF(AND(C2+E2>=2000,B2+D2>=200)=FALSE,"Bronze",IF(AND(C2+E2>=3000,B2+D2>=300)=FALSE,"Silver",IF(AND(C2+E2>=4000,B2+D2>=400)=FALSE,"Gold",IF(AND(C2+E2>=5000,B2+D2>=500)=FALSE,"Platnum",IF(AND(C2+E2>=6000,B2+D2>=600)=FALSE,"Diamond",IF(AND(C2+E2>=7000,B2+D2>=700)=FALSE,"MoonRock","CarbonNanoTubes")))))))

if I've got my edits right, but of course that still exceeds the 7 limit
for nesting.

I think you might be able to try something like:
=CHOOSE(MIN(INT(MIN((C2+E2)/1000,(B2+D2)/100))+1,8),"None","Bronze","Silver","Gold","Platnum","Diamond","Moon
Rock","CarbonNanoTubes")

And in fact you can probably skip the INT(), hence:
=CHOOSE(MIN(MIN((C2+E2)/1000,(B2+D2)/100)+1,8),"None","Bronze","Silver","Gold","Platnum","Diamond","Moon
Rock","CarbonNanoTubes")

Bob Phillips · May 24, 2006

One step at a time Harlan, I was trying to show the flaws in the OPs logic
<g>

Bob

trex005 · May 24, 2006

Wow, this is a great wealth of information! My problem is that I have
JavaScript (don't laugh, I needed something that someone could run o
any computer, modify the source and run again) program that is writin
this and many other formulas that takes user input of the "tiers".
I have to output many formula's, and I was using other formulas that
was outputting and simply nesting them into the other formulas. Thi
would work if I could nest unlimited, but I guess that is not the case
Instead I decided, with your guys help, that I was just going to have t
write each formula on it's own.
Now my program outputs this :
=IF(OR(C2+E2<1000,B2+D2<100),"none",
IF(OR(C2+E2<2000,B2+D2<200),"Bronze",
IF(OR(C2+E2<3000,B2+D2<300),"Silver",
IF(OR(C2+E2<4000,B2+D2<400),"Gold",
IF(OR(C2+E2<5000,B2+D2<500),"Platinum",
IF(OR(C2+E2<6000,B2+D2<600),"Diamond",
IF(OR(C2+E2<7000,B2+D2<700),"MoonRock",
"CarbonNanoTubes")))))))
(exactly what you guys were simplifying to)
Unfortunatly, I still then hit the nesting limit as soon as I add on
more tier
=IF(OR(C2+E2<1000,B2+D2<100),"none",
IF(OR(C2+E2<2000,B2+D2<200),"Bronze",
IF(OR(C2+E2<3000,B2+D2<300),"Silver",
IF(OR(C2+E2<4000,B2+D2<400),"Gold",
IF(OR(C2+E2<5000,B2+D2<500),"Platinum",
IF(OR(C2+E2<6000,B2+D2<600),"Diamond",
IF(OR(C2+E2<7000,B2+D2<700),"Moon Rock",
IF(OR(C2+E2<8000,B2+D2<800),"Carbon Nano Tubes",
"Naquida"))))))))
So I guess I do need to go with a lookup system of sorts. I was lookin
at dadylonglegs and it looked good, except my tier levels wil
constantly be changing and will not be as reliable as my sample data.
It will more likely look something like this :
=IF(OR(C2+E2<117.5,B2+D2<12),"none",
IF(OR(C2+E2<186.45,B2+D2<19),"Bronze",
IF(OR(C2+E2<499.99,B2+D2<27),"Silver",
IF(OR(C2+E2<1965.45,B2+D2<70),"Gold",
IF(OR(C2+E2<6789.95,B2+D2<111),"Platinum",
IF(OR(C2+E2<8006.41,B2+D2<173),"Diamond",
IF(OR(C2+E2<9001.32,B2+D2<198),"Moon Rock",
IF(OR(C2+E2<10000.39,B2+D2<275),"Carbon Nano Tubes",
"Naquida"))))))))
Is there an easy way to do this

Bob Phillips · May 24, 2006

trex,

This is where you use Harlan's little beauty of an alternative.

=LOOKUP(MIN(INT((C2+E2)/1000),INT((B2+D2)/100)),{-1E+300;1;2;3;4;5;6;7;8},
{"none";"Bronze";"Silver";"Gold";"Platnum";"Diamond";"MoonRock";"CarbonNanoT
ubes";"Naquida"})

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

trex005 · May 24, 2006

Because that assumes that there is consistency between the tiers, which
is easy to write without, but even more because it assumes that there
is consistency within the tier, it does not work. I'm sure that there
is a way to write it, but it is too much for my little brain to handle!
if you look at the last sample I gave, you will see that I used more
realistic numbers.

Thank you everyone for your help already given, and the help you are
still providing!

Harlan Grove · May 24, 2006

trex005 wrote...

Because that assumes that there is consistency between the tiers, which
is easy to write without, but even more because it assumes that there
is consistency within the tier, it does not work. I'm sure that there
is a way to write it, but it is too much for my little brain to handle!
if you look at the last sample I gave, you will see that I used more
realistic numbers.

....

Regularity isn't necessary. Lack of regularity makes this only slightly
more complicated. Instead of MIN((C2+E2)/1000,(B2+D2)/100), use

MIN(MATCH(C2+E2,{-1E300;117.5;186.45;499.99;1965.45;6789.95;8006.41;9001.32;
10000.39}),MATCH(B2+D2,{-1E300;12;19;27;70;111;173;198;275}))

and dispense with the LOOKUP and use INDEX instead.

=INDEX({"none";"Bronze";"Silver";"Gold";"Platinum";"Diamond";"Moon
Rock";
"Carbon Nano
Tubes";"Naquida"},MIN(MATCH(C2+E2,{-1E300;117.5;186.45;499.99;
1965.45;6789.95;8006.41;9001.32;10000.39}),MATCH(B2+D2,{-1E300;12;19;27;70;111;
173;198;275}))

Bob Phillips · May 24, 2006

Give this a whirl

=INDEX({"Naquida";"Carbon Nano Tubes";"Moon
Rock";"Diamond";"Platinum";"Gold";"Silver";"Bronze";"none"},
MAX(MATCH(C2+E2,{999999;10000.39;9001.32;8006.41;6789.95;1965.45;499.99;186.
45;117.5},-1),
MATCH(B2+D2,{999999;275;198;173;111;70;27;19;12},-1)))

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

A formula that is too complex for Excel?

trex005

Biff

Bob Phillips

bgeier

daddylonglegs

David Biddulph

Harlan Grove

David Biddulph

Bob Phillips

trex005

Bob Phillips

trex005

Harlan Grove

Bob Phillips