"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