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=10 0,TRUE,FALSE)),TRUE,FALSE)=FALSE,"none",IF(IF(AND( IF(C2+E2=2000,TRUE,FALSE),IF(B2+D2=200,TRUE,FALS E)),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,TRU E,FALSE),IF(B2+D2=400,TRUE,FALSE)),TRUE,FALSE)=FA LSE,"Gold",IF(IF(AND(IF(C2+E2=5000,TRUE,FALSE),IF (B2+D2=500,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Platnu m",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","CarbonNanoT ubes")))))))

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


--
trex005
------------------------------------------------------------------------
trex005's Profile: http://www.excelforum.com/member.php...o&userid=34724
View this thread: http://www.excelforum.com/showthread...hreadid=544888

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

"trex005" wrote in
message ...

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=10 0,TRUE,FALSE)),TRUE,FALSE)=FALSE,"none",IF(IF(AND( IF(C2+E2=2000,TRUE,FALSE),IF(B2+D2=200,TRUE,FALS E)),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,TRU E,FALSE),IF(B2+D2=400,TRUE,FALSE)),TRUE,FALSE)=FA LSE,"Gold",IF(IF(AND(IF(C2+E2=5000,TRUE,FALSE),IF (B2+D2=500,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Platnu m",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","CarbonNanoT ubes")))))))

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


--
trex005
------------------------------------------------------------------------
trex005's Profile:
http://www.excelforum.com/member.php...o&userid=34724
View this thread: http://www.excelforum.com/showthread...hreadid=544888

=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","CarbonNano Tubes")))))))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"trex005" wrote in
message ...

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=10 0,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=3 000,TRUE,FALSE),IF(B2+D2=
300,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Silver",IF(IF( AND(IF(C2+E2=4000,TRUE,FA
LSE),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,TR UE,FALSE)),TRUE,FALSE)=FAL
SE,"Diamond",IF(IF(AND(IF(C2+E2=7000,TRUE,FALSE), IF(B2+D2=700,TRUE,FALSE))
,TRUE,FALSE)=FALSE,"MoonRock","CarbonNanoTubes"))) ))))

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


--
trex005
------------------------------------------------------------------------
trex005's Profile:
http://www.excelforum.com/member.php...o&userid=34724
View this thread: http://www.excelforum.com/showthread...hreadid=544888

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","CarbonNan oTubes")))))))
....

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";"Diamon d";"MoonRock";"CarbonNanoTubes"})

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

Bob

"Harlan Grove" wrote in message
oups.com...
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","CarbonNan oTubes")))))))
...

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";"Diamon d";"MoonRock";"CarbonNanoT
ubes"})

Wow, this is a great wealth of information! My problem is that I have a
JavaScript (don't laugh, I needed something that someone could run on
any computer, modify the source and run again) program that is writing
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 I
was outputting and simply nesting them into the other formulas. This
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 to
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 one
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 looking
at dadylonglegs and it looked good, except my tier levels will
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?


--
trex005
------------------------------------------------------------------------
trex005's Profile: http://www.excelforum.com/member.php...o&userid=34724
View this thread: http://www.excelforum.com/showthread...hreadid=544888

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";"Diamon d";"MoonRock";"CarbonNanoT
ubes";"Naquida"})

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"trex005" wrote in
message ...

Wow, this is a great wealth of information! My problem is that I have a
JavaScript (don't laugh, I needed something that someone could run on
any computer, modify the source and run again) program that is writing
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 I
was outputting and simply nesting them into the other formulas. This
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 to
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 one
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 looking
at dadylonglegs and it looked good, except my tier levels will
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?


--
trex005
------------------------------------------------------------------------
trex005's Profile:
http://www.excelforum.com/member.php...o&userid=34724
View this thread: http://www.excelforum.com/showthread...hreadid=544888

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


--
bgeier
------------------------------------------------------------------------
bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
View this thread: http://www.excelforum.com/showthread...hreadid=544888

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",I F((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","CarbonNanoT ubes")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=544888

"trex005" wrote in
message ...

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=10 0,TRUE,FALSE)),TRUE,FALSE)=FALSE,"none",IF(IF(AND( IF(C2+E2=2000,TRUE,FALSE),IF(B2+D2=200,TRUE,FALS E)),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,TRU E,FALSE),IF(B2+D2=400,TRUE,FALSE)),TRUE,FALSE)=FA LSE,"Gold",IF(IF(AND(IF(C2+E2=5000,TRUE,FALSE),IF (B2+D2=500,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Platnu m",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","CarbonNanoT ubes")))))))

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

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(AN D(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","Platnu m","Diamond","Moon
Rock","CarbonNanoTubes")
--
David Biddulph

"David Biddulph" wrote in message
...
"trex005" wrote in
message ...

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=10 0,TRUE,FALSE)),TRUE,FALSE)=FALSE,"none",IF(IF(AND( IF(C2+E2=2000,TRUE,FALSE),IF(B2+D2=200,TRUE,FALS E)),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,TRU E,FALSE),IF(B2+D2=400,TRUE,FALSE)),TRUE,FALSE)=FA LSE,"Gold",IF(IF(AND(IF(C2+E2=5000,TRUE,FALSE),IF (B2+D2=500,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Platnu m",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","CarbonNanoT ubes")))))))

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

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(AN D(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","Platnu m","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")
--
David Biddulph
Rowing web pages at
http://www.biddulph.org.uk/